OpsCenter Custom Report

[Phil_Trinh]

Hello,

         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?

Thanks,

Phil

[watsons]

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.

[tom_sprouse]

Phil,

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