Remove Duplicate entries from report

[paultech]

Hi all,

I have put a report together which tells me all of the machines which have 'Event ID 7023'.

The report has worked and I can see all the machines, but unfortunately it's showing the each PC multiple times in the report (as each machine has multiple instances of the Event ID 7023).

Does anyone know a way which I can reduce all this to only show each PC once?

I'm using the following statement:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID = tblNtlog.SourcenameID
Where tblNtlog.Eventcode = '7023' And tblNtlogSource.Sourcename = 'Service Control Manager' And State = 1

[paultech]

Thank you RC62N!

That's just what I needed - much appreciated.

[RCorbeil · Accepted solution]

Something like this?

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Event7023.LatestTimeGenerated
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN (SELECT
                tblNTLog.AssetID,
                Max(tblNTLog.TimeGenerated) AS LatestTimeGenerated
              FROM
                tblNTLog
                INNER JOIN tblNtlogSource ON tblNtlogSource.SourcenameID = tblNtlog.SourcenameID
              WHERE
                tblNtlog.Eventcode = '7023'
                AND tblNtlogSource.Sourcename = 'Service Control Manager'
              GROUP BY
                tblNTLog.AssetID) AS Event7023 ON Event7023.AssetID = tblAssets.AssetID
WHERE
  State = 1