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

[SAHarrington]

I received the same error as laurentiun when trying to create the report.

[Daniel_Red]

Hmm, I am not getting any hits, I think i might not be scanning that logfile. What exactly do I need to enable to collect this data?

[harringg]

Thanks! Just had a customer ask me today if I knew why their PC went into BSOD. I'm now able to (quickly) tell them. 🙂

[stevce_brazeau]

Very cool!

[Yoginath]

Great Efforts !! Thanks

[jacob_bks]

Thanks - that's a really great report format!!

It works for me both in SQL Express and full MSSQL - not sure why you're getting an error?

[laurentiun]

error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 319,Token in error = Left ]