Report: McAfee - AVEEngine +30 days

[javier]

Hi friends!

I have a custom report that show the reg key AVEngine (last time MCAfee was updated).

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As Equipo,
tblADusers.Name As Usuario,
tblRegistry.Regkey As [Clave de registro x32 & x64],
tblRegistry.Value As [Antivirus fecha],
tblAssets.Lasttried
tblAssets.Lasttriggered
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssets.AssetName Not Like 'SR%' And tblRegistry.Regkey Like '%AVEngine'
And tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By [Antivirus fecha]

tblAssets.AssetName Not Like 'SR%' dont show server.


I would like to show only computers which have AVEngine > 30 days.

The format for that key is: 2010/02/02


Could somebody help me?


Thank you.

[javier]

Thank RC62N for de help.

Finally

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As Equipo,
tblADusers.Name As Usuario,
tblRegistry.Regkey As [Clave de registro x32 & x64],
tblRegistry.Value As [Antivirus fecha],
tblAssets.Lasttried As [Inventario a 1],
tblAssets.Lasttriggered As [Inventario a 2]
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssets.AssetName Not Like 'SR%' And tblRegistry.Regkey Like '%AVEngine'
And tblAssets.Assettype = -1 And tblAssetCustom.State = 1 And
DateDiff(d, Convert(DateTime,tblRegistry.Value,101), GetDate()) > 30
Order By [Antivirus fecha]

tblRegistry.Regkey Like '%AVEngine' : The table have a mix of datas (example internet explorer version). We only need the AVENgine-MCAfee date.

[RCorbeil · Accepted solution]

As long as the last-scanned field is consistently formatted, you should be able to convert it to a date type, then do a DateDiff to filter based on age.

Technet: CAST and CONVERT
http://msdn.microsoft.com/en-us/library/ms187928.aspx

CONVERT(DateTime, field_containing_date_as_text, 101)

(I prefer to use ISO8601, but I'll assume most readers of this forum prefer American format and go with 101.)

Technet: DATEDIFF
http://technet.microsoft.com/en-us/library/ms189794.aspx

DateDiff( d,
          CONVERT(DateTime, field_containing_date_as_text, 101),
          GetDate() )

should give you the number of days between the last-scan-date and today, so

DateDiff( d,
          CONVERT(DateTime, field_containing_date_as_text, 101),
          GetDate() ) > 30

should filter for only those dates that are more than 30 days ago.