Microsoft security advisory 4025685 report help

[dabravokid]

Hi all,

I've been attempting to create a report that checks for assets that are missing a number of patches. This is to help report on the latest Microsoft advisory 4025685 that lists a number of patches for different OS. With the help of some reports already built and some from Lansweeper support I've got something as follows:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB958644', 'KB4025218')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

In the example I've just picked 2 hotfixes for XP and if I run the report on just one hotfix I get 5 returns and then on the other hotfix I get 10 returns, if I then include both as above I get 5 returns. Could anybody point me in the right direction? Thanks in advance.

[stevejohnson]

Hi Tom.P

I've tried to run your second report but I get the following error :

Error: There was an error parsing the query. [ Token line number = 1,Token line offset = 270,Token in error = KB958644 ]

I've removed that entry, but it just errors on the next item instead.

Any ideas?

Cheers

Steve

[Tom_P]

Hi,

The report that was included indeed only returns the assets that don't have any of the mentioned KB's installed. Sorry for the misinterpretation there. We added a second report that allows you to see what KB's (in detail) are missing on your XP machines. If all mentioned KB's are installed, the asset will not be returned by the report. If needed, this can of course be changed by altering the filter settings

Select Top 1000000 tblAssets.AssetID,
    tblAssets.AssetName,
    tsysAssetTypes.AssetTypename,
    tsysAssetTypes.AssetTypeIcon10 As icon,
    tblAssets.IPAddress,
    tblAssets.Lastseen,
    tblAssets.Lasttried,
    tsysOS.OSname,
	CASE When Qfix1.HotFixID is null then 'No' else 'Yes' end as 'KB958644',
	CASE When Qfix2.HotFixID is null then 'No' else 'Yes' end as 'KB2347290',
	CASE When Qfix3.HotFixID is null then 'No' else 'Yes' end as 'KB3197835',
	CASE When Qfix4.HotFixID is null then 'No' else 'Yes' end as 'KB4012583',
	CASE When Qfix5.HotFixID is null then 'No' else 'Yes' end as 'KB4012598',
	CASE When Qfix6.HotFixID is null then 'No' else 'Yes' end as 'KB4018271',
	CASE When Qfix7.HotFixID is null then 'No' else 'Yes' end as 'KB4018466',
	CASE When Qfix8.HotFixID is null then 'No' else 'Yes' end as 'KB4019204',
	CASE When Qfix9.HotFixID is null then 'No' else 'Yes' end as 'KB4022747',
	CASE When Qfix10.HotFixID is null then 'No' else 'Yes' end as 'KB4024323',
	CASE When Qfix11.HotFixID is null then 'No' else 'Yes' end as 'KB4024402',
	CASE When Qfix12.HotFixID is null then 'No' else 'Yes' end as 'KB4025218'
From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB958644')  as Qfix1 on Qfix1.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB2347290') as Qfix2 on Qfix2.AssetID = TblAssets.AssetID 
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB3197835') as Qfix3 on Qfix3.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4012583') as Qfix4 on Qfix4.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4012598') as Qfix5 on Qfix5.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4018271') as Qfix6 on Qfix6.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4018466') as Qfix7 on Qfix7.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4019204') as Qfix8 on Qfix8.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4022747') as Qfix9 on Qfix9.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4024323') as Qfix10 on Qfix10.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4024402') as Qfix11 on Qfix11.AssetID = TblAssets.AssetID
	Left Join (Select tblQuickFixEngineering.AssetID, tblQuickFixEngineeringUni.HotFixID From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID Where tblQuickFixEngineeringUni.HotFixID = 'KB4025218') as Qfix12 on Qfix12.AssetID = TblAssets.AssetID
Where
	tsysOS.OSname = 'Win XP'
    And tblAssetCustom.State = 1
	And (CASE When Qfix1.HotFixID is not null
		And Qfix2.HotFixID is not null
		And Qfix3.HotFixID is not null
		And Qfix4.HotFixID is not null
		And Qfix5.HotFixID is not null
		And Qfix6.HotFixID is not null
		And Qfix7.HotFixID is not null
		And Qfix8.HotFixID is not null
		And Qfix9.HotFixID is not null
		And Qfix10.HotFixID is not null
		And Qfix11.HotFixID is not null
		And Qfix12.HotFixID is not null then 'Up to date' else 'Missing KB' end) = 'Missing KB'

[dabravokid]

Hi, thanks for the reply. That query only returns assets that are missing all the patches. The report I've done does return what I think is the correct set back but it just takes a while to run.

[Tom_P]

To check if one of the mentioned KB's is installed on your XP machines, you can use the following report, which will only return the assets that don't have any of the mentioned KB's installed. Additional KB's can of course still be added to 'In'-clause that is currently being used.

Select Top 1000000 tblAssets.AssetID,
	tblAssets.AssetName,
	tsysAssetTypes.AssetTypename,
	tsysAssetTypes.AssetTypeIcon10 As icon,
	tblAssets.IPAddress,
	tblAssets.Lastseen,
	tblAssets.Lasttried,
	tsysOS.OSname
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
	Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where 
	tblAssets.AssetID Not In (
		Select tblQuickFixEngineering.AssetID 
		From tblQuickFixEngineering 
			Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
		Where tblQuickFixEngineeringUni.HotFixID In ('KB958644', 'KB2347290', 'KB3197835', 'KB4012583', 'KB4012598', 'KB4018271', 'KB4018466', 'KB4019204', 'KB4022747', 'KB4024323', 'KB4024402', 'KB4025218')) 
	And tsysOS.OSname = 'Win XP' 
	And tblAssetCustom.State = 1

[dabravokid]

I've made a little progress with this using the following:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB958644')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB2347290')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB3197835')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4012583')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4012598')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4018271')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4018466')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4019204')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4022747')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4024323')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4024402')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select tblQuickFixEngineering.AssetID
From tblQuickFixEngineering Inner Join tblQuickFixEngineeringUni
On tblQuickFixEngineeringUni.QFEID = tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID In ('KB4025218')) And
tsysOS.OSname = 'Win XP' And tblAssetCustom.State = 1)

Its not the best report, it takes a long time to run but the results tally with running the report as single patches. If anybody has any suggestions on how to optimise I would be most grateful.