08-29-2014 07:53 AM
I've been playing with OpsCenter for a couple days trying to create a Custom Report to no avail. My report need to list all the policies on the Master Server, whether or not the policy is active and the servers associated with that policy. Can anyone provide some guidance on this matter?
08-31-2014 05:42 PM
Can't find a template where you can use for all policies, you can try with this SQL query:
SELECT distinct a.name AS 'policy name', b.friendlyname AS 'master server'
FROM nb_policy a, domain_masterserver b
WHERE ( a.masterserverID = b.id)
Please note this list all policies (regardless of active/inactive) and include SLP policies as well.
09-08-2014 01:37 PM
I have been working on something for another coworker / customer...
Please try the following and let me know how this looks...
It may report some duplicate information, which I am trying to resolve...
I have tested it, in my lab, but I must provide the following disclaimer:
This script is provided for educational purposes only...and should not be utilized for production reporting. Additional Testing of this script in your environment should be performed and validated against the data that is currently present in your environment.
If anyone can offer any additional support / advice - please let us know.
SELECT dms.networkname AS 'Master', dc.name AS 'ClientName', dp.name AS 'PolicyName', lps.name AS 'PolicyStatus' FROM domain_client dc JOIN domain_policyclient dpc ON dpc.clientname = dc.name JOIN domain_policy dp ON dp.name = dpc.policyname JOIN domain_masterserver dms ON dms.id = dpc.masterserverid JOIN lookup_policystatus lps ON dp.status = lps.id JOIN nb_policy nbp ON nbp.name = dp.name WHERE dp.versionno = nbp.versionNo AND dp.versionno = dpc.policyversionno GROUP BY dms.networkname, dp.name, dc.name, lps.name ORDER BY lps.name, dms.networkname, dc.name