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
13
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: 6  
posted: 2/23/2018 1:36:35 AM(UTC)
Very cool!
harringg
#5harringg Member Posts: 123  
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: 1  
posted: 3/21/2018 8:07:45 PM(UTC)
I received the same error as laurentiun when trying to create the report.
Charles.X
#8Charles.X Member Administration Posts: 1,250  
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: 24  
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: 7  
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: 12  
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

Lansweeper How to deploy Skype MSI package with admin rights
by  zagard   Go to last post Go to first unread
Last post: 1/9/2019 7:28:21 PM(UTC)
Action Powershell script for WOL on VLAN
by  HKLM_  
Go to last post Go to first unread
Last post: 11/1/2018 4:15:02 PM(UTC)
Action System Information (Windows)
by  cjcox   Go to last post Go to first unread
Last post: 10/17/2018 7:37:28 PM(UTC)
Action Apple support links
by  roberto.m  
Go to last post Go to first unread
Last post: 10/15/2018 1:45:58 PM(UTC)
Lansweeper RemoteUninst.exe & shellrunas.exe
by  ehbilodeau   Go to last post Go to first unread
Last post: 10/10/2018 3:03:40 PM(UTC)
Lansweeper TeamViewer Remote App
by  studerje  
Go to last post Go to first unread
Last post: 10/9/2018 11:32:30 PM(UTC)
Action Dameware Remote Support tool
by  Moorken   Go to last post Go to first unread
Last post: 9/27/2018 9:52:07 AM(UTC)
Lansweeper Custom Action that calls report
by  SPAMHATER007  
Go to last post Go to first unread
Last post: 9/14/2018 9:59:13 PM(UTC)