Workstations With Recent BSOD

[jperry]

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

[Viper]

Expressions in the ORDER BY list cannot contain aggregate functions.

[StephanieCDA]

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]

Fabulous. Nicely done. I did similar to what HappyHeathen did; changed history to 10 days.

[chris_johnson]

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]

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]

Very nice!

[pfenton]

I am getting zero hits. The report description says devices that have recently experienced a BSOD. What exactly is meant by "recently"?

[HappyHeathen]

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]

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.