...your virtual communITy
Welcome to Sign in | Join | Help
in Search

Garth Jones

Using Datediff & GetDate in WQL

Normally I wouldn’t post a reply to blog post but in this case I had too.

So I was reading this blog Creating a Collection Query based upon Hardware Inventory, and this blog too and I notice that Chris posted a blog about editing your ConfigMgr / SMS database.

  1. You SHOULD never edit your database! This is just asking for trouble.
  2. Editing your database directly is NOT SUPPORTED by Microsoft; it is one thing to use the SDK to make changes to ConfigMgr / SMS but…
  3. The problem he is trying to solved was fixed in SMS 2003 SP1 or SP2.

Now the problem as I see it, is he want to create a collection of all WS that have provided hardware inventory in the last 5 days. Well back in SMS 2003 SP1 (maybe SP2) Getdate(), DateDiff() and DateAdd() were added to WQL functionality. So there is no need to do this hack and it is unnecessary to do this.


In Greg’s blog, He provide the WQL code to:

  • All systems that have reported a LastHardwareScan date within the last 30 days:
  • All systems that have been discovered since midnight:


I have created this WQL query to more or less matching what Chris is looking for.





SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId


DATEDiff(DD, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, Getdate()) < 5

Published Apr 22 2009, 08:35 PM by Garth
Filed under: , ,



Joe Holt said:

Ive tried playing with this code on my database and it worked flawlessly. Thanks

July 29, 2010 12:10 AM

This Blog




Powered by Community Server (Non-Commercial Edition), by Telligent Systems