Prior to running the report follow the instructions found in
this blog post.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When CCleanerInfo.ValuenameFound = 'No key found' And
CCleanerInfo.[32-bit CCleaner Exe] = 'Not found' Then 'Not Infected'
When CCleanerInfo.ValuenameFound = 'No key found' And
CCleanerInfo.[CCleaner Version] Not Like '5.33%' Then 'Not Infected'
When CCleanerInfo.ValuenameFound = 'MUID value found' And
CCleanerInfo.[CCleaner Version] Not Like '5.33%' Then
'Infected registry key found'
When CCleanerInfo.ValuenameFound = 'No key found' And
CCleanerInfo.[CCleaner Version] Like '5.33%' Then
'Infected CCleaner found but no registry key'
When CCleanerInfo.ValuenameFound = 'MUID value found' And
CCleanerInfo.[CCleaner Version] Like '5.33%' Then
'Infected CCleaner and registry found' End As Status,
CCleanerInfo.Regkey,
CCleanerInfo.Valuename,
CCleanerInfo.ValuenameFound,
CCleanerInfo.[32-bit CCleaner Exe],
CCleanerInfo.[CCleaner Version],
CCleanerInfo.[CCleaner path]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When CCleanerReg.Valuename Is Not Null And CCleanerReg.Valuename <>
'' Then 'MUID value found' Else 'No key found' End As ValuenameFound,
CCleanerReg.Regkey,
CCleanerReg.Valuename,
Case When CCleanerFile.Found = 1 Then 'Found' Else 'Not Found'
End As [32-bit CCleaner Exe],
Replace(Replace(CCleanerFile.FileVersion, ' ', ''), ',', '.') As
[CCleaner Version],
CCleanerFile.PatchSearched As [CCleaner path]
From tblAssets
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\Piriform\Agomo' And
tblRegistry.Valuename = 'MUID') CCleanerReg On CCleanerReg.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblFileVersions.AssetID,
tblFileVersions.FilePathfull As PatchSearched,
tblFileVersions.Found,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged,
tblFileVersions.CreationDate,
tblFileVersions.LastAccessed,
tblFileVersions.LastModified
From tblFileVersions
Where tblFileVersions.FilePathfull Like '%CCleaner.exe') As CCleanerFile
On tblAssets.AssetID = CCleanerFile.AssetID) As CCleanerInfo
On tblAssets.AssetID = CCleanerInfo.AssetID
Where tblComputersystem.SystemType Not Like 'x64%'
Order By tblAssets.Domain,
tblAssets.AssetName
