Client Management Suite

I'd like to know if I can get a report or some kind of extract from Altiris 7.1 to show us the managed and unmanaged software list which can be seen from the console under Manage  - Software.

We have got the Client Management Suite only on our server so do we need another product to show this information?

Thanks

Shaun

This report will all you to report on all software or filter on Managed software. It will list the Software Name, Company, Version and Install Count based upon Inventory data.

Attachment(s)

Installed Software by Product.zip   3 KB 1 version

This will return exactly what you see in your Newly Discovered list...

SELECT DISTINCT

[vri2_Software Component].[Name] AS [Name]

FROM

[vRM_Software_Component_Item] AS [vri2_Software Component]

INNER JOIN [vAC_InstalledSoftware] AS [ajs3_vAC_InstalledSoftware]

ON ([vri2_Software Component].[Guid] = [ajs3_vAC_InstalledSoftware].[Guid])

LEFT OUTER JOIN ([ResourceAssociation] AS [ajs4_ra]

LEFT OUTER JOIN [vRM_Company_Item] AS [ajs5_vRM_Company_Item]

ON ([ajs4_ra].[ChildResourceGuid] = [ajs5_vRM_Company_Item].[Guid]))

ON ([vri2_Software Component].[Guid] = [ajs4_ra].[ParentResourceGuid])

LEFT OUTER JOIN [ItemPresentation] AS [ajs6_ItemPresentation]

ON ([vri2_Software Component].[Guid] = [ajs6_ItemPresentation].[Guid])

WHERE [ajs3_vAC_InstalledSoftware].[IsManaged] = 0

ORDER BY [Name] ASC

Thanks guys I'll give these a go and let you know how if successful.

The managed software list query doesn't seem to work and is saying the Data source is not in a runnable state, the Newly discovered software list does work however, it was the managed and unmanaged software we we are most interested in though, any ideas?

Thanks again

Does the query work when run through SQL Server Management Studio?

No but I've found out why is was a stray ,

Just ran the report and it works a treat.

Thanks for that.

This query will return exactly what you see in your Managed Software list...

SELECT DISTINCT

[vri2_Software Product].[Name] AS [Name]

FROM

[vRM_Software_Product_Item] AS [vri2_Software Product]

INNER JOIN [Inv_Software_Product_State] AS [dca3_Software Product State]

ON ([vri2_Software Product].[Guid] = [dca3_Software Product State].[_ResourceGuid])

INNER JOIN [vAC_ManagedSoftwareProducts] AS [ajs4_vAC_ManagedSoftwareProducts]

ON ([vri2_Software Product].[Guid] = [ajs4_vAC_ManagedSoftwareProducts].[Guid])

LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]

LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]

ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))

ON ([vri2_Software Product].[Guid] = [ajs5_ra].[ParentResourceGuid])

LEFT OUTER JOIN [Inv_Software_Image] AS [ajs7_Inv_Software_Image]

ON ([vri2_Software Product].[Guid] = [ajs7_Inv_Software_Image].[_ResourceGuid])

WHERE [dca3_Software Product State].[IsManaged] = 1

ORDER BY [Name] ASC

When I edited the query to clean it up I removed a few more items from the SELECT statement. I must have missed it and in my proof reading didn't even see it since it shows up in a very light grey color on my screen!

I editedt he original post to rmeove the , so others shouldn't have the same problem, sorry about that!

Also, just to let you know these are not the full blown queries that are actually executed when you click on the Newly Discovered list or the Managed Software list. I can attach the original FULL queries if you are interested.

Thanks I'd be interested in that just to see if there is much difference with what it returns.

The only line I edited was the SET@ line to remove the names of my security groups...

DECLARE @v1_TrusteeScope nvarchar(1013)
   SET @v1_TrusteeScope = N
