sql command to generate report for inactive users


  • hi,

    I want to create a report in sccm console so that it will show the inactive users for a specific period of time with the columns lastlogon time, and user name. Does any one have the idea of this


    Tuesday, September 10, 2013 10:58 AM


All replies

  • It's not a good idea to do this in SCCM. SCCM will check each PC for the last logged in user, then display that.

    So if there's a PC called XYZ that Alice logged onto on wednesday, and Bob on thursday, you'd only see Bob's login and assume that Alice is inactive.

    Querying Active Directory would work better, with a script like

    Get-ADUser -Filter * -SearchBase "ou=UserAccounts,dc=contoso,dc=local" -Properties lastLogon | Export-Csv users.csv

    That will give you a list of all the user's last domain logon times.

    Tuesday, September 10, 2013 11:17 AM
  • SCCM will deals with  computers also. So i just need the details of computers which are not active for a while.


    • Edited by don'zz Tuesday, September 10, 2013 12:29 PM
    Tuesday, September 10, 2013 11:18 AM
  • What about the default SRS reports in category Client Status, i.e. Inactive Client Details?

    Torsten Meringer |

    Tuesday, September 10, 2013 11:41 AM
  • i have seen the default reports section. In that Inactive client details report is available but it is not showing computers which inactive. 


    Tuesday, September 10, 2013 11:43 AM
  • That report does list inactive clients if there are inactives ones. Have you already checked if there are inactive ones according to \Monitoring\Overview\Client Status\Client Activity

    Torsten Meringer |

    Tuesday, September 10, 2013 12:01 PM
  • Yes as per the list there is only one inactive client (as per \Monitoring\Overview\Client Status\Client Activity). But in console while viewing all systems there is one more PC which doesn't have any activity. I mean in the activity column nothing is there.


    • Edited by don'zz Tuesday, September 10, 2013 12:17 PM
    Tuesday, September 10, 2013 12:09 PM
  • Is this a resource with a client installed (client = yes)?

    Torsten Meringer |

    Tuesday, September 10, 2013 12:33 PM
  • Yes it shows client installed 


    Tuesday, September 10, 2013 1:04 PM
  • The following code gives the report for inactive clients..

    SELECT coll.Name AS NetBiosName, cs.LastMPServerName, cs.LastOnline, coll.SiteCode, cs.LastHealthEvaluation, 
           (N'CH_LastEvaluationHealthy' + CAST(cs.LastEvaluationHealthy AS NCHAR(1))) AS LastResult, 
           (N'CH_ClientState' + CAST(cs.ClientState AS NCHAR(1))) AS ClientStateDescription
    FROM   fn_rbac_FullCollectionMembership(@UserSIDs)  coll 
           LEFT OUTER JOIN fn_rbac_CH_ClientSummary(@UserSIDs)  cs ON coll.ResourceID = cs.ResourceID
    WHERE  coll.CollectionID = @CollID  AND cs.ClientActiveStatus = 0 

    I want to modify this in a way that it should list last logon time also 


    • Edited by don'zz Wednesday, September 11, 2013 3:05 AM
    Wednesday, September 11, 2013 2:59 AM
  • Refer the query in the link.

    Juke Chou
    TechNet Community Support

    Wednesday, September 11, 2013 5:44 AM
  • Hi Juke,

    Thanks for the reply. Can you tell me where i should run this query


    Wednesday, September 11, 2013 6:01 AM
  • This is a SQL query and has to be run in SQL Management Studio or can be used in a SRS report.

    Torsten Meringer |

    Wednesday, September 11, 2013 3:52 PM