Event Logs Filtered within the Past Hour

[cvhyatt]

Hello All

I am trying to generate a report that will filter my event logs for my servers within the last hour. Right now I can only figure out how to filter it within the last day. Instead of a GetDate, is there a "GetTime" or something similar I can use?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssetGroups.AssetGroup,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End As EventType,
tblNtlog.TimeGenerated,
tblNtlogSource.Sourcename,
tblNtlogFile.Logfile,
tblNtlogUser.Loguser,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetGroupLink On tblAssetGroupLink.AssetID = tblAssets.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup Like 'ABC_%' Or tblAssetGroups.AssetGroup Like
'123%' Or tblAssetGroups.AssetGroup = 'DC%') And tblNtlog.TimeGenerated >
GetDate() - 1 And tblNtlogFile.Logfile = 'System' And tblState.Statename =
'Active'
Order By tblNtlog.TimeGenerated Desc,
tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

Take a look at DateDiff().

e.g.

DateDiff(minute, tblNtlog.TimeGenerated, GetDate()) <= 60

I suggest doing the difference in minutes rather than hours to ensure that differences between 1h and 2h don't get truncated to 1 hour and lumped in with your results.