Help from a Reporting GURU Need?

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:

Help from a Reporting GURU Need?

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 >> Help from a Reporting GURU Need?

Page: [1]

Message

<< Older Topic Newer Topic >>

Help from a Reporting GURU Need? - 11/9/2006 5:10:59 AM

lkasper318

Posts: 7
Score: 0
Joined: 12/23/2005
Status: offline

Guys,

I have made a report which replicates data from the Package Status inside the console, and I'm after the last 12 hours of status messages of data from the OSD/RIS information....

select v_StatusMessage.SiteCode, v_StatusMessage.Time, v_StatusMessage.MachineName, v_StatusMessage.Component, v_StatusMessage.MessageID, v_AdvertisementStatusInformation.MessageStateName, v_AdvertisementStatusInformation.MessageName
from v_StatusMessage
join v_AdvertisementStatusInformation on v_StatusMessage.MessageID = v_AdvertisementStatusInformation.MessageID
where (v_StatusMessage.MessageID >= 11100 and v_StatusMessage.MessageID <= 11199) and v_StatusMessage.Time > DATEADD(ss,-@__timezoneoffset-(12*3600),GetDate()) and v_StatusMessage.SiteCode = 'P0A' order by v_StatusMessage.Time desc

What I found was that it was giving me the last 12 hours of messages but the time seems to be 10 hours earlier...

I know it has something to do with the timezoneoffset component of the DATEADD command, but I can't find any information regarding this command...

Help?

Thanks
Lloyd

Post #: 1

RE: Help from a Reporting GURU Need? - 11/12/2006 4:21:27 AM

eyona

Posts: 300
Score: 30
Joined: 8/17/2003
From: israel
Status: offline

the datetime fields in SMS are GMT 0
so to get the real time you need to add the @__timezoneoffset variable to the field
in your query while you add it in the where section for filtering you do not
include it in the select section (the columns to display) you need to have a colum
like dateadd(ss,@__timezoneoffset,v_statusmessage.time)

(in reply to lkasper318)

Post #: 2