SCCM Collection based on LastOnlineTime

[DaveW]

I am trying to build a collection based on the Last Online time (within an arbitrary number of days). I know typically this is done using the Heartbeat, ClientActivity, or Hardware scan property. but I would like to see all machines, to identify which machines may not have the client installed. 

I cant for the life of me find where the column in the collections view populates from. 

I have the queries to filter out machines based on Last Hardware Scan shown below. I am looking for something very similar, but as I said, i cant find the property for "Last Online Time"

Quote

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 inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId 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()) > 90

 

 

[skissinger]

You are looking at the console; that's a custom view,  which is only available for reporting or the console; that "last online time" simply doesn't exist in the database as a "let's write wql queries against, for collection queries".

Basically I'm saying don't try to use it for a collection query.   The console is pretty and all... but I don't rely on it myself.  MANY things in the console are based on summarization tasks.  If you are in a smallish company (at least, smaller than what I have to deal with); sure, those summarization schedules and tasks complete efficiently and successfully.  Quite often for me they don't finish; so the console lies all the time for me.  It's convenient, sure... but not reliable for me at all.