none
Query to Show Only Active Computers

    Question

  • We have tried to use the query below to show computers which have not been active for 30 days or more.  WSUS shows PCs that have been seen within the last 30 days (as i have just run the cleanup routine).

    However, for some reason SCCM shows:
    Total PCs = 15479
    PCs not seen for 30 days or more = 1439

    WSUS total computers = 9273

    So 15,479 - 1439 = 14,040 - 9273 = 4,767.  That is a considerable difference between the two figures.

    What is going on??!!

    I beleive that we really have about 10k-11k active machines, and i want to remove inactive machines but the AD discovery is a problem bringing in the old ____ again.  So this query is essential

    Any help much apprecaited!!.

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=30) and AgentName = "Heartbeat Discovery"))

    Thursday, November 25, 2010 3:03 PM

Answers

  • I would check the computers if they are active or not based on its inventory information when the computer has sent its last hardware scan by which we can easily say,client is Active or not.

    Here is the collection which gives you list of computers which are not sent their inventory and computers that doesnt even have inventory on the client(empty).

    select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 30) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS)

    But Yes,if you are looking for the clients status based on its discovery method,here you go with report:

    select a.Name0,b.AgentName,b.AgentTime from v_R_System a
    join v_AgentDiscoveries b on b.ResourceId=a.ResourceId where
    (AgentName  like 'Heartbeat Discovery' and DATEDIFF(Day,AgentTime,Getdate())>=30 ) OR
    (AgentName  like 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and DATEDIFF(Day,AgentTime,Getdate())>=30 )

    group by name0 ,agentname,AgentTime


    //Eswar Koneti @ http://eskonr.wordpress.com/
    • Marked as answer by Eric Zhang CHN Friday, December 03, 2010 8:01 AM
    Thursday, November 25, 2010 4:39 PM

