Client Management Suite

Hi,

Could someone tell me what JOIN I am missing or if it is the wrong one or where I need a (

This query needs to work and I just am not good at tying all these things together.

SELECT    

vComputer.Name AS 'Computer Name'

,vComputer.[GUID], vComputer.[IP Address]

, vComputer.[OS Name]

, vComputer.[OS Revision]

, vHWComputerSystem.Manufacturer

,  vHWComputerSystem.Model

, vHWComputerSystem.[Identifying Number] AS 'Serial Number'

, vHWProcessor.Model AS 'Processor'

, vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'

, I.Name AS 'Location', vComputer.[User] AS 'Primary Owner'

, vComputer.IsManaged AS 'Managed'

FROM         vComputer LEFT OUTER JOIN
                      ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR
                      loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN
                      vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER JOIN
                      vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid LEFT OUTER JOIN
                      vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid and
                     

INNER JOIN [Inv_InstalledSoftware] AS [InstalledSoftware]
                      ON [vComputer].[Guid] = [InstalledSoftware].[_ResourceGuid]
WHERE         
                      (
                        (
                          (IsManaged=1) and _ResourceGuid IN (SELECT _ResourceGuid FROM Inv_InstalledSoftware WHERE InstallFlag = 1 and
                     (_SoftwareComponentGuid = '9a388a78-87d9-48b6-a004-8e54536aa6a4')))
                                    )

To help this cause here is the query I am going to include the query I am trying to incorporate

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT
                                    [vri1_Computer].[Guid] AS [_ResourceGuid]
                                 FROM
                                    [vRM_Computer_Item] AS [vri1_Computer]
                                    INNER JOIN [Inv_InstalledSoftware] AS [dca2_InstalledSoftware]
                                       ON [vri1_Computer].[Guid] = [dca2_InstalledSoftware].[_ResourceGuid]
                                    INNER JOIN [vComputerResource] AS [ajs3_vComputerResource]
                                       ON [vri1_Computer].[Guid] = [ajs3_vComputerResource].[Guid]
                                 WHERE
                                    (
                                       (
                                        ([ajs3_vComputerResource].[IsManaged] = 1) AND _ResourceGuid IN (SELECT _ResourceGuid FROM Inv_InstalledSoftware WHERE InstallFlag = 1 AND (_SoftwareComponentGuid = '9a388a78-87d9-48b6-a004-8e54536aa6a4')))
                                    )
 

Help is greatly appreciated.

Thanks.

Hi Brian,

I think this is what you are looking for.

SELECT    vc.Name AS 'Computer Name'
        , vc.[GUID]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] AS 'Serial Number'
        , p.[Model] AS 'Processor'
        , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
        , vi.[Name] AS 'Location'
        , vc.[User] AS 'Primary Owner'
        , vc.IsManaged AS 'Managed'
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid
INNER JOIN [Inv_InstalledSoftware] sw
    ON vc.[Guid] = sw.[_ResourceGuid] AND sw.[_SoftwareComponentGuid]= '9a388a78-87d9-48b6-a004-8e54536aa6a4' AND sw.InstallFlag = 1
WHERE vc.IsManaged = 1

John

Hi John,

Cool and thanks. I should have throught of something after the fact this application has two different Guid's to represent the two different versions. What I am after is find computers that don't have these two versions. Obviously I can can tweak to AND sw.InstallFlag = 0

Can your query above that be tweaked so we can actually refer to the apps by their names? I am pretty sure it could. I have used something like this before

vc.Guid NOT IN (SELECT arp._ResourceGuid FROM Inv_AddRemoveProgram arp WHERE DisplayName LIKE 'Microsoft Office Outlook 2007%')

Thanks

Hi John and other folks,

The query works, but it does take about 25 seconds to complete. If I change the sw.InstallFlag = 1 to 0 to see which systems don't have this application I only get 3. By our estimates there should be between 400-500 without it. With the query as is we get 4284 computers with it, which seems to be right.

Not sure if adding this line will help.

vc.Guid NOT IN (SELECT arp._ResourceGuid FROM Inv_AddRemoveProgram arp WHERE DisplayName LIKE 'Microsoft Office Outlook 2007%' and DisplayVersion  like 'xxxx')

You want a report which shows all computers that do not have at least one of two versions of a particular software installed on them?

How about this:

