Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

How to export Altiris 7.1 "Software Product License & Usage" results

Created: 13 Aug 2012 | 20 comments

I am using Altiris 7.1 and there is great interface for displaying summary data of how many licenses you own for a specific piece of software and who is using it (Metering). The problem is there doesn't seem to be an easy way to export the data, or even copy paste it for that matter so I am left to manually retype the data I see on the screen. I tried looking for a report in the back end this data is driven off as that would be exportable but can't find it. Any advice would be greatly appreciated. See screenshot below:

Tim Hurd
thurd@greatbatch.com

Comments 20 CommentsJump to latest comment

RufusS's picture

Hi Tim,

I have a query that will display all the software, total installedand the license count, 2 other report that will give you the software and installed count with a drilldown(by dubble clicking the software), computers installed on.

you can combine the report SQL to have one report and drill down to all 3 levels. 

AttachmentSize
License Compliance -Managed Software-.xml 12.88 KB
Installed Software -Custom Managed Software Only-.xml 14.55 KB
Installed Software per computer -Custom Managed-(Drilldown report).xml 11.62 KB
CBZ9104's picture

Hi RufusUCS

Can we get report if the license count is over deployed

Can we figure out which are the over deplyed users 

Tim Hurd's picture

Thanks RufusUCS but without the metering info in there it doesnt completely solve the problem.

RufusS's picture

Hi Tim,

Attached is a query that will give you the Application, how many is installed, Used and the % used with the total licenses. you can then have a drill down to see on what computer the cliecked software is installed as perviously attached.

if you want a report with graphics ITA should be able to do it, we use SSRS to create a report with multiple data sources and report parts. i.e. datagrid and graph on the same report.

hope this get you going in the right direction.

Regrads,

Rufus.

AttachmentSize
License_Info.txt 1.73 KB
planetmanu's picture

Hi Rufus UCS,

   We trying to pull out one report, which is software product License compliance, but we unbale to add location wise filter in report.

Above license info query very helpful to us. but we need location wise filter in it.

Please help us & provide query with location wise filter in above query provided by you.

Thanks & Regards

RufusS's picture

Hi PlanetManu,

The XML files attached you can filter the results, then when you click to drill down the parameters will be sent to the other report and only show the results in respect to the filter selected before.

If that is not wahat you are looking for please let me know. or attach the file and I can look at it.

planetmanu's picture

Thanks for your reply.

We required a report of software compliance

In that report following format req.

Software Name Total Install Count Total (Metering) Usage Count Total License Count Compliance
         

We need above filter in the report

Thanks & Regards,

Planetmanu

RufusS's picture

Hi,

Attached is the SQL for the above, please let me know if it will work for you.

Regards,

Rufus.

AttachmentSize
License_Info.txt 2.02 KB
planetmanu's picture

Hi,

Thank you very much for the report. you are great...

Same report we want. Its running perfect.

But we need to filter as location wise, as per the above jpg.

Thanks & Regards,

Planetmanu

planetmanu's picture

Please provide same query with Location wise filter

RufusS's picture

Hi,

the report xml is attached just import.

please mark as soluiton. :)

AttachmentSize
License Compliance.zip 3.72 KB
planetmanu's picture

Hi RefusUCS,

First of all many thanks for your and your team (...may be I dont know your hierarchy) to work on our request and providing the solution.

This is Bhagyesh and "PLANETMANU" belongs to my Team. While we are all very eagar to mark your reply as SOLUTION, however Tim Hurd would have the authority for that.

One Laaaaaaaaaaast  Request which is very important to point out here....

As you can see from the 2 screenshots I have attached, when we select the Filters, the "Total Install" & "Total Used"  count changes HOWEVER, the "Purchased License" count remains same which is the import requirement cum parameter to change accordingly. Example: when we select ABC Filter it gives 9048 "Purchased License" count for Microsoft Office and then we get the similar count for Filter XYZ. Actually 9048 is the total license count which is split between ABC and XYZ.  Therefore when we select a filter, "Total Installed", "Total Used" and "Purchased License"  i.e. all 3 coulumns should change according to filter selection.

Guys....pls pls and pls try to understand and provide solution if its feasibile though its a practical requirement.

Regards

Bhagyesh/PLANETMANU

ABC.JPG XYZ.JPG
RufusS's picture

Hi,

The system is not developed to support that, I had the same issue where we have multiple customers have diffrent licenses purchased, so I had to manually develope an appication for that. the best way now is to save to excel and manually update purchased licenses for each department/customer. or log a call to support to query the report results in respects to the scoping.

planetmanu's picture

Dear RefusUSD/Tim,

Many thanks for the clarification and the support so far.

We have raised this issue with Symantec but feeling internally that it should have some solution or query which will pull the required data together. Now your clarification put a final signature there as this seems product enhancement.

Best regards

Bhagyesh

Falquian's picture

Hi, RufusUCS:

Thanks for providing this useful reports. I'd like to ask you if you could help me in getting the [Usage %] column from the "License Compliance" Report to be included in your "Installed Software -Custom Managed Software Only" report?

I've been messing with both reports, trying to get this done, with no luck. Unfortunately I'm not an SQL expert :(

Thanks a lot:

      Falquian

Kind regards:

     Falquian

If this post is useful to you, remember to mark it as a solution ;)

