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

Software License Compliance- Missing Total Install Count

Created: 29 Nov 2012 • Updated: 05 Apr 2013 | 9 comments
This issue has been solved. See solution.

I have recently installed IT Analytics and have been trying to use the cubes and some of the reports for Software Compliance.  In the Software License Compliance cube, I've used the pivot table to try and create a report.  I am able to pull in the Software Product Name and have pulled the 'Total Install Count'. 'Total License Count' and 'Compliance' totals to the report but there is no data showing up in the 'Total Install Count' column.  In the activity center in the Software tab, the purchases and installs all show up.  Under Home>Service & Asset Mgmt>Software Licensing, the installed totals show up.  It's just in IT Analytics that it's not working.  

For example, see the Adobe Acrobat Pro 7 screen-shots below.  

This first image is from the activity center.  You can see that we have 48 licenses and 18 installs.

This is from the Asset Management portal and shows the same thing....

But when I pull the info from IT Analytics, this is what I get....

 Any help would be great.  Thanks.

 

Comments 9 CommentsJump to latest comment

AndyN's picture

I don't know the answer to this but wanted to make you aware of the IT Analytics Webcast that is scheduled for Tuesday, December 4th. You may want to attend or watch the recording later.

 

https://symantecevents.verite.com/27707/37401

Also, there is a new Group on Connect focused on Reporting. Tons of great info on IT Analytics. You may want to join that Group or post your question there.

https://www-secure.symantec.com/connect/groups/rep...

Here are some great resources for IT Analytics info:

 

How To Videos
Learning to Use IT Analytics Reporting

IT Analytics frequently asked questions and resources

tschenke's picture

Thanks Andy.  I will sign up for the webcast and I did join the reporting group this afternoon as well.  Thanks for the other links as well.  We just did get this installed and working, at least for the most part, so I haven't had time to really dive into it yet.  These resources will be a huge help.  Thanks again.

tschenke's picture

OK, I was told that this is an issue with SP2.  Apparently in SP2 they changed the table name or location of the installed details but did not make the change in IT Analytics.  So, is there a way to edit the existing cubes to point to the correct spot?  I have the custom cube tool but it doesn't look like that is going to work.  

tschenke's picture

We just upgraded to MP1 and this report still does not work in IT Analytics.  But I have a SQL report that you can run that gives you the same thing.

 

 

SELECT DISTINCT VSL.Name as 'Software License'
, CASE WHEN vcc.[Cost Center Code] is NULL then 'UNASSIGNED'
ELSE VCC.[Cost Center Code]
End as 'Cost Center'
, CASE WHEN VCC.Description is NULL then 'UNASSIGNED'
ELSE VCC.Description
End as 'Cost Center Description'
,(vu.[Given Name]+' '+vu.Surname) as [Cost Center Manager]
, BU.GAIC_Business_Unit as 'Business Unit'
, ISNULL(Purchases,0) as [Purchases]
, ISNULL(Installs,0) as [Installs]
, ISNULL(Purchases,0)-ISNULL(Installs,0) as [Compliance]
, CASE WHEN ((ISNULL(Purchases,0)-ISNULL(Installs,0))>=0) then 'COMPLIANT'
ELSE 'NOT COMPLIANT' END AS 'Status'
 
 from vSoftwareLicense VSL
 
