Event: Unexpected Reboot Count Last ~3 Months (More than 3)

[jperry]

This report was to help identify computer that were experiencing a large number of unexpected reboots.
Uses Windows Event: Microsoft-Windows-Kernel-Power event code 41 to identify occurrences.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Displayname,
  Count(tblNtlog.TimeGenerated) As Instances,
  Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,
  tblAssets.OScode,
  tblNtlog.Eventcode,
  Max(tblNtlog.TimeGenerated) As LastOccurrence,
  tblNtlogSource.Sourcename,
  tblNtlogMessage.Message,
  tblAssetCustom.Location,
  tblAssets.Lastseen,
  '<img src="thumbnail.aspx?user=' + tblADusers.Username + '&domain=' +
  tblADusers.Userdomain + '&size=16" class="rimage"/>' As Picture,
  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 tblNtlog.TimeGenerated > GetDate() - 90 And tblNtlogSource.Sourcename =
  'Microsoft-Windows-Kernel-Power' And tblNtlog.Eventcode = 41
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Displayname,
  tblAssets.OScode,
  tblNtlog.Eventcode,
  tblNtlogSource.Sourcename,
  tblNtlogMessage.Message,
  tblAssetCustom.Location,
  tblAssets.Lastseen,
  '<img src="thumbnail.aspx?user=' + tblADusers.Username + '&domain=' +
  tblADusers.Userdomain + '&size=16" class="rimage"/>',
  tblADusers.Username,
  tblADusers.Userdomain,
  tblAssetCustom.Model,
  tblOperatingsystem.Version,
  tblOperatingsystem.Caption,
  tsysIPLocations.IPLocation,
  tblAssets.Description,
  tblADComputers.Description,
  tblADComputers.OU
Having Count(tblNtlog.TimeGenerated) > 3
Order By Count(tblNtlog.TimeGenerated) Desc,
  LastOccurrence Desc,
  tblAssets.AssetName

[jacob_bks]

I think there's a requirement to retain event logs for X days... like, I have last 7 days, so mine won't show any results past 7 days...

[CarolOstos]

Thank you very much!!!
Much appreciated

Now, This report has no results!

[jperry]

Expressions in the ORDER BY list cannot contain aggregate functions.

That would be it complaining about the the count aggregate functions in line 60 & 61 in order by.

Change

Order By Count(tblNtlog.TimeGenerated) Desc,
LastOccurrence Desc,

to

Order By 

[CarolOstos]

Thanks for the quick response, I tried changing the line with the one provided and now I get

Expressions in the ORDER BY list cannot contain aggregate functions.

Database back end is SQL Compact

[jperry]

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

I'm pretty sure the line you're having problems with is the 5th line under select:

 Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,

Try replacing that line with just

tblADComputers.OU,

Let us know if that works for you.
What is your back end database (if you know) we're using M$ SQL Express.

[CarolOstos]

Hey guys, I have created the report but I am getting the following error upon running it

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

Any ideas?

Cheers
Carol Ostos

[Esben_D]

Thanks for sharing!