Help with Group Policy Report

[sam1]

Hello Lansweeper Community

I tried to create a Report, which outputs the windows clients where the last successful GPO update is two days apart from the last successful lansweeper scan.

My problem is that the windows assets are listed multiple times because of the multiple event log entries.

How could i group the event log entries so that only the newest event log entry is listed for the client.

Thank you in advance for your help / ideas. 🙂

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End As EventType,
tblNtlog.TimeGenerated,
tblNtlogSource.Sourcename,
tblNtlogFile.Logfile,
tblNtlogUser.Loguser,
tblNtlogMessage.Message
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNtlog On tblNtlog.AssetID = tblAssets.AssetID
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 tblNtlogFile On tblNtlogFile.LogfileID = tblNtlog.LogfileID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblNtlog.Eventcode Like '%1500%' Or tblNtlog.Eventcode Like '%1501%' Or
tblNtlog.Eventcode Like '%1502%' Or tblNtlog.Eventcode Like '%1503%') And
Case tblNtlog.Eventtype
When 1 Then 'Error'
When 2 Then 'Warning'
When 3 Then 'Information'
When 4 Then 'Security Audit Success'
When 5 Then 'Security Audit Failure'
End = 'Information' And tblNtlog.TimeGenerated > GetDate() - 30 And
tblNtlogSource.Sourcename Like '%Group%Policy%' And tblState.Statename =
'Active' And (tblNtlog.TimeGenerated + 2) <= tblAssets.Lastseen
Order By tblNtlog.TimeGenerated

[Mister_Nobody]

You can use MAX function

https://learn.microsoft.com/ru-ru/sql/t-sql/functions/max-transact-sql?view=sql-server-ver16

[sam1]

Hey Mister_Nobody,

thanks for that idea.

i tried that, but it still shows older entries, when removing "(tblNtlog.TimeGenerated + 2) <= tblAssets.Lastseen" it only shows the newest entry. 

I need it to filter out the older entries and then apply the SQL Where filter, is that possbile?