→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎11-06-2017 07:32 PM
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
‎12-05-2017 05:55 PM
‎11-23-2017 03:30 AM
‎11-23-2017 02:38 AM
Expressions in the ORDER BY list cannot contain aggregate functions.
Order By Count(tblNtlog.TimeGenerated) Desc,
LastOccurrence Desc,
Order By
‎11-23-2017 02:31 AM
‎11-23-2017 02:28 AM
Error: There was an error parsing the query. [ Token line number = 1,Token line offset = 137,Token in error = Left ]
Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,
tblADComputers.OU,
‎11-23-2017 02:19 AM - last edited on ‎10-27-2022 01:01 PM by Mercedes_O
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
‎11-07-2017 09:31 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now