Blue screen- Report

[Martyn_Umpleby]

Hi,

I'm looking to create a blue screen report that would show all the assets that have had Blue screen in the last 30 days ideally and then be able to drill down to the event log reported/scanned for each asset. I have run into a few issues with my attempts so far though as the report I have is not quite right and takes some time find all assets that have encountered issues in an easy dashboard.

This is what I have so far;

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID As ID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype When 1 Then 'Error' When 2 Then 'Warning'
When 3 Then 'Information' When 4 Then 'Success Audit'
When 5 Then 'Failure Audit' End As Eventtype,
tblNtlogFile.Logfile,
tblNtlogMessage.Message,
tblNtlogSource.Sourcename,
tblNtlogUser.Loguser,
tblNtlog.TimeGenerated
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblNtlogMessage.Message Like '%BlueScreen%' And
tblNtlogSource.Sourcename Like 'Windows Error Reporting' And
tblAssetCustom.State = 1
Order By tblNtlog.TimeGenerated Desc

The above shows all Blue screen events that have been scanned from the event viewer logs of each asset. However I would like all/ any events to be grouped and listed per asset for example if 50 events across 5 assets then they would show the following;

AssetName | Times Occurred | Last event
Asset1 | 25 times occurred | 18/04/17
Asset2 | 10 times occurred | 15/04/17
Asset3 | 10 times occurred | 14/04/17
Asset4 | 3 times occurred | 12/04/17
Asset5 | 2 times occurred | 13/04/17

This would be in ascending order, 'Times occurred first then Last Event'

If this is possible then have the ability to click the asset of times occurred to get a more detailed report like the report first listed above but just for the asset selected.

Thanks in advance.

[Tom_P · Accepted solution]

Hi,

You can use the report below that counts the number of events and lists the last message date per asset. Unfortunately, there is no option within Lansweeper to add a 'click through' based on a custom calculated field from one custom report to another.

Select Top 1000000
  tblAssets.AssetName,
  COUNT(tblNtlogMessage.MessageID) as [Times Occured],
  MAX(tblNtlog.TimeGenerated) as [Last Event]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
  Inner Join tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
  Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
  Inner Join tblNtlogSource On tblNtlogSource.SourcenameID = tblNtlog.SourcenameID
  Inner Join tblNtlogUser On tblNtlogUser.LoguserID = tblNtlog.LoguserID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where
  tblNtlogMessage.Message Like '%BlueScreen%' And
  tblNtlogSource.Sourcename Like 'Windows Error Reporting' And
  tblAssetCustom.State = 1
Group by tblAssets.AssetName
Order By [Last Event] Desc