Login report - inconclusive results

Login report - inconclusive results

by Dan Bell -
Number of replies: 3

Hi,

Currently using Moodle 3.8. Not a SQL expert, but have had some success, but as noted, some question marks surrounding two similar reports. The goal is to have a report that (#1) lists all users and the last login date. The report needs to (#2) include user profile fields so we can export and filter. I started with #1. I had success with the following:

/*ALL LOGINS*/

SELECT id, username, FROM_UNIXTIME(   lastlogin   ) as lastlogin

FROM    prefix_user 


The above returns all 1,063 users with login dates. In the case a user has never logged in, a default date of12/31/1969  7:00:00 PM appears. ( I wish this was blank, but can work with this in filtering an exported csv, but would be nice to display 0 like it does in the data).

However when trying to add in some custom user profile fields with the following SQL, the profile fields appear as expected, but the results  are much less (382). They are mix of results. Some with the 1969 date. Not all of the user profile fields are populated in the LMS. The user profile fields values appear to be correct. But I'm not sure why the results are so much less. I was expecting the same as above but with user profile fields. Is there anything in this sql that would filter different than above?

SELECT u.username,FROM_UNIXTIME(   lastlogin   ) as lastlogin,d1.data AS 'Vertical', d2.data As 'Entity'

FROM    prefix_user u 

JOIN prefix_user_info_data d1 ON d1.userid = u.id

JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'vertical'

JOIN prefix_user_info_data d2 ON d2.userid = u.id

JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'entity'


Average of ratings: -
In reply to Dan Bell

Re: Login report - inconclusive results

by Randy Thornton -
Picture of Documentation writers
Dan,

For the first issue about the 0 timestamp, please see the documentation I have about this on the Ad-hoc contributed reports page.

For the second issue, you data is correct, given two things:

- Custom profile fields ONLY have actual data in them if the user (or admin or manager etc) has EXPLICITLY chosen or set the data. The "default" indicator for many of the types of profile field is an illusion. There will only be a row in the table for users that have actually set a value.

- The query is using two joins on two fields. So, it is showing users that have both fields filled in with data. If you want to show users who have any of them, then try the alternate method using subselects as documented here.
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Login report - inconclusive results

by Dan Bell -
Randy,
Thank you for your reply. Yes, many of our users are expected to have no value populated for for some or all of the profile fields added via the sql. In fact, this might work for us as is because we don't care too much about users without these profile fields populated. However, if I understand you correctly, only users with one or more of these 3 profile fields populated will appear. However, I am getting a few users in this report with no vertical, entity or entitygroup value as shown in the image below. Can you confirm that is what you are saying - that at least of these 3 fields needs a value to appear in the report?

Average of ratings: -
In reply to Dan Bell

Re: Login report - inconclusive results

by Randy Thornton -
Picture of Documentation writers
Dan,

Yes, that is what I usually see and expect since so many of those values will just be non-existent.

If you really wanted to, of course, you can add some logic to the report to fill in those blanks, even with the default value of the fields you have set. But I usually do not do that when we want to see that those have not been filled and are empty. It really depends on the purpose (and audience) of the report.
Average of ratings: -