Notification

Icon
Error

Workstations With Recent BSOD - Identify computers that have recently experienced a Blue Screen of Death :(

Posted: Monday, November 6, 2017 7:34:30 PM(UTC)
jperry

jperry

Member Original PosterPosts: 15
14
Like
Uses windows event log Microsoft-Windows-WER-SystemErrorReporting and code 1001

Code:
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
laurentiun
#1laurentiun Member Posts: 13  
posted: 2/9/2018 4:22:09 PM(UTC)
error:

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

jacob_bks
#2jacob_bks Member Posts: 49  
posted: 2/10/2018 8:10:58 PM(UTC)
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?

Yoginath
#3Yoginath Member Posts: 2  
posted: 2/21/2018 4:20:35 PM(UTC)
Great Efforts !! ThanksApplause Applause
stevce.brazeau
#4stevce.brazeau Member Posts: 7  
posted: 2/23/2018 1:36:35 AM(UTC)
Very cool!
harringg
#5harringg Member Posts: 124  
posted: 2/27/2018 11:09:10 PM(UTC)
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. :-)
Daniel Red
#6Daniel Red Member Posts: 4  
posted: 3/8/2018 2:10:09 PM(UTC)
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?
SAHarrington
#7SAHarrington Member Posts: 2  
posted: 3/21/2018 8:07:45 PM(UTC)
I received the same error as laurentiun when trying to create the report.
Esben.D
#8Esben.D Member Administration Posts: 1,632  
posted: 11/13/2018 4:10:41 PM(UTC)
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.
pfenton
#9pfenton Member Posts: 26  
posted: 12/5/2018 4:32:28 PM(UTC)
I am getting zero hits. The report description says devices that have recently experienced a BSOD. What exactly is meant by "recently"?
Diego Tomazzoni
#10Diego Tomazzoni Member Posts: 14  
posted: 12/5/2018 4:32:46 PM(UTC)
Very nice! Applause
Rick Knight
#11Rick Knight Member Posts: 1  
posted: 12/5/2018 5:01:16 PM(UTC)
This looks like a great tool, but I am unable to save and run the report. I get this message when I try
Quote:
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
HappyHeathen
#12HappyHeathen Member Posts: 9  
posted: 12/5/2018 6:47:45 PM(UTC)
Originally Posted by: pfenton Go to Quoted Post
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

chris.johnson@mondigroup.com
posted: 12/5/2018 6:59:31 PM(UTC)
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
Rob B
#14Rob B Member Posts: 9  
posted: 12/6/2018 6:27:03 PM(UTC)
Fabulous. Nicely done. I did similar to what HappyHeathen did; changed history to 10 days.
StephanieCDA
#15StephanieCDA Member Posts: 15  
posted: 12/19/2018 6:59:54 PM(UTC)
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.
Viper
#16Viper Member Posts: 5  
posted: 1/9/2019 7:11:03 PM(UTC)
Expressions in the ORDER BY list cannot contain aggregate functions.

Active Discussions

Report Center Failed Logins Report/Chart
by  Cindy.C   Go to last post Go to first unread
Last post: Today at 11:05:12 AM(UTC)
Lansweeper Microsoft Patch Tuesday Report - May 2019
by  jase  
Go to last post Go to first unread
Last post: Yesterday at 10:17:06 PM(UTC)
Lansweeper Report of Monitors Connected To PC's
by  meddlema   Go to last post Go to first unread
Last post: Yesterday at 10:04:39 PM(UTC)
Lansweeper Windows 7 Assets, their user, and users manager
by  Jeff Hilmo  
Go to last post Go to first unread
Last post: Yesterday at 6:51:55 PM(UTC)
Lansweeper Firefox 67 Critical Security Issue
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 11:40:51 AM(UTC)
Report Center BIOS version check
by  Esben.D  
Go to last post Go to first unread
Last post: 5/22/2019 1:22:28 PM(UTC)
Lansweeper report in defined period
by  CHIREC   Go to last post Go to first unread
Last post: 5/22/2019 10:46:34 AM(UTC)