collection for systems

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

collection for systems

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

collection for systems - 9/17/2008 11:28:52 AM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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

Post #: 1

RE: collection for systems - 9/17/2008 12:06:30 PM 1 votes

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to MeenEnta)

Post #: 2

RE: collection for systems - 9/17/2008 12:11:50 PM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

That did it, thank you "Sensei" John.

The logic is interesting.

(in reply to jnelson993)

Post #: 3

RE: collection for systems - 9/17/2008 12:32:22 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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).

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to MeenEnta)

Post #: 4

RE: collection for systems - 9/17/2008 1:00:40 PM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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?

(in reply to jnelson993)

Post #: 5

RE: collection for systems - 9/17/2008 1:21:50 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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?

< Message edited by jnelson993 -- 9/17/2008 1:24:48 PM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to MeenEnta)

Post #: 6

RE: collection for systems - 9/17/2008 1:24:00 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 7

RE: collection for systems - 9/17/2008 5:58:04 PM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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. "

(in reply to jnelson993)

Post #: 8