02-26-2018 07:10 AM
We have OPSCenter version 8.0. I need to generate a list of all active policies. I don't need storage or success rate , etc. Just a list of policies. I didn't see any saved templates for this. Is there a SQL query that can do this?
Solved! Go to Solution.
02-26-2018 08:54 AM - edited 02-26-2018 08:59 AM
All you want is a list of active policies? If you are on Windows, you could open the Admin Console and go to the policies section. Right click->filter and select choose Actie 'equal to' yes. Then select all the policies and Ctrl+C and paste into an excel spreadsheet. This would give you a lot more information than just the policy names.
If you can't do that, you can use the following SQL query in an OpsCenter report.
select DISTINCT TOP 20 START AT 1 domain_Policy.name as "domain_Policy.name",domain_Policy.active as "domain_Policy.active" from domain_JobArchive , domain_Policy , domain_MasterServer where domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId and ( ( (domain_JobArchive.endTime BETWEEN '137377474957070000' AND '137389570957070000') ) AND ( ( (CASE WHEN domain_Policy.active = 1 AND domain_Policy.status = 0 THEN 1 ELSE 0 END) = '1') ) AND ( (domain_Policy.status IN (0 )) ) AND ( (domain_Policy.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61,2530,8422140 )) ) ) )
02-26-2018 08:54 AM - edited 02-26-2018 08:59 AM
All you want is a list of active policies? If you are on Windows, you could open the Admin Console and go to the policies section. Right click->filter and select choose Actie 'equal to' yes. Then select all the policies and Ctrl+C and paste into an excel spreadsheet. This would give you a lot more information than just the policy names.
If you can't do that, you can use the following SQL query in an OpsCenter report.
select DISTINCT TOP 20 START AT 1 domain_Policy.name as "domain_Policy.name",domain_Policy.active as "domain_Policy.active" from domain_JobArchive , domain_Policy , domain_MasterServer where domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId and ( ( (domain_JobArchive.endTime BETWEEN '137377474957070000' AND '137389570957070000') ) AND ( ( (CASE WHEN domain_Policy.active = 1 AND domain_Policy.status = 0 THEN 1 ELSE 0 END) = '1') ) AND ( (domain_Policy.status IN (0 )) ) AND ( (domain_Policy.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61,2530,8422140 )) ) ) )
02-26-2018 09:23 AM
The Master servers are linux.
I tried your query in OPSCenter, but "The report did not return any data."
02-26-2018 09:31 AM
Hi,
I did the test using the ops query sent by @GeForce123 and its working...You need to adjust for your environment, take a look
select DISTINCT TOP 20 START AT 1 domain_Policy.name as "domain_Policy.name",domain_Policy.active as "domain_Policy.active" from domain_JobArchive , domain_Policy , domain_MasterServer where domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId
Thiago
02-26-2018 09:40 AM
The master server can be Linux and you can still use the Admin Console on a Windows machine, like a laptop.
In regards to the query not working, not sure what to tell you. I just copied it into a custom SQL report and it ran just fine (I changed TOP 20 to TOP 1000 so it would return all 700+ of my policies).
I'd suggest just using the Admin Console method.
02-26-2018 10:17 AM
Thanks; the script worked.