FULL OUTER JOIN (
(
(SELECT DISTINCT SWI._ResourceGuid, VI1.Name ,RA.ChildResourceGuid as [CCGuid], RA2.ParentResourceGuid  as [licenseguid], VCC.[Cost Center Code],
COUNT(DISTINCT _ComputerResourceGuid) as [Installs]
from Inv_SoftwareProduct_InstallationInfo SWI
--COST CENTER ASSOCIATION  PARENT-ASSET CHILD-COST CENTER
FULL JOIN ResourceAssociation RA on SWI._ComputerResourceGuid = RA.ParentResourceGuid AND RA.ResourceAssociationTypeGuid = '9BC22D17-C0CF-45D5-9A8F-D62BBAFD955D'
JOIN vItem VI1 on VI1.Guid = SWI._ResourceGuid
LEFT JOIN vCostCenter VCC on VCC._ResourceGuid = RA.ChildResourceGuid
--COVERED SOFTWARE PRODUCT PARENT-LICENSE CHILD-PRODUCT
RIGHT OUTER JOIN ResourceAssociation RA2 on RA2.ChildResourceGuid = SWI._ResourceGuid and RA2.ResourceAssociationTypeGuid = '59C323AA-E616-489A-BD30-A671F4F6C8C8'
WHERE SWI._ResourceGuid IS NOT NULL
GROUP BY SWI._ResourceGuid, VI1.Name ,RA.ChildResourceGuid , VCC.[Cost Center Code],RA2.ParentResourceGuid
--ORDER BY VI1.Name, VCC.[Cost Center Code]
)SL
 
FULL JOIN
(SELECT DISTINCT VCC.[Cost Center Code],RA.ParentResourceGuid as _ResourceGUID, VI1.Name, RACC.ChildResourceGuid as [CCGuid],  SUM(SPD.Quantity) as Purchases
from Inv_Software_Purchase_Details SPD
--SOFTWARE PURHCASES ASSOCIATION PARENT-LICENSE CHILD-PURCHASE
JOIN ResourceAssociation RA on RA.ChildResourceGuid  = SPD._ResourceGuid AND RA.ResourceAssociationTypeGuid = 'f7002a49-11a6-44ea-af19-2e06632ff831'
--COST CENTER (SOFTWARE PURCHASE) ASSOCIATION PARENT-PURCHASE CHILD-COST CENTER
LEFT OUTER JOIN ResourceAssociation RACC on RACC.ParentResourceGuid = SPD._ResourceGuid AND RACC.ResourceAssociationTypeGuid = '338a1ecf-983d-49a9-b19e-fae972ac1a3f' 
JOIN vItem VI1 on VI1.Guid = RA.ParentResourceGuid 
LEFT JOIN vCostCenter VCC on VCC._ResourceGuid = RACC.ChildResourceGuid
GROUP BY VCC.[Cost Center Code],RA.ParentResourceGuid, VI1.Name, RACC.ChildResourceGuid) PUR
ON PUR._ResourceGUID = SL.licenseguid and ISNULL(PUR.CCGuid,0x0)=ISNULL(SL.CCGuid,0x0)))
ON VSL._ResourceGuid=COALESCE(SL.licenseguid,PUR._ResourceGuid)
 
LEFT OUTER JOIN vCostCenter VCC on VCC._ResourceGuid=COALESCE(SL.CCGuid,PUR.CCGuid)
FULL JOIN Inv_Business_Unit BU on BU._ResourceGuid = VCC._ResourceGuid
FULL JOIN ResourceAssociation RA on RA.ParentResourceGuid = VCC._ResourceGuid and RA.ResourceAssociationTypeGuid = '65D9B2E7-9AC9-4563-9225-8B46E1E0AEFE'
FULL OUTER JOIN vUser vu on vu.Guid = RA.ChildResourceGuid
 
--ORDER BY VI1.Name, VCC.[Cost Center Code]
WHERE VSL.Name IS NOT NULL
ORDER BY VSL.Name, (CASE WHEN vcc.[Cost Center Code] is NULL then 'UNASSIGNED'
ELSE VCC.[Cost Center Code]
End)
 
RomanSt's picture

Hello tschenke,

I found a missing part in your report, which is cost center percentage ownership. If a purchase is split to more than 1 cost centre, report shows incorrect results. Could you check if it is possible to implement, please? Or is there any similar report based on SW products (per cost centers), instead of licenses?

Thank you very much, Roman

abhishek_rane's picture

Hello,

Please refer to ‘Software Product Licensing Compliance’ report from Reports --- Service and Asset Management --- Contract Management--- Software Licensing’ , which will give you similar output that you are looking from IT analytics cube.

Thanks,

~Abhishek

RomanSt's picture

Hi tschenke,

there is a small bug in your report, as it does not reflect purchase cost ownership percentage and therefore totals are not correct in case of 1 purchase is split to more cost centers. Is it possible to reflect this in your report?

Thank you, Roman

sunnyDays44's picture

Also seeing this issue in the current environment, SP2 MP 1.1.  Still no fix for the Cube?

hugo_parra's picture

Thank you for your feedback.

A point fix is now available for this issue. Please open a case with Technical Support and state that you're seeing the issue documented in TECH197245. They will then provide you with the fix.

Regards,

-Hugo

SOLUTION