Using Datediff & GetDate in WQL

by | Apr 22, 2009 | odds and ends

Last updated on September 28th, 2022 at 09:30 am

As a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr) Adminstrators. Knowing WQL is important! But How do you use Datediff & GetDate in WQL? This blog will talk about for you.

Normally I wouldn’t post a reply to blog post but in this case I had too. Using Datediff & GetDate in WQL within MECM can be hard. Creating collections use dates is difficult.

Datediff & GetDate in WQL

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. https://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

Looking at the query above you can see that it has both datediff and getdate WQL functions! From this you should be able to create your own WQL queries.

Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.

Code , ConfigMgr , SMS