collection for systems (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

MeenEnta -> collection for systems (9/17/2008 11:28:52 AM)

This script supposedly creates a collection of systems not seen in at least 30 days.
but when i run it, it gives me all my working and available systems

Do you see anything wrong with the following?
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_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId and SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-30,GetDate())



jnelson993 -> RE: collection for systems (9/17/2008 12:06:30 PM)

I think you want <=   not >=

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_WORKSTATION_STATUS
    ON SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId
       AND SMS_G_System_WORKSTATION_STATUS.LastHardwareScan <= DateAdd(dd,-30,GetDate())



MeenEnta -> RE: collection for systems (9/17/2008 12:11:50 PM)

That did it, thank you "Sensei" John.

The logic is interesting.



jnelson993 -> RE: collection for systems (9/17/2008 12:32:22 PM)

You're very welcome.


To me the logic makes perfect sense.  You're basically saying to SQL...

Take today's date, and subtract 30 days from it.  (so 9/17/2008 becomes 8/18/2008)
Now return only those LastHWScan records that have a date less than or equal to that date (8/18/2008).



MeenEnta -> RE: collection for systems (9/17/2008 1:00:40 PM)

So you are saying above will limit to within 30 days.

How can I change it to return any machine not seen for more than 30 Days? 



jnelson993 -> RE: collection for systems (9/17/2008 1:21:50 PM)

No, what I'm saying is, with the LESS THAN instead of the GREATER THAN, you are already returning only those records that haven't returned a HW scan for 30 days or greater.

Look at the comparison statement:
SMS_G_System_WORKSTATION_STATUS.LastHardwareScan <= DateAdd(dd,-30,GetDate())

This says, for every LastHardwareScan record in the SMS_G_System_WORKSTATION_STATUS class on the left side of <= , return those with a date that's less than whatever is on the right side of <=.

Perhaps it would help to use some real values

Let's say you have the following values in LastHardwareScan

2008-07-17 12:07:20.350
2008-08-17 12:07:20.350
2008-09-17 12:07:20.350

And let's assume the current date is
2008-09-17 12:07:20.350

So when the records get evaluated, it will use that comparison statement you have.  So let's use the values above and substitute them into the statement

First, let's replace SMS_G_System_WORKSTATION_STATUS.LastHardwareScan with the first value:
2008-07-17 12:07:20.350

Next, let's evaluate that DATEADD stuff on the right
DateAdd(dd,-30,2008-09-17 12:07:20.350)becomes
2008-08-18 12:07:20.350  (which is today MINUS 30 days)

So, is 2008-07-17 12:07:20.350 really less than or equal to 2008-08-18 12:07:20.350?
The answer is yes, the record is less than, so that record shows up.

Moving on to the next sample value, is 2008-08-18 12:07:20.350 really less than or equal to 2008-08-18 12:07:20.350?
The answer is yes, they're equal, so that record shows up.

Now the last sample value...is 2008-08-18 12:07:20.350 really less than or equal to 2008-09-18 12:07:20.350?
The answer is no, it's greater than, so that record DOES NOT show up.

This make any sense?



jnelson993 -> RE: collection for systems (9/17/2008 1:24:00 PM)

I think perhaps your confusion is that you're thinking in number of days, but we're doing the comparison based on the date value itself, not the number of days.



MeenEnta -> RE: collection for systems (9/17/2008 5:58:04 PM)

Thx John, and your explanation do make sense, especially "we're doing the comparison based on the date value itself, not the number of days. "



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.203125