Creating two WSUS/Installed Updates Reports with date issues

[dpelletier]

Hello,

I am creating two reports that show the last time an update was installed. Both are failing and i could really use help.

First issue:

The date value i am returning from the reg scan of the last installs date from "SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\Results\Install" is 2015-10-19 21:57:40. Which looks like a date but i cant apply any date filters such as > GetDate() - 60 or MAX or any type of meaningfully sorting.

The two errors i get are:

error while getting report
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

error while getting report
Conversion failed when converting date and/or time from character string.

I tried using substring to make the field look like 10/19/2015 but sorting with > Getdate's still fail.

Sample of one of my report attempts is below, also i have tried "convert(datetime, Tablename, 101)" and "Cast(tablename as datetime)" and then i just get a cant covert to datetime error.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Convert(datetime,(SubString(tblRegistry.Value, 1,
10))) As [Last Install Date],
Convert(datetime,(SubString(tblRegistry1.Value, 1,
10))) As [Last Download Date]
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblRegistry tblRegistry1 On tblAssets.AssetID =
tblRegistry1.AssetID
Where Convert(datetime,(SubString(tblRegistry.Value, 1, 10))) > GetDate() - 60
And tblRegistry.Valuename Like '%LastS%' And tblRegistry.Regkey Like
'%Install' And tblRegistry1.Regkey Like '%Download%' And tblAssets.Assettype =
-1

Second Issue:

Similar to the first one i have the below report when i run it without grouping every computer shows every hotfix thats installed. I just want to show the latest installed hotfix. While i can group the computer into one entry, i have no way of grabbing the latest date installed since the field with the date is not a date field!

Select Top 1000000 tblAssets.AssetID As AssetID1,
tblAssets.AssetName,
tblQuickFixEngineeringUni.HotFixID,
tblQuickFixEngineeringUni.Description,
Max(tblQuickFixEngineering.InstalledOn)
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblQuickFixEngineering On tblAssets.AssetID =
tblQuickFixEngineering.AssetID
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID,
tsysOS
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName
Order By tblAssets.AssetName,
Max(tblQuickFixEngineering.InstalledOn) Desc

Thanks!!!

[Susan_A]

Making manual database changes is not supported and is likely to cause issues. There's no point in changing the field type anyway. You can convert values to datetime within your SQL query itself. If this is generating errors, that means there are values that are not datetime compatible, i.e. that cannot be converted. We know for a fact that the WMI (Windows Management Instrumentation) field we pull tblQuickFixEngineering.InstalledOn from doesn't always return a date. Lansweeper scans the value as it is stored in WMI on the client machine itself.

I would recommend running the report referenced in my previous post. You will however need to ensure that your machines are *successfully* scanned on a regular basis in order for the report results to be accurate. The "last seen" date listed on your computer webpages should be recent. If it isn't, check the computer's Errors tab for scanning errors and refer to this troubleshooting guide to resolve the errors.

[dpelletier]

Thank you for the reply. I tried your report and while it is pulling last scan that does not match last install. I have computers with 1-6+ months between those two dates. I unmarked this as resolved because i don't feel that resolves the issue.

Can i connect to the SQL database and manually set TblRegistry.Value and tblQuickFixEngineering.InstalledOn fields to date fields or will that mess up other parts of the program?

[Susan_A · Accepted solution]

TblRegistry.Value and tblQuickFixEngineering.InstalledOn are text fields, not date fields. You can see the field type listed next to the field name in the report builder. To apply date filters to the aforementioned fields, you would need to convert them to dates first. If you've already tried this and there are errors, there must be data in the fields that cannot be converted to dates, which is quite possible.

I would recommend building a report based on tblQuickFixEngineering.Lastchanged (which is a datetime field) instead, as seen here for instance. This will show you when a patch was last *scanned*, which should fairly accurately reflect when a patch was last *installed*, if your machines are scanned on a regular basis.