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. 

Is that an easy tweak? 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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.
Answered 04/01/2015 by: h2opolo25
Red Belt

  • 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.
    • 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.
      • 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)
Please log in to comment
0
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
Answered 04/01/2015 by: h2opolo25
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share