Hi there,
I have this report that I *think* gets the correct # of unique logins grouped by day. However, when I compare this with manually checking the users list, the numbers are drastically different. For example: With my dataset and query below, the SQL reports that 1014 unique users logged into the LMS on 26th March 2019. However, admin/user.php > advanced filters > last access is after 26 March 2020 and
before 27 March 2020 shows 866 users that logged in. Screenshot attached.
Why the difference?
select
from_unixtime(lsl.timecreated,"%D %M %Y") day,
count(lsl.userid)
from prefix_logstore_standard_log lsl
where lsl.action = 'loggedin'
and lsl.target = 'user'
group by from_unixtime(lsl.timecreated,"%D %M %Y")
order by lsl.timecreated desc
SQL Query data:
| day | count(distinct lsl.userid) |
|---|---|
| 27th March 2020 | 534 |
| 26th March 2020 | 1014 |
| 25th March 2020 | 1119 |
| 24th March 2020 | 1243 |
| 23rd March 2020 | 1241 |
| 22nd March 2020 | 395 |
| 21st March 2020 | 301 |
| 20th March 2020 | 744 |
| 19th March 2020 | 915 |
| 18th March 2020 | 920 |