cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
paultech
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

2 REPLIES 2
paultech
Engaged Sweeper
Thank you RC62N!

That's just what I needed - much appreciated.
RCorbeil
Honored Sweeper II
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