SCCM 2012 Report- user login info

[IT-LAIG]

I am a SCCM newbie with no SQL query background and need help with this task. I have been asked in SCCM 2012 to create custom report that shows user login information. They want to be able to see all users and their login timestamp (in a readable form). I really need help with how get this accomplished in Configuration Manager and how do I go about in creating a query. Can somebody please help me with this task

[IT-LAIG]

Can anyone please help me with this... This is the query I have come up with so far and I am getting error when I try to run it against the user collection I have created.

 

SELECT SMS_R_User.UserName,
SMS_R_User.Name, SMS_R_User.lastLogonTimestamp
DATEADD(mi,(lastLogontimeStamp0 / 600000000) - 157258080,0) AS [Last Logon Time Stamp (UTC)]
FROM SMS_R_User.UserName
WHERE User_Name0 LIKE @variable

 

If anyone can help me fix this script, then that would be a big help.

Thanks-

[itguru1982]

I'm assuming you are using SCCM 2012? (SP1?) The above looks more like a SCCM 2007 script, the tables have changed slightly in 2012 so the script will not work.

 

I've put together something that should work for you here:

 

http://itguru82-sccm.blogspot.co.uk/2013/03/last-logon-time-report-using-sccm-data.html

 

Hope this helps...

[GarthMJ]

I hate to tell you itguru1982 this but there are a number of thing wrong with your query.

1) Your join in incorrect. USR.ResourceID != SYS.ResourceID ; you can see this by changing you left join to a right join

2) The Last logon time that you are using is for the PC not the user.