SELECT
   [vri2_Software Component].[Guid] AS [GUID],
   [vri2_Software Component].[Name] AS [Name],
   [ajs6_ItemPresentation].[ImageUrl] AS [Image Url],
   [ajs3_vAC_InstalledSoftware].[IsManaged] AS [Managed],
   [vri2_Software Component].[ResourceTypeGuid] AS [ResourceTypeGuid],
   [ajs5_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Component_Item] AS [vri2_Software Component]
      INNER JOIN [vAC_InstalledSoftware] AS [ajs3_vAC_InstalledSoftware]
         ON ([vri2_Software Component].[Guid] = [ajs3_vAC_InstalledSoftware].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs4_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs5_vRM_Company_Item]
            ON ([ajs4_ra].[ChildResourceGuid] = [ajs5_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Component].[Guid] = [ajs4_ra].[ParentResourceGuid])
            AND
            (
               ([ajs4_ra].[ResourceAssociationTypeGuid] = 'Software Component to Company (292dbd81-1526-423a-ae6d-f44eb46c5b16)')
            )
      LEFT OUTER JOIN [ItemPresentation] AS [ajs6_ItemPresentation]
         ON ([vri2_Software Component].[Guid] = [ajs6_ItemPresentation].[Guid])
WHERE
   (
      (
         ([ajs3_vAC_InstalledSoftware].[IsManaged] = 0)
      )
   )
ORDER BY
   [Name] ASC

I edited only the SET line again to remove the names of my security groups.

DECLARE @v1_TrusteeScope nvarchar(1013)
   SET @v1_TrusteeScope = N
SELECT
   [vri2_Software Product].[Guid] AS [GUID],
   [vri2_Software Product].[Name] AS [Name],
   [ajs7_Inv_Software_Image].[ImageUrl] AS [Image Url],
   [dca3_Software Product State].[IsManaged] AS [Managed],
   [ajs6_vRM_Company_Item].[Name] AS [Manufacturer]
FROM
   [vRM_Software_Product_Item] AS [vri2_Software Product]
      INNER JOIN [Inv_Software_Product_State] AS [dca3_Software Product State]
         ON ([vri2_Software Product].[Guid] = [dca3_Software Product State].[_ResourceGuid])
      INNER JOIN [vAC_InstalledProducts] AS [ajs4_vAC_InstalledProducts]
         ON ([vri2_Software Product].[Guid] = [ajs4_vAC_InstalledProducts].[Guid])
      LEFT OUTER JOIN ([ResourceAssociation] AS [ajs5_ra]
         LEFT OUTER JOIN [vRM_Company_Item] AS [ajs6_vRM_Company_Item]
            ON ([ajs5_ra].[ChildResourceGuid] = [ajs6_vRM_Company_Item].[Guid]))
         ON ([vri2_Software Product].[Guid] = [ajs5_ra].[ParentResourceGuid])
            AND
            (
               ([ajs5_ra].[ResourceAssociationTypeGuid] = 'Software Product to Company (d5c66d5a-7686-4ca2-b7c1-ac980576ce1d)')
            )
      LEFT OUTER JOIN [Inv_Software_Image] AS [ajs7_Inv_Software_Image]
         ON ([vri2_Software Product].[Guid] = [ajs7_Inv_Software_Image].[_ResourceGuid])
WHERE
   (
      (
         ([dca3_Software Product State].[IsManaged] = 1)
      )
   )
ORDER BY
   [Name] ASC

When I run the this in SQL I get this error, it parses ok though.

Msg 207, Level 16, State 1, Line 2

Invalid column name 'N'.

I'm not really a SQL person so I don't understand why.

Remove the DECLARE and SET lines...

DECLARE @v1_TrusteeScope nvarchar(1013)
   SET @v1_TrusteeScope = N
 

Hello. I'm looking for a similar report but I need to filter it for product category, for example "desktop software".

My final goal is to create a report of computer wich have installed the software that belong to one specificy category.

I have also some trouble to find the correct table witht the software categories

Can you help me?