Video Screencast Help

Need assistance with query

Created: 15 May 2014 | 8 comments

Hi,

I am checking for an update on both XP and Windows 7 systems. From I gather updates for XP are in one table and the ones for W7 are in another table. I got this simple query that brings me back numbers for Windows 7. How I can update this to include XP?

Help is appreciated.

SELECT
DISTINCT T1.[Name], T2.[User], T0.[Guid], T3.[Patch ID] AS [Product Name]
FROM (
(
vComputer T0
INNER JOIN [Inv_AeX_AC_Identification] T1 ON T0.[Guid] = T1.[_ResourceGuid]
)
INNER JOIN [Inv_AeX_AC_Primary_User] T2 ON T1.[_ResourceGuid] = T2.[_ResourceGuid]
)
INNER JOIN Inv_SW_Patch_Windows T3 ON T2.[_ResourceGuid] = T3.[_ResourceGuid]
WHERE T3.[Patch ID] LIKE '%2964358'

Operating Systems:

Comments 8 CommentsJump to latest comment

Gavin Sanish's picture

Hi  Brain,

    As my understanding you want check both the os names in this query  right ?? i have added os name in this column you can check running this query. If you need more help please let me know

SELECT DISTINCT T1.Name, T2.[User], T0.Guid, T3.[Patch ID] AS [Product Name], T0.[OS Name]
FROM         dbo.vComputer AS T0 INNER JOIN
                      dbo.Inv_AeX_AC_Identification AS T1 ON T0.Guid = T1._ResourceGuid INNER JOIN
                      dbo.Inv_AeX_AC_Primary_User AS T2 ON T1._ResourceGuid = T2._ResourceGuid INNER JOIN
                      dbo.Inv_SW_Patch_Windows AS T3 ON T2._ResourceGuid = T3._ResourceGuid
WHERE     (T3.[Patch ID] LIKE '%2964358')

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
Briandr88's picture

Gavin,

The data for XP and W7 are in two different tables.

prince_y's picture

Hi Brain,

Could you please try with this one

SELECT Inv_AeX_AC_Identification.Name, Inv_SW_Patch_Windows.[Patch ID] AS [Product Name], Inv_SW_Patch_Windows.Description, Inv_AeX_AC_Identification.[OS Name],
Inv_AeX_AC_Identification.[OS Type]
FROM Inv_AeX_AC_Identification CROSS JOIN
Inv_SW_Patch_Windows
Inv_SW_Patch_Windows
where [Patch ID]like '%2964358%'

Gavin Sanish's picture

Hi Brain,  

 I cant understand which tables contains the xp and windows 7. I think both the values are in same table.

@Pince_Y the table is not joined properly so you will getting the duplicated values in it.

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
prince_y's picture

Hi Brain,

  I belive this is the table which contaisn all the details regarding patches irerespective of the O.S

Inv_SW_Patch_Windows
 

prince_y's picture

Gavin,

  I checked the quey and it was working  fine in mine

Gavin Sanish's picture

Hi Prince,

Yeah you are right Inv_SW_Patch_Windows contains the patches in it. but you have linked both the tables

(Inv_AeX_AC_Identification and  Inv_SW_Patch_Windows) If ac_indentification contains more values than

Inv_SW_Patch_Windows   it will get duplicated. So in such seceniro it wont work.

--

 

...

G. Gavin Sanish

T : +91 9884877206
&a
Tomasz Wozniak's picture

Brian,

I understand you are looking for computers where kb2964358 is installed regardless of operating system. According to http://support.microsoft.com/kb/2964358 it is MS14-021 bulletin.

I assume you have had Patch Management set up. In such case I would usethe out-of-box report 'Windows Compliance by bulletin'.  

Select MS14-021 bulletin in the report and from the context menu select 'View installed computers'. Then you can save the list to Excel. This still works even if you did not deploy the bulletin to the computers yet.

If you really want to see the sql behind the 'Installed report' 'you can use 'View XML' for the drilled down report. But I would not bother doing it manually if you can have it with 2 clicks.

Let us know how it worked for you.

Tomasz