All replies

  • If you have ConfigMgr 2007 R2 you should consider installing Client Status reporting tools. It is included on the R2 media.

    http://technet.microsoft.com/en-us/library/cc161956.aspx

     

    Thursday, November 25, 2010 3:31 PM
  • You do not have to do these thing manually. Use the "Site Maintenance Task" to perform the removing the inactive clients from the SCCM.

    You could enable the following two task for your site.

    Delete Inactive Client Discovery Data - http://technet.microsoft.com/en-us/library/bb693646.aspx

    Delete Aged Discovery Data Task Overview - http://technet.microsoft.com/en-us/library/bb693856.aspx

    Check the following link for information about all the Maintenance Tasks available in SCCM.

    http://technet.microsoft.com/en-us/library/bb632595.aspx


    Regards, Madan
    Thursday, November 25, 2010 4:28 PM
  • I would check the computers if they are active or not based on its inventory information when the computer has sent its last hardware scan by which we can easily say,client is Active or not.

    Here is the collection which gives you list of computers which are not sent their inventory and computers that doesnt even have inventory on the client(empty).

    select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 30) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS)

    But Yes,if you are looking for the clients status based on its discovery method,here you go with report:

    select a.Name0,b.AgentName,b.AgentTime from v_R_System a
    join v_AgentDiscoveries b on b.ResourceId=a.ResourceId where
    (AgentName  like 'Heartbeat Discovery' and DATEDIFF(Day,AgentTime,Getdate())>=30 ) OR
    (AgentName  like 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and DATEDIFF(Day,AgentTime,Getdate())>=30 )

    group by name0 ,agentname,AgentTime


    //Eswar Koneti @ http://eskonr.wordpress.com/
    • Marked as answer by Eric Zhang CHN Friday, December 03, 2010 8:01 AM
    Thursday, November 25, 2010 4:39 PM
  • How resource intensive is 'Client Status Reporting'?  Is it a significant overhead?  I do not want to install it on the Central Site Server, but would consider installing it on a site server.  The site server already hosts the following, and as Mike says we have 10-11K active clients.

    Asset Intelligence Sync Point

    Management Point

    Distribution Point

    Fallback Status Point

    PXE Service Point

    Reporting Point

    Reporting Services Point.

     

    In terms of hardware it has 8gb RAM, 2 physical Intel Xeon 2.8GHZ (X64) processors both with 4 cores each.  Looking at the server stats it isn't particularly working hard.

     

    thanks.

    Tuesday, November 30, 2010 10:12 AM
  • Please check: http://technet.microsoft.com/en-us/library/cc161840.aspx

    Yes, generates lot network trafic as it pings each systems in the SCCM database and for performance reasons, Microsoft recommends installing client status reporting on a server other than the server hosting the Configuration Manager 2007 site database.


    Regards, Madan
    Tuesday, November 30, 2010 11:59 AM
  • Thanks, I have seen that document already.

    My question was more around if I installed Client Status Reporting on the server I have given the spec for, would I expect to see an impact?  The server doesn't host the db.  I would like to know if can I get away with installing it here, or should I be introducing another server to the SCCM estate, someone must have some real life experience with this?

    I know this server is quite busy distributing software, and am conscious that Client Status Reporting generates a lot of network traffic, would this become a bottleneck for the primary task the server is commissioned to do 'software distribution'?

    I suppose I could install it and carefully monitor performance, and move it if need be.  Once Client Status Reporting has been introduced in a site can it be uninstalled and installed on another server without issue?

     

    thanks.

    Tuesday, November 30, 2010 2:36 PM
  • In terms of hardware it has 8gb RAM, 2 physical Intel Xeon 2.8GHZ (X64) processors both with 4 cores each.  Looking at the server stats it isn't particularly working hard. 
    It depends on the average load of the server (user perfmon or OpsMgr to get an idea). It won't add too much load, so it should work. In case it doesn't: it can be moved to another box.
    Tuesday, November 30, 2010 2:44 PM
    Moderator
  • I would check the computers if they are active or not based on its inventory information when the computer has sent its last hardware scan by which we can easily say,client is Active or not.

    Here is the collection which gives you list of computers which are not sent their inventory and computers that doesnt even have inventory on the client(empty).

    select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 30) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS)

    But Yes,if you are looking for the clients status based on its discovery method,here you go with report:

    select a.Name0,b.AgentName,b.AgentTime from v_R_System a
    join v_AgentDiscoveries b on b.ResourceId=a.ResourceId where
    (AgentName  like 'Heartbeat Discovery' and DATEDIFF(Day,AgentTime,Getdate())>=30 ) OR
    (AgentName  like 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and DATEDIFF(Day,AgentTime,Getdate())>=30 )

    group by name0 ,agentname,AgentTime

     


    //Eswar Koneti @ http://eskonr.wordpress.com/


    Sorry for not replying until now - got a detached retina and been out of action.

    Thanks for this relpy Eswar, most helpful.

    However, the collection gives me 5636 devices and the report only gives me 2607, considerably less than WSUS is reporting as live machines (8715).

    How do you interpret my results of your query and report?  Again, where does/do the descrepency/cies lie?

    Thursday, January 20, 2011 3:13 PM
  • However, the collection gives me 5636 devices and the report only gives me 2607, considerably less than WSUS is reporting as live machines (8715).

    can you be more specific on devices 5636,are these clients with SCCM client installed(i.e total number of clients in collection) or Total resources in collection ?

    Which report are you running in getting 2607 computers only ?


    //Eswar Koneti @ http://eskonr.com/
    Thursday, January 20, 2011 3:24 PM
  • can you be more specific on devices 5636, are these clients with SCCM client installed(i.e total number of clients in collection) or Total resources in collection ?

    Which report are you running in getting 2607 computers only?

    Sorry, the majority of the 5636 devices DO NOT have the client installed.  I am not sure of the difference between clients in collection or total resources - We only have computers in SCCM (no printers or servers for example).

    Which report? Your report below! :)

    select a.Name0,b.AgentName,b.AgentTime from v_R_System a
    join v_AgentDiscoveries b on b.ResourceId=a.ResourceId where
    (AgentName  like 'Heartbeat Discovery' and DATEDIFF(Day,AgentTime,Getdate())>=30 ) OR
    (AgentName  like 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and DATEDIFF(Day,AgentTime,Getdate())>=30 )

    Monday, January 24, 2011 3:41 PM
  • Total Number of Resource in collection-->Means,the computers that are discoverd and listed in collection (doesnt matter if it assigned to site or Client is installed what ever reason so)

    Total Number of clients in collection-->Means,computers that Active with SCCM client installed.

    Okay,the report gives the computers where in,either Heartbeat Discovery or System Disovery is Greater than 30 Days which requires your attention on this.

    What is the Schedule Discovery set for Hearbeat /System Discovery Agent ?


    //Eswar Koneti @ http://eskonr.com/
    Monday, January 24, 2011 4:19 PM
  • What is the Schedule Discovery set for Hearbeat /System Discovery Agent ?
    //Eswar Koneti @ http://eskonr.com/


    Heartbeat Discovery is every 3 hours

    AD System Discovery is daily.

    Tuesday, January 25, 2011 9:39 AM
  • Probably,you can check the system Discovery Logs(adsysdis.log) if these computers are on network or not ? since the discovery date is Old.

    Do you have any Disabled computers or computers that are not connected to Network.this could be one of the reason,why the count shows more clients and the report uses OR condition (if either is True, it will display output).

     


    //Eswar Koneti @ http://eskonr.com/
    Tuesday, January 25, 2011 1:43 PM