Thursday, April 12, 2007

DateAdd in Query/Collection

Many of you have already discovered some of the nice features and added benifits of upgrading to Sp 2 for SMS 2003. What you might not be aware of is that you can now use the DateAdd function in your query. The only down side is that you can only add it while editing the query manually. There is no WYSISYG method of adding or editing it. So once you add it you no longer have the editor.

Below is an example of how to use it to keep a computer in a collection for 2 days based on the MCNSDATA.ImageDate. I will describe this more later. Basically we are using a custom MIF file to add a computer to a new computer collection where it will stay until the end of the second day. It will then automatically remove itself. Expect to see more of the "New Computer" subject. I will have more about what we are doing with new computer provisioning.
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_MCNSDATA on SMS_G_System_MCNSDATA.ResourceID = SMS_R_System.ResourceId where SMS_G_System_MCNSDATA.ImageDate >= DateAdd(dd,-2,GetDate())