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. 

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

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.

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

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.

Thanks for your reply.

We required a report of software compliance

In that report following format req.

We need above filter in the report

Thanks & Regards,

Planetmanu

Hi,

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

Regards,

Rufus.

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

Please provide same query with Location wise filter

Hi,

the report xml is attached just import.

please mark as soluiton. :)

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

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.

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

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

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

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