Need new SQL query for Events that occurred since 18:30 the prior day

[Drew Lehr]

Help wanted my current SQL query for inaccessible devices no longer works after the SIM 4 upgrade.

Description of what and why:
My helpdesk leaves at 6:30 pm and needs to generate a report each morning listing the servers that experienced issues during the night hours when no one is here to actively monitor it. Under CIM 7 I was able to use the following SQL query:

______________________________________________
declare @YesterdayDate varchar(20)
declare @TempDate datetime
set @TempDate = getdate() - 1
set @TempDate = cast(@TempDate as varchar(11))
set @YesterdayDate = cast(@TempDate as varchar(11)) + ' 18:30:00'

SELECT Cast (devices.Name as varchar(20)) as 'Server Name',
Max(Cast(Generated as varchar(23))) as 'Alarm Date',
Cast(stringTableLarge.largeValue as varchar(60)) as 'Alarm Description'

FROM INSIGHT_V1_0_143944718.dbo.devices devices, INSIGHT_V1_0_143944718.dbo.notices notices,
INSIGHT_V1_0_143944718.dbo.stringTableLarge stringTableLarge,INSIGHT_V1_0_143944718.dbo.noticeType noticeType,
INSIGHT_V1_0_143944718.dbo.stringResource stringResource

WHERE devices.DeviceKey = notices.DeviceKey AND notices.NoticeType = noticeType.NoticeType
AND Cast(noticeType.NoticeType as varchar(10)) = stringResource.subClass
AND stringResource.id = stringTableLarge.id
AND stringResource.resourceName='label' AND stringResource.resourceClassName='NoticeType'
AND ((notices.NoticeSeverity = '5' OR notices.NoticeSeverity = '4')
AND stringTableLarge.largeValue = 'Device Status Change')
-- The line above states to grab the alarms where the Device Status changed to Critical (5) or Major (4)
-- This is the equivalent of device inaccessible in previous CIM
AND Cast(Generated as varchar(11)) > @YesterdayDate

GROUP BY devices.Name,stringTableLarge.largeValue

ORDER BY Max(notices.Generated) asc
_______________________________________________

We run the SQL script using ISQL form the command line saving the output in a text file that we parse and automatically email to all who wish to recieve it. The new SIM 4 database changed the datatype time of the event to a java sql timestamp which is millieseconds since 01/01/1970 00:00:01

Sample of previous and desired output:
______________________________________________
psnva211 Oct 20 2004 10:23PM Device Status Change
csnva280 Oct 20 2004 10:44PM Device Status Change
psnva222 Oct 20 2004 11:26PM Device Status Change
psnva101 Oct 21 2004 12:34AM Device Status Change
ntprt28 Oct 21 2004 12:47AM Device Status Change

[Aravindh Rajaram]

Based on the info u have given,

select dateadd(day,(/86400000),'1970-01-01')

will give you the date associated with the timestamp. Hope you can carry on further.