Evenlog ID Query

[andre_rocha]

Hello all,

How can i make changes to the following query in order to get unique values from each device instead of getting multiple results for each device?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Where tblNtlog.Eventcode = 10

[Hemoco · Accepted solution]

If you want to list events that occurred in the last day, you need to add tblNtlog.TimeGenerated to your report and filter this expression. At the moment, you are NOT listing events that occurred in the last day. You filtered the tblAssets.Lastseen column, which stores an asset's scan date, not the date/time an event was generated.

You can also use the MAX function to list the most recent instance of the event for each machine: http://technet.microsoft.com/en-us/library/ms187751.aspx

[andre_rocha]

Made some changes including only events from last day. reduced a lot the results but still many. Getting single result per device would be the ideal.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Where tblAssets.Lastseen > GetDate() - 1 And tblNtlog.Eventcode = 10
Order By tblAssets.AssetName