List Servers Last Patched As a total Count

[mmeetze]

I am trying to get the Total Count only on the number of machines last patched in 20**, 20**, 20** and so on. I have this report which pulls back our last patched Server 2003 Machines however I cannot get the report to spit out just totals and not individual machines:


Code:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
(Select Top 1 YEAR(tblQuickFixEngineering.InstalledOn) From tblQuickFixEngineering
Where tblQuickFixEngineering.AssetID = tblAssets.AssetID And GetDate() > Case
When CharIndex('/', tblQuickFixEngineering.InstalledOn) > 0 Then
Convert(datetime,tblQuickFixEngineering.InstalledOn)
End Order By tblQuickFixEngineering.InstalledOn Desc) As LastInstalledon
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 tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysOS.OSname Like '%2003%' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole > 1
Order By LastInstalledon,
tblAssets.AssetName



Please Help!

[mmeetze]

Thanks, so since we just stood up Lansweeper at the beginning of the year we can use the example you showed going forward. Then use our original queries to get everything patched up, then let Lansweeper handle it from there using the lastchanged field to create the report. Originally we realized that using the InstalledOn field was the most accurate of a true last patched date and we did have to do a little conversion as you can see from the original post to get that to output a date value we were looking for but we wanted a High Level Total Count report for each year so that we could create a chart for management.

Thanks Again!

[Susan_A]

We advise against using the InstalledOn field, because it is a text field that doesn't always contain a date-like value and isn't always filled in. The Lastchanged field is less accurate in the sense that it indicates when the patch was scanned by Lansweeper, not necessarily when it was installed. If your machines are scanned on a regular basis, it should be fairly accurate going forward though.

[mmeetze]

When we run this sample report it is pulling back data that shows "X" number of machines were patched in 2015, and "X" number of machines were patched in 2016. We know for a fact this is not correct data and why we opted to use the lastinstalledon value because it was returning more accurate data. However the output listing the data in the report is exactly how we wanted to see it.

[Jeremy_D · Accepted solution]

If you are trying to list the last patches for a specific Windows machine then you are better off using the Lastchanged field from tblQuickFixEngineering instead of InstalledOn. The InstalledOn field is a text field. It doesn't always contain a date and isn't filled in for every update either.

We included a sample report below that takes the last scanned update of each Server 2003 machine and then counts the number of machines that were last updated in specific years. You can add this report to your Lansweeper installation by following the instructions in this forum topic.

Select Top 1000000 DatePart(yy, SubQuery.Max) As Year,
  Count(SubQuery.AssetID) As Count
From (Select Top 1000000 tblQuickFixEngineering.AssetID,
    Max(tblQuickFixEngineering.Lastchanged) As Max
  From tblQuickFixEngineering
  Group By tblQuickFixEngineering.AssetID) SubQuery
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = SubQuery.AssetID
  Inner Join tblAssets On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1 And tsysOS.OSname Like '%2003%'
Group By DatePart(yy, SubQuery.Max)
Order By Year

[mmeetze]

To clarify, this report returns results that are listed by machine and the last date they were patched assorted in ascending order. What I am looking for is a total count similar to the one I use for all versions of IE. i need it to output, "X" number of machines were last patched in 2007, "X" number of machines were last patched in 2008...and so on. I cannot seem to get the output of my query to work without giving an error about a grouping issue.