→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
s_subramanian
Engaged Sweeper III
Hi,

I have scanned the Registry for the AV definition date and trying to pull out a report with the dates. But, when i run the below report it gives me one or two machines only. There's small thing which i'm missing in this query. Any help on resolving this will be really helpful.

Select Top 1000000 tblAssets.AssetID,
tsysIPLocations.IPLocation As [IP location],
tblAssets.AssetName,
tblAssets.Username As [AD.GPID],
tblADusers.Name As [AD.Username],
tblRegistry.Value As [AV Def Date],
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Left Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'LatestVirusDefsDate' And
tblAntivirus.DisplayName = 'Symantec Endpoint Protection' And
tblAssetCustom.State = 1 And tblAntivirus.productUpToDate = 0
Order By [IP location]

The below query without the Registry Value for Definition date gives me 468 rows while the above query gives me only 2 rows. I just want to add the definition date in the below report

Select Top 1000000 tblAssets.AssetID,
tsysIPLocations.IPLocation As [IP location],
tblAssets.AssetName,
tblAssets.Username As [AD.GPID],
tblADusers.Name As [AD.Username],
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAntivirus.DisplayName = 'Symantec Endpoint Protection' And
tblAssetCustom.State = 1 And tblAntivirus.productUpToDate = 0
Order By [IP location]

Regards
Subramanian S
2 REPLIES 2
s_subramanian
Engaged Sweeper III
No. As I said I'm getting 400 odd machines while I click the default report link provided near the Custom Scanning of the registry.
MikeMc
Champion Sweeper II
Is it possible your other machines have not scanned that registry key yet?