Report of software licenses in use per PC?
I see there is a master report of Microsoft software per PC. (Software title- computer list (MS Only).
How do we tweak that report to ONLY report on the licenses that we are tracking through License compliance? I dont need a report that shows every last module of microsoft products like compatibility packs, high definition audio driver packages, etc. I am only concerned with the actual paid and licensed products we are tracking in the License Compliance database.
How do we tweak that report to ONLY report on the licenses that we are tracking through License compliance? I dont need a report that shows every last module of microsoft products like compatibility packs, high definition audio driver packages, etc. I am only concerned with the actual paid and licensed products we are tracking in the License Compliance database.
Is that an easy tweak?
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
h2opolo25
9 years ago
This might be what you need to get started.... (Report will take quite a while to run because of all the join statements on large tables and the Group By)
select distinct(M.NAME) as 'Machine', M.USER_FULLNAME as 'User', M.CS_DOMAIN as 'Domain', M.IP as 'IP Address', GROUP_CONCAT(distinct(A.NAME) SEPARATOR ' , ') as 'License' from MACHINE M
left join SAM_MACHINE_JT SMJ on SMJ.MACHINE_ID = M.ID
left join SAM_VIEW_DISCOVERED_APPLICATIONS SVDA on SVDA.ID = SMJ.SAM_CATALOG_ID
left join ASSET_CATALOG_ASSOCIATION ACA on ACA.ASSOCIATED_CATALOG_ID = SMJ.SAM_CATALOG_ID
left join ASSET A on A.ID = ACA.ASSET_ID
left join ASSET_DATA_7 A7 on A7.ID = A.ASSET_DATA_ID
where A.ASSET_TYPE_ID = 7
GROUP BY M.NAME
This will give a report of the Machine Name, User Full Name, Domain, IP Address and the Licenses installed that are part of the License Assets. It's not Microsoft specific though.
Comments:
-
Close. VERY close. How do we get it to sort based on the license and not the PC? As it sits, if a PC has more than one license it consolidates the two licenses on one line. I need one line item for each individual license. Currently I get one line item for each PC regardless of how many licenses it has. - roy.urick@koorsen.com 9 years ago
-
You can try the code below (Formatting in replies sucks). You will need to make sure to have one license asset for each license type. For example, if you group AutoCAD 2014 and 2015 licensing into one asset then it'll show up once even if the user has both versions installed. - h2opolo25 9 years ago
-
hmmmm. Sorry to be a pain. The first version was a little closer to what we need. It had exactly the right information including machine name and system description. (all things we need)
Is there a way to get it to format like the MS only software listing does it and create a group for the software license, lists each machine with that software, breaks into another group for the next license, etc. ? Essentially breaking out the information displayed at the bottom of the screen when you look at the details of the license. (machines with software installed) - roy.urick@koorsen.com 9 years ago
Posted by:
h2opolo25
9 years ago
select A.NAME as 'License', GROUP_CONCAT(M.NAME SEPARATOR ' , ') as 'Machine' from MACHINE M
left join SAM_MACHINE_JT SMJ on SMJ.MACHINE_ID = M.ID
left join SAM_VIEW_DISCOVERED_APPLICATIONS SVDA on SVDA.ID = SMJ.SAM_CATALOG_ID
left join ASSET_CATALOG_ASSOCIATION ACA on ACA.ASSOCIATED_CATALOG_ID = SMJ.SAM_CATALOG_ID
left join ASSET A on A.ID = ACA.ASSET_ID
left join ASSET_DATA_7 A7 on A7.ID = A.ASSET_DATA_ID
where A.ASSET_TYPE_ID = 7
GROUP BY A.NAME
