cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jperry
Engaged Sweeper III
Uses windows event log Microsoft-Windows-WER-SystemErrorReporting and code 1001

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
16 REPLIES 16
Viper
Engaged Sweeper II
Expressions in the ORDER BY list cannot contain aggregate functions.
StephanieCDA
Engaged Sweeper III
This is great, no errors and ran nicely. Quick question: how can I change the third column OScode (10.0.16299) to output as the OS Version (1709 etc) instead? Thanks.
Rob_B
Engaged Sweeper III
Fabulous. Nicely done. I did similar to what HappyHeathen did; changed history to 10 days.
chris_johnson
Engaged Sweeper
For users with multiple workgroups/domains, the current report might show duplicate records if a username exists in more than one domain/workgroup. To avoid this, please change:
Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username
to
Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username and tblAssets.Userdomain = tblADusers.Userdomain
Rick_Knight
Engaged Sweeper
This looks like a great tool, but I am unable to save and run the report. I get this message when I try
Expressions in the ORDER BY list cannot contain aggregate functions.

What do I need to do to correct this so I can run the report?

Thanks,
Rick
Diego_Tomazzoni
Engaged Sweeper III
Very nice!
pfenton
Engaged Sweeper III
I am getting zero hits. The report description says devices that have recently experienced a BSOD. What exactly is meant by "recently"?
pfenton wrote:
I am getting zero hits. The report description says devices that have recently experienced a BSOD. What exactly is meant by "recently"?


Zero hits is a good sign. Means no BSODs recorded in the last 3 days.

[Line 36] --> will scan for errors in the last 3 days.
I changed "3" to "7" to list errors over the last 7 days

Esben_D
Lansweeper Employee
Lansweeper Employee
I've updated the original report to fix some of the issues people were having. Basically, I removed the picture column since it required the use of a LEFT function which is not compatible with SQL Compact. Therefore, people running the report on SQL Compact installations were getting errors.

The report should work now on all installations.