WITH Installations (Guid) AS
(SELECT DISTINCT sw.[_ResourceGuid]
from [vSoftwareComponent] sc
INNER JOIN [Inv_InstalledSoftware] sw
on sc.[Guid] = sw.[_SoftwareComponentGuid]
where sc.[Name] in (
  'Security Update for Windows XP (KB950762)'
 ,'SoftwareManagementSolution 7.1.7858.0 English x64'
   )
   AND sw.InstallFlag = 1
)
SELECT    vc.[Name] [Computer Name]
        , vc.[Guid] [ItemGuid]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] [Serial Number]
        , p.[Model] [Processor]
        , cs.[Total Physical Memory (Bytes)] / 1048576 [RAM (MB)]
        , vi.[Name] [Location]
        , vc.[User] [Primary Owner]
        , vc.IsManaged [Managed]
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid
LEFT OUTER JOIN Installations i
    ON i.Guid = vc.Guid
WHERE i.[Guid] IS NULL
  AND vc.[IsManaged] = 1  

John

Edited because I forgot the InstallFlag=1 condition.

Hi John,

Yes, I want to check computers that don't have either version of a particular application.

So I change this line to the software I am checking for?

'Security Update for Windows XP (KB950762)'
 ,'SoftwareManagementSolution 7.1.7858.0 English x64'

W7 Add/Remove Programs doesn't list the version in the name. The version is listed besides it in the 'Version' column. So how do I change that line. The other thing I need to account for is the app is installed on both x86 and x64 systems.

Getting closer, I think.

Thanks.

Most computers that do not have the software installed will not have entries in the Inv_InstalledSoftware table for that component. I am not sure but InstallFlag=0 probably means that it was installed, but has been removed.

The length of time it takes to run may be due to the reference to vHWProcessor. Try using Inv_HW_Processor_Windows_Name instead:

WITH Installations (Guid) AS
(SELECT DISTINCT sw.[_ResourceGuid]
from [vSoftwareComponent] sc
INNER JOIN [Inv_InstalledSoftware] sw
on sc.[Guid] = sw.[_SoftwareComponentGuid]
where sc.[Name] in (
  'Security Update for Windows XP (KB950762)'
 ,'SoftwareManagementSolution 7.1.7858.0 English x64'
   )
   AND sw.InstallFlag = 1
)
SELECT    vc.[Name] [Computer Name]
        , vc.[Guid] [ItemGuid]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] [Serial Number]
        , p.[Processor Name] [Processor]
        , cs.[Total Physical Memory (Bytes)] / 1048576 [RAM (MB)]
        , vi.[Name] [Location]
        , vc.[User] [Primary Owner]
        , vc.IsManaged [Managed]
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN Inv_HW_Processor_Name_Windows p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid
LEFT OUTER JOIN Installations i
    ON i.Guid = vc.Guid
WHERE i.[Guid] IS NULL
  AND vc.[IsManaged] = 1

John

Here is the query using the AddRemove view in place of the SoftwareComponent view:

WITH Installations (Guid) AS
(SELECT DISTINCT ar.[_ResourceGuid]
from RMV_AddRemove ar
where
    (ar.[Product Name]='SQL Server 2008 R2 Management Studio' AND ar.[Version]='10.50.1600.1')
    OR
    (ar.[Product Name]='Microsoft Silverlight' AND ar.[Version]='5.1.10411.0')
)
SELECT    vc.[Name] [Computer Name]
        , vc.[Guid] [ItemGuid]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] [Serial Number]
        , p.[Processor Name] [Processor]
        , cs.[Total Physical Memory (Bytes)] / 1048576 [RAM (MB)]
        , vi.[Name] [Location]
        , vc.[User] [Primary Owner]
        , vc.IsManaged [Managed]
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN Inv_HW_Processor_Name_Windows p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid
LEFT OUTER JOIN Installations i
    ON i.Guid = vc.Guid
WHERE i.[Guid] IS NULL
  AND vc.[IsManaged] = 1 
  

John

Edit: The reference to the Inv_InstalledSoftware table was redundant. I removed it.

Hi John,

Very cool and thanks. Just want to take this one step further and create a report on retired systems.

SELECT    vc.Name AS 'Computer Name'
        , vc.[GUID]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] AS 'Serial Number'
        , p.[Model] AS 'Processor'
        , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
        , vi.[Name] AS 'Location'
        , vc.[User] AS 'Primary Owner'
        , vc.IsManaged AS 'Managed'
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid

INNER JOIN ResourceAssociation ra

   ON ra.ParentResourceGuid = ci.Guid
   AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'

WHERE vc.IsManaged = 1

I think the only other thing I would need to add is dates indicating Begin and End Date.......

Hi John and other folks,

