Hotfix Report for XP machines or Windows 7 machines only

[harringg]

I have this report which shows me any machine that is missing the patch for XP KB2621440. I have isolated the ones that need updates applied.

Select Top (1000000) tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, Web40OSName.OSname, TsysLastscan.Lasttime As Lastscanned, Web40OSName.Compimage As icon From tblComputers Inner Join TsysLastscan On tblComputers.Computername = TsysLastscan.Computername Left Outer Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Where tblComputers.Computername Not In (Select tblQuickFixEngineering.Computername From tblQuickFixEngineering Where tblQuickFixEngineering.HotFixID = 'KB2621440') And TsysLastscan.CFGcode = 49 And Cast(Left(tblOperatingsystem.Version, 3) As Decimal(3,1)) < 6.1 Order By tblComputers.Computer

There is a KB for Windows 7 machines that is KB2667402.

When I simply change:
tblQuickFixEngineering.HotFixID = 'KB2621440')
to
tblQuickFixEngineering.HotFixID = 'KB2667402')

I get a bunch of XP machines. I'm thinking it has to do with something in this region of the query:

TsysLastscan.CFGcode = 49 And Cast(Left(tblOperatingsystem.Version, 3) As Decimal(3,1)) < 6.1

Is there a value to distinguish Windows 7 and greater?

The KBs I'm searching for come from this link:

http://technet.microsoft.com/en-us/security/bulletin/ms12-020

[harringg]

Worked great! I'm heading out to patch those remaining machines now.

Thanks again.

[harringg]

This code:

Select Top (1000000) tblComputers.Computername, tblComputers.ComputerUnique, tblComputers.Domain, Web40OSName.OSname, TsysLastscan.Lasttime As Lastscanned, Web40OSName.Compimage As icon From tblComputers Inner Join TsysLastscan On tblComputers.Computername = TsysLastscan.Computername Left Outer Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Where tblComputers.Computername Not In (Select tblQuickFixEngineering.Computername From tblQuickFixEngineering Where tblQuickFixEngineering.HotFixID = 'KB2621440') And TsysLastscan.CFGcode = 49 And Cast(Left(tblOperatingsystem.Version, 3) As Decimal(3,1)) < 6.1 Order By tblComputers.Computer

Results in these results:

Computer	Domain	OSname	Lastscanned
		Win XP	2012-3-27 12:40
		Win XP	2012-3-27 09:11
		Win 2008	2012-3-27 09:12
		Win XP	2012-3-27 12:41
		Win XP	2012-3-27 12:43
		Win XP	2012-3-30 16:22
		Win 2008	2012-3-27 09:04
		Win 2003	2012-3-27 12:41
		Win 2003	2012-3-27 12:41
		Win XP	2012-3-28 16:28
		Win XP	2012-3-27 09:14
		Win XP	2012-3-27 09:45

And I've manually gone to those PCs and confirmed that the Hotfix has not been installed.

I'm looking run the same report for Windows 7 and Server 2008 R2 machines but looking for missing KB2667402.

Thanks

[Hemoco · Accepted solution]

harringg wrote:
I'm looking run the same report for Windows 7 and Server 2008 R2 machines but looking for missing KB2667402.

Could you try the report below.

Select Top (1000000) tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblComputers.Firstseen, tblComputers.Lastseen,
Web40OSName.OSname, Web40OSName.Compimage As icon
From tblComputers Left Outer Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername
Where
tblComputers.Computername Not In (Select tblQuickFixEngineering.Computername
From tblQuickFixEngineering
Where tblQuickFixEngineering.HotFixID = 'KB2667402') And
tblOperatingsystem.Version Like '6.1%'
Order By tblComputers.ComputerUnique

[Hemoco]

An OS version number overview can be found here. The report you are using does not actually include Windows 7 machines.
Could you clarify exactly which operating systems you would like to report on from the list linked?