Antivius Pattern Date Older than x Days

[spencerwaller]

HI, I have the below report which pulls out the AV Pattern date for my servers nicely. How can I amend the report to just show machines where pattern date is older than x days ?. Hope you can help



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblRegistry.Valuename,
tblRegistry.Value,
Convert(datetime,tblRegistry.Value,104) As PatternDate,
tblOperatingsystem.Caption,
tblAssets.Domain
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.Lastseen > DateAdd(Day, -14, GetDate()) And
tblRegistry.Valuename = 'PatternDate' And tblOperatingsystem.Caption
Like '%server%' And tblAssetCustom.State = 1

[Esben_D]

Wouldn't the same function work that you used earlier in the query but on the registry value?

I haven't tried this but see if this works:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblRegistry.Valuename,
  tblRegistry.Value,
  Convert(datetime,tblRegistry.Value,104) As PatternDate,
  tblOperatingsystem.Caption,
  tblAssets.Domain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.Lastseen > DateAdd(Day, -14, GetDate()) And
  tblRegistry.Valuename = 'PatternDate' And
  Convert(datetime,tblRegistry.Value,104) > DateAdd(Day, -14, GetDate()) And
  tblOperatingsystem.Caption Like '%server%' And tblAssetCustom.State = 1