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: 14
12
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,172  
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: 6  
posted: 12/6/2018 6:27:03 PM(UTC)
Fabulous. Nicely done. I did similar to what HappyHeathen did; changed history to 10 days.

Active Discussions

Lansweeper Fixing LDAP Error when syncing AD Users
by  ttye   Go to last post Go to first unread
Last post: Yesterday at 10:09:26 PM(UTC)
Lansweeper Error saving Tab as Global
by  NeoIsTaken  
Go to last post Go to first unread
Last post: Yesterday at 9:55:49 PM(UTC)
Lansweeper automated reporting scheduler
by  AZHockeyNut   Go to last post Go to first unread
Last post: Yesterday at 7:00:54 PM(UTC)
Lansweeper Related KB articles when creating tickets
by  StephanieCDA  
Go to last post Go to first unread
Last post: Yesterday at 6:50:51 PM(UTC)
Lansweeper Maximum image size location map
by  Charles.X   Go to last post Go to first unread
Last post: Yesterday at 3:33:17 PM(UTC)
Lansweeper Ticket dispatching rules for web created tickets?
by  Charles.X  
Go to last post Go to first unread
Last post: 12/11/2018 5:49:44 PM(UTC)
Lansweeper User Portal: hide assigned Agent
by  Charles.X   Go to last post Go to first unread
Last post: 12/11/2018 5:39:56 PM(UTC)
Lansweeper What happens to tickets when users removed?
by  Charles.X  
Go to last post Go to first unread
Last post: 12/11/2018 5:34:31 PM(UTC)