→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jperry
Engaged Sweeper III
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

7 REPLIES 7
jacob_bks
Champion Sweeper
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
Engaged Sweeper
Thank you very much!!!
Much appreciated

Now, This report has no results!
jperry
Engaged Sweeper III
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
Engaged Sweeper
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
Engaged Sweeper III
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
Engaged Sweeper

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
Lansweeper Employee
Lansweeper Employee
Thanks for sharing!