Chris Nackers Blog

ConfigMgr and MDT Deployment Solutions

Useful Blogs

User Groups

Creating a Collection Query based upon Hardware Inventory

Credit to Dave Fuller for which this information is based upon.

I posted previously on how to use a SQL statement for updating collection membership.  I thought I would show an example this time around.

 

So, we want to create a collection that is based up on hardware inventory having run in the past 5 days.  So if the client hasn’t run hardware inventory in the past 5 days, i don’t want to see him in my collection.

So lets create the collection.

image

Now lets create a query statement for that collection.

image

Verify that the rule has no criteria to it.

image

Now we need to identify the “CollectionID/Store Name”

image

Which we can see is “SZ000389”

So now lets run a query in the query analyzer to find the CollectionID as it’s listed in SQL.

image

Now we have been able to tie the CollectionID that we see in the admin console with the ID that is listed in SQL, which is the ID we will need to update the SQL command.

Here is the query we want to update the collection with:

select sys.ItemKey, sys.DiscArchKey, sys.Name0, sys.SMS_Unique_Identifier0, sys.Resource_Domain_OR_Workgr0, sys.Client0
from System_DISC AS sys
JOIN WorkstationStatus_DATA AS wks on sys.ItemKey = wks.MachineID
where DATEDIFF(dd,wks.LastHWScan,GetDate()) < 5

This query will only show clients that have run a hardware inventory within the past 5 days.

So lets run this in the query analyzer and make sure we can return some valid results. I blocked out some of my data on this screenshot.

image

Ok so we validated that my query actually works and returns valid results. So lets update our collection with this query so we can use it.

So we need to run an update command and replace the current SQL query for the collection with the one i want to use.

I know that my CollectionID is ‘917’ in this instance. So my update query is as follows:

Update Collection_Rules_SQL
set SQL = 'select sys.ItemKey, sys.DiscArchKey, sys.Name0, sys.SMS_Unique_Identifier0, sys.Resource_Domain_OR_Workgr0, sys.Client0
from System_DISC AS sys
JOIN WorkstationStatus_DATA AS wks on sys.ItemKey = wks.MachineID
where DATEDIFF(dd,wks.LastHWScan,GetDate()) < 5'
where CollectionID = 917

So once i run that command i should see the following result.

image

Now i can go back to my admin console and update the collection membership.

image

And now when i look at my collection, I should see the same results that i saw in the SQL query analyzer.

Now you could also flip that query around and show clients that haven’t reported in a certain amount of days or whatever you are trying to achieve.

Comments

No Comments