‎11-06-2017 07:34 PM
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlog.Eventcode,
Max(tblNtlog.TimeGenerated) As LastOccurrence,
Count(tblNtlog.TimeGenerated) As Instances,
tblNtlogSource.Sourcename,
tblNtlogMessage.Message,
tblAssetCustom.Location,
tblAssets.Lastseen,
tblADusers.Username,
tblADusers.Userdomain,
tblAssetCustom.Model,
tblOperatingsystem.Version As [OS Version],
tblOperatingsystem.Caption As [OS Name],
tsysIPLocations.IPLocation,
tblAssets.Description As [LS Description],
tblADComputers.Description As [AD Description]
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Outer Join tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where
tblNtlogSource.Sourcename Like '%Microsoft-Windows-WER-SystemErrorReporting%'
And tblNtlog.Eventcode = 1001 And tblNtlog.TimeGenerated > GetDate() - 3
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
tblNtlogMessage.Message,
tblAssetCustom.Location,
tblAssets.Lastseen,
tblADusers.Username,
tblADusers.Userdomain,
tblAssetCustom.Model,
tblOperatingsystem.Version,
tblOperatingsystem.Caption,
tsysIPLocations.IPLocation,
tblAssets.Description,
tblADComputers.Description,
tblADComputers.OU
Order By Count(tblNtlog.TimeGenerated) Desc,
LastOccurrence Desc,
tblAssets.AssetName
‎03-21-2018 08:07 PM
‎03-08-2018 02:10 PM
‎02-27-2018 11:09 PM
‎02-23-2018 01:36 AM
‎02-21-2018 04:20 PM
‎02-10-2018 08:10 PM
‎02-09-2018 04:22 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now