Advanced reporting against Add/Remove Programs (ARP), filtering and appending data – Part 2
I’m not a SQL expert by any means, so if you see something that isn’t quite right, well i already said I'm not a SQL expert :) This process involves custom tables and modifying your SQL database, proceed at your own risk. Don’t say i didn’t warn you…
This is an extension of Part 1
We made a pretty cool addition to this recently. We wanted the report to actually tell you if the computer was in the correct AD group and say “Yes/No” in the report web page. Then we would know if the computer/user was in the correct groups and if anything needed to be done. If they weren’t then we would know exactly what groups they needed to be added to. This queries our ADGroup field in our custom table AddRemoveProgram against SMS/SCCM’s v_ra_system_systemgroupname to find any matches.
Here is an example of the new output:
So in order for this to work, we need to add some new logic to our queries for the reports.
We need to add a new CASE for settings the Yes/No:
CASE
WHEN sgn.system_group_name0 IS NULL THEN 'No'
ELSE 'Yes'
END as InGroup
And then we need to add a new join statement to match up the AD group names:
LEFT OUTER JOIN dbo.v_ra_system_systemgroupname sgn ON arp.ADGroup = Replace(sgn.system_group_name0,'DOMAIN\','') AND arpd.ResourceID = sgn.ResourceID
You will need to modify the “DOMAIN” for your domain, this will allow you to not have to populate the custom table with DOMAIN\AD GROUP NAME, instead it just assumes that for you.
That’s it, pretty simple, yet adds a very nice feature to the report!
Hope this helps,
Chris