RufusS's picture

Hi Falquian,

below is a query we use, hope this helps:

SELECT DISTINCT vsp.[Guid]
    , vsp.Name [Application]
    , COUNT (DISTINCT iis._ResourceGuid) [Total Installed]
    , COUNT (DISTINCT a._ResourceGuid) AS [Total Used]
    , CASE WHEN spu.IsUsageTracked = 0 OR spu.IsUsageTracked IS NULL THEN 'NA' ELSE CAST( (CAST((COUNT (DISTINCT a._ResourceGuid)) AS INTEGER) * 100) / (COUNT (DISTINCT iis._ResourceGuid))  AS NVARCHAR (MAX)) END [Usage %]

FROM vSoftwareProduct vsp
    INNER JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vsp.Guid AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
    INNER JOIN Inv_InstalledSoftware iis ON iis._SoftwareComponentGuid = ra.ChildResourceGuid AND iis.InstallFlag = 1
    INNER JOIN vComputer i ON i.Guid = iis._ResourceGuid
    LEFT JOIN Inv_Software_Product_Usage spu ON spu._ResourceGuid = vsp.Guid
    LEFT JOIN (SELECT DISTINCT ms.FileResourceGuid, ms._ResourceGuid, MAX(ms.[Last Start]) [Last Start], ra.ParentResourceGuid [ParentResourceGuid], SUM (CAST (se.IsMetered AS INT)) [Metered]  FROM dbo.Inv_Software_Execution se
                LEFT JOIN dbo.vAMMonthlySummary ms
                    ON ms.FileResourceGuid = se._ResourceGuid
                JOIN ResourceAssociation ra
                    ON ra.ChildResourceGuid = se._ResourceGuid
                WHERE se.IsMetered = 1            
                GROUP BY ms.FileResourceGuid, ms._ResourceGuid, ra.ParentResourceGuid
                ) a ON a.ParentResourceGuid = ra.ChildResourceGuid
            AND (a._ResourceGuid = i.Guid OR a._ResourceGuid IS NULL)
            AND a.[Last Start] BETWEEN GETDATE() - spu.UsageCount  and GETDATE()
GROUP BY vsp.Name, spu.IsUsageTracked, vsp.[Guid]
ORDER BY vsp.Name

Falquian's picture

RufusUCS:

Thanks a lot for this two pieces of code. I think you hit in the right nail :)

I'll try to adapt it to what I've been requested, but I think it suits very nicely on what I must provide.

Thanks again (it's a pity I cannot mark this as a solution)

Kind regards:

     Falquian

If this post is useful to you, remember to mark it as a solution ;)

RufusS's picture

and here is a report for Installed vs. used vs. purchased

SELECT DISTINCT vsp.Name [Application]
    , COUNT (DISTINCT iis._ResourceGuid) [Total Installed]
    , COUNT (DISTINCT a._ResourceGuid) AS [Total Used]
    , CASE WHEN spu.IsUsageTracked = 0 OR spu.IsUsageTracked IS NULL THEN 'NA' ELSE CAST( (CAST((COUNT (DISTINCT a._ResourceGuid)) AS INTEGER) * 100) / (COUNT (DISTINCT iis._ResourceGuid))  AS NVARCHAR (MAX)) END [Usage %]
    , CASE WHEN spc.[Purchased Licenses] IS NULL THEN 0 ELSE spc.[Purchased Licenses]END AS [Purchased Licenses]

FROM vSoftwareProduct vsp
    INNER JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vsp.Guid AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
    INNER JOIN Inv_InstalledSoftware iis ON iis._SoftwareComponentGuid = ra.ChildResourceGuid AND iis.InstallFlag = 1
    INNER JOIN vComputer i ON i.Guid = iis._ResourceGuid
    LEFT JOIN Inv_SoftwareProduct_ComplianceInfo spc ON spc._ResourceGuid = vsp.Guid
    LEFT JOIN Inv_Software_Product_Usage spu ON spu._ResourceGuid = vsp.Guid
    LEFT JOIN (SELECT DISTINCT ms.FileResourceGuid, ms._ResourceGuid, MAX(ms.[Last Start]) [Last Start], ra.ParentResourceGuid [ParentResourceGuid], SUM (CAST (se.IsMetered AS INT)) [Metered]  FROM dbo.Inv_Software_Execution se
                LEFT JOIN dbo.vAMMonthlySummary ms
                    ON ms.FileResourceGuid = se._ResourceGuid
                JOIN ResourceAssociation ra
                    ON ra.ChildResourceGuid = se._ResourceGuid
                WHERE se.IsMetered = 1            
                GROUP BY ms.FileResourceGuid, ms._ResourceGuid, ra.ParentResourceGuid
                ) a ON a.ParentResourceGuid = ra.ChildResourceGuid
            AND (a._ResourceGuid = i.Guid OR a._ResourceGuid IS NULL)
            --AND a.[Last Start] BETWEEN GETDATE() - spu.UsageCount  and GETDATE()

GROUP BY vsp.Name, spu.IsUsageTracked, spc.[Purchased Licenses]
ORDER BY vsp.Name

TheAdminKell's picture

So I tried to import the Software by Computer XML but no data was returned by the MC could you check the XML?