Ok, so as I discussed in my previous post I built multiple collections that determine client inventory health.
So the end result is the ability to build collections based off of machines who haven’t reported to one or more inventory types for X number of days and have the script deployed to them auto-magically with the corresponding inventory type.
This is one of the areas where SCCM (from the console level) becomes pretty restrictive. In MSSQL you have access to a couple of functions that make determining time frames very simple. In this case DATEDIFF and GETDATE which combined provide a nice simple statement for determining a fixed time frame.
DATEDIFF(d, column-to-check, GETDATE()) >= 30
Ok, great. So what’s the problem? Well when building collections in SCCM you have to use WQL, and it doesn’t have such a robust function set. Fortunately there is a way around this, and I’m going to give a few SQL queries you can use to build some inventory health collections.
Start by building a new empty collection, and during the creation process make a standard query rule and save it. Now right click and go to properties of your new collection and copy it’s Collection ID.
Now, open up your sql management studio and connect to your site server DB. Make sure you are connected to the proper DB and run the following query:
select QueryExpression from v_CollectionRuleQuery where CollectionID = '(pasted collection id)'
What you should see is the SQL being used on the back end to present to your console. Now what you need to do is update this column to match whatever query you need. Bear in mind, when you are redefining things inside this query they need to match the WQL schema for the console to get the information it needs. I’ll show you examples later. I also want to point out, this is not supported by Microsoft, and if you don’t properly structure your Update statement you could end up hosing all your collections, so be safe. Now here is how you do it.
Update v_CollectionRuleQuery Set QueryExpression = ‘whatever query statement you want’ where CollectionID = ‘(pasted collection id)’
Now when you update membership and refresh that collection you should see results to match your query. Ok, now here’s the kicker. Never modify this collections query properties from inside SCCM, the changes you made will be overwritten if you do.
Now, about those queries I promised you.
--No Inventory for 30 days SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0,SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 FROM System_DISC AS SMS_R_System JOIN softwareinventorystatus AS sw ON SMS_R_System.ItemKey = sw.ClientId JOIN WorkstationStatus_DATA AS wks ON wks.MachineID = sw.ClientId WHERE DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 30 AND DATEDIFF(dd,wks.LastHWScan,GETDATE()) > 30
--No Hinv 30 days SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0, SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 FROM System_DISC AS SMS_R_System JOIN WorkstationStatus_DATA AS wks ON wks.MachineID = sms_r_system.ItemKey WHERE DATEDIFF(dd,wks.LastHWScan,GETDATE()) > 30
--No Sinv 30 days SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0, SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 FROM System_DISC AS SMS_R_System JOIN softwareinventorystatus AS sw ON SMS_R_System.ItemKey = sw.ClientId WHERE DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 30
--Client with no DDR SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0, SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 FROM System_DISC AS SMS_R_System JOIN softwareinventorystatus AS sw ON SMS_R_System.ItemKey = sw.ClientId JOIN WorkstationStatus_DATA AS wks ON wks.MachineID = sw.ClientId WHERE DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 5 AND DATEDIFF(dd,wks.LastHWScan,getdate()) > 5 AND SMS_R_System.Client0 = 0
If you need to specify a collection for exclusion (which if you build all of these you may want to exclude machines in the NO inventory collection) append this to the end of your query.
and SMS_R_System.ItemKey not in (select ResourceID from v_cm_res_coll_CollectionID)
And there you have it. When writing your update line I would change these sql queries to be one line and remove the comment obviously. There is also a LimitToCollectionID column you might want to apply any specific restrictions to.
Hopefully these past two posts help a few people looking to take an automated stance to proactively resolving inventory reporting issues on clients. For anyone else:
- Hopefully you learned something about sql?
- You got a nice refresher on the collection query views name?
Have a good one.