Thanks for the help thus far. I tried piecing together one of your queries so I can get some information on retired systems. The code somewhat works. Adding in ModifiedDate returns and invalid column name, but if I run this without that field I get all null values for CreatedDate. Not sure what JOIN may be incorrect.

SELECT    vc.Name AS 'Computer Name'
        , vc.[GUID]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] AS 'Serial Number'
        , p.[Model] AS 'Processor'
        , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
        , ra.[CreatedDate] AS 'Deployed Date'
        , ra.[ModifiedDate] AS 'Archived Date'       
FROM vComputer  vc
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid

LEFT OUTER JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = vc.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'

WHERE vc.IsManaged = 1

Here is the code to check for retired systems that someone was nice enough to provide me.

-------------------------------------------------------------------------------------------------------------------------------------------

SELECT ci.*

FROM vRM_Computer_Item ci

JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = ci.Guid
   AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'  --Retired
   AND IsManaged=1

Thanks.

Hi Brian,

If you only want information from retired computers then you should be doing an INNER JOIN on the ResourceAssociation table for resources associated with the retired asset status. The CreatedDate in the ResourceAssociation table should be the date that the computer was marked as retired. The reason you are getting null values in your output is because you are using a LEFT OUTER JOIN and so you are getting results from computers which do not have a corresponding entry in this table.

You can use the CreatedDate from the vItem table as an indication of when the system was first recorded in Altiris.

Note that the IsManaged = 1 is unnecessary in this query and could give misleading results.

Your query should read:

SELECT    vc.Name AS 'Computer Name'
        , vc.[GUID]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] AS 'Serial Number'
        , p.[Model] AS 'Processor'
        , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
        , ra.[CreatedDate] AS 'Deployed Date'
        , ra.[CreatedDate] AS 'Archived Date'       
FROM vComputer  vc
INNER JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = vc.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid

John

Hi John,

I'd like to have a hardware/software summary as part of this report. That is why I included fields like OS Name, OS Revision, Manfuacturer, Mode, Serial Number from their respective tables. Nothing more at this point, except the date the system was created and retired within Altiris.

When the query you provided is run I get 0 records returned. Also, why is the two lines below pulling from the same field? Typo? I thought for the second one to get the retired or archived date I woul want to use ModifiedDate.

 , ra.[CreatedDate] AS 'Deployed Date'
 , ra.[CreatedDate] AS 'Archived Date'  

Thanks.

I should have tested it. The vComputer view only contains 'Active' assets so trying to report on resources with any other status based on that view is bound to return no values. Apologies for that. There is a vCMDBComputer view which contains a lot of the same information but I do not know which solutions you have installed and whether that view is available, so I have not used it.

The repeated ra.[CreatedDate] was indeed a typo. The deployed date would have been vc.[CreatedDate]. However, for the reason stated above, this still would not have worked.

This should give results:

SELECT    COALESCE(os.[Name],ci.[Name]) [Computer Name]
        , c.[Guid]
        , tcp.[IP Address]
        , vi.[Name] [Location]
        , os.[OS Name]
        , os.[OS Revision]
        , cs.[Manufacturer]
        , cs.[Model]
        , cs.[Identifying Number] [Serial Number]
        , p.[Model] [Processor]
        , cs.[Total Physical Memory (Bytes)] / 1048576 [RAM (MB)]
        , ci.[CreatedDate] [Deployed Date]
        , ra.[CreatedDate] [Archived Date]       
FROM [vRM_Computer] c
INNER JOIN [vRM_Computer_Item] ci
    ON c.[Guid] = ci.[Guid]
INNER JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = c.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON loc.ParentResourceGuid = c.Guid AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON c.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON c.Guid = cs._ResourceGuid
LEFT JOIN    [Inv_AeX_AC_Identification] os
    ON os.[_ResourceGuid] = ci.[Guid]
LEFT JOIN    [Inv_AeX_AC_TCPIP] tcp
    ON tcp.[_ResourceGuid] = ci.[Guid]
                    AND tcp.[_id] =
                        (
                            SELECT TOP 1 t.[_id]
                            FROM [Inv_AeX_AC_TCPIP] t
                            WHERE ci.[Guid]=t.[_ResourceGuid] AND t.[IP Address] NOT IN ('127.0.0.1', '0.0.0.0', '') AND NOT t.[IP Address] IS NULL
                            ORDER BY t.Routable DESC, t.DHCPEnabled DESC
                        )
where
    c.Deleted = 0

John

Hi Brian,

Did the last query work for you? If so, you should mark it as the solution.

Thanks,

John

Hi John,

I think I am all set, but I will be back no doubt with more requests for help with my queries. Thanks again.