SMSUG.ca

...your virtual communITy
Welcome to SMSUG.ca 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. http://msdn.microsoft.com/en-us/library/cc146121.aspx. 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.

 

select

*

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()) < 5

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

Comments

 

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

Syndication

News

Training

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