Software License Report
We are interested in generating many reports for our software license compliance project. We want reports that will give the list of computers found with a particular software installed. The output would be similar to the results found when viewing a Software License Asset in the "Machines with Software Installed" section. Ideally the results would also be limited to a subset of computers with a name beginning with "xyz" since that will break up the list by our different sites.
For example, a smart software label was created to find various titles containing Adobe Acrobat 9. This was assigned to a Software Asset which found 26 matching software titles. Then a Software License was created and applied to the software asset. When viewing the License it lists 600 computers but so far we only have records for 400 licenses so need to track down the sites where the license records are missing or they are out of compliance.
All I need is the list of computers matching the search criteria already established in over 300 smart labels and don't want to have to re-create a sql query with all the criteria established creating the smart labels like software display name includes this but not that and OS is this, etc.
Please tell me I'm overlooking something easy to do! Thanks in advance.
For example, a smart software label was created to find various titles containing Adobe Acrobat 9. This was assigned to a Software Asset which found 26 matching software titles. Then a Software License was created and applied to the software asset. When viewing the License it lists 600 computers but so far we only have records for 400 licenses so need to track down the sites where the license records are missing or they are out of compliance.
All I need is the list of computers matching the search criteria already established in over 300 smart labels and don't want to have to re-create a sql query with all the criteria established creating the smart labels like software display name includes this but not that and OS is this, etc.
Please tell me I'm overlooking something easy to do! Thanks in advance.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
airwolf
14 years ago
Add the location smart labels you've setup as a column, and then choose that column as the "Break On Column" for the report. This will break out your machines by location.
Posted by:
RichB
14 years ago
Thanks Andy, that's a good idea when I get to that point since we do have smart location labels. I'm still back at square one trying to make a Select statement for this report. Once a model is created I can duplicate and change the name of the software license to report on.
Using the KBOX report wizard I can create a report for the License Asset example shown in the screen shot (zipped and attached).
What I'd like to have is a breakdown of the computers in the 607 Seats Installed. Here's the SQL that created the screen shot:
Using the KBOX report wizard I can create a report for the License Asset example shown in the screen shot (zipped and attached).
What I'd like to have is a breakdown of the computers in the 607 Seats Installed. Here's the SQL that created the screen shot:
SELECT ASSET.NAME AS ASSET_NAME, ASSET_DATA_7.FIELD_1 AS FIELD_1, A2_L10001.NAME AS A2_FIELD_10001, COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS INSTALLED_COUNT FROM ASSET_DATA_7 LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7 LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_6 AD2 ON AD2.ID = A2.ASSET_DATA_ID
LEFT JOIN LABEL A2_L10001 ON A2_L10001.ID = AD2.FIELD_10001 LEFT JOIN ASSET_ASSOCIATION JX2 ON JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID)) WHERE ASSET.NAME LIKE '%Ado Acrobat Pro 9 Win%' GROUP BY ASSET_DATA_7.ID ORDER BY ASSET.NAME asc
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.