| Purpose |
Query |
| 1. List all device-level profiles in the environment |
select * from SSRV2Profile where cs_id is not null and ancestorprofile is null; |
| 2. List all group-level profiles in the environment |
select * from SSRV2Profile where group_id is not null and ancestorprofile is null; |
| 3. List all pending profiles, which are not in 'ok' state |
select * from SSRV2Profile where cs_id is not null and status<>'ok'; |
| 4. List profiles missing checksum which may be due to 20.4 to 23.4 migration |
select * from SSRV2Profile sp left join SSRV2ProfileCheckSum spc on sp.profileId=spc.profile where sp.cs_id is not null and sp.status='notified' and spc.id is null; |
| 5. List profiles which are in 'Pending' state for robots that are down or don't exist. |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip, robot.hub, robot.robot from ssrv2profile profile
inner join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller' and profile.status='notified'
inner join cm_nimbus_robot robot on robot.dev_id = dev.dev_id
where robot.robot_active=0 |
| 6. List profiles which are in 'Pending' state for robots that are up. |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip, robot.hub, robot.robot from ssrv2profile profile
inner join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller' and profile.status='notified'
inner join cm_nimbus_robot robot on robot.dev_id = dev.dev_id
where robot.robot_active=1 |
| 7. List profiles which are in notified state/not being pulled as spooler is down |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip, robot.hub, robot.robot from ssrv2profile profile
inner join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller' and profile.status='notified'
inner join cm_nimbus_robot robot on robot.dev_id = dev.dev_id and robot.robot_active=1
inner join cm_nimbus_probe probe on probe.robot_id=robot.robot_id and probe.probe_name='spooler'
where probe.active=0; |
| 8. List profiles not synced as the device is not recognized as a robot |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip from ssrv2profile profile
left join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller'
where profile.status='notified' and dev.dev_id is null; |
| 9. Group profile deployment summary |
select status, COUNT(*) from SSRV2Profile where ancestorprofile=<group_profile_id> group by status; |
| 10. List all profiles of a cs_id |
select sp.profileName, sp.profileId, sps.pgm_hash, sps.prb_hash, sp.status, sp.ancestorprofile from ssrv2profile sp inner join ssrv2profilechecksum sps on sp.profileId = sps.profile where sp.cs_id=<cs_id>; |
| 11. List all profiles that will be pulled by robot with cs_id(including remote monitoring profiles) |
select sp.profileName, st.probe, sptp.minimum_probe_version, st.directives, sp.status, sps.* from SSRV2ProfileCheckSum sps inner join ssrv2profile sp on sps.profile = sp.profileId
inner join ssrv2template st on sp.template = st.templateid
left join ssrv2probetemplateupgrade stu on stu.template_id = st.templateid
left join ssrv2probetemplatespackage sptp on sptp.probe_name=stu.probe_name and sptp.probe_template_pkg_version = stu.probe_template_pkg_version
where sp.poller=<robot_cs_id>; |
| 12. Group level profile status summary |
select status, count(*) from ssrv2profile where ancestorprofile= group by status |
| 13. List all policies that will be pulled by Robot with given cs_id |
select spts.policy_id, spts.id, spc.pgm_policy_hash, spts.status from policy p inner join ssrv2policytargetstatus spts on p.id=spts.policy_id
inner join ssrv2policychecksum spc on spc.policyprofile = spts.id where spts.poller=<robot_cs_id> |
| 14. List all policies as well as their associated QoS conditions |
select p.id, p.name, target.type, target.name, m.qos, t.operator, t.value, t.severity from Policy p inner join PolicyCondition c on p.id=c.policy_id inner join PolicyMetric m on c.policyMetric_id=m.id
inner join PolicyThreshold t on c.id=t.condition_id inner join PolicyTarget target on target.id=c.policyTarget_id |
| 15. Filter robots having given <QoS_name> value more then the given <threshold_value> for given <policy_id> |
select sqd.robot, sh.samplevalue, sh.sampletime, cd.cs_id from s_qos_data sqd inner join s_qos_snapshot sh on sqd.table_id = sh.table_id
inner join cm_configuration_item_metric cim on sqd.ci_metric_id = cim.ci_metric_id
inner join cm_configuration_item ci on ci.ci_id = cim.ci_id
inner join cm_device cd on cd.dev_id = ci.dev_id
inner join ssrv2policytargetstatus sps on sps.cs_id= cd.cs_id and policy_id=<policy_id>
where sqd.qos='<QoS_name>' and sh.samplevalue >= <threashold_value>; |
| 16. List all mcs profiles and their associated parameter thresholds at group/devices level. |
select
pro.profileId,
pro.profilename,
pc.policy_id,
pt.condition_id,
po.author,
po.name,
pc.policyMetric_id,
pc.seededFromProfileId,
pt.operator,
pt.severity,
pt.type,
pt.value
from
Policy po
left join PolicyCondition pc on po.id = pc.policy_id
left join PolicyThreshold pt on pt.condition_id = pc.id
left join SSRV2Profile pro on po.seededFromProfileId = pro.profileId
--where po.name='your policy name'
--where pro.profileId = 'your profile id' |
| 17. List alarm policies with regex filter for CentOS group. CentOS group. |
select Policy.name as "Policy Name",
PolicyMetric.name as "Policy Metric Name",
PolicyMetric.probeName,
PolicyMetric.qos,
PolicyMetric.ciname,
PolicyTarget.name AS "Group Name",
PolicyThreshold.type as "Alarm Type",
PolicyThreshold.operator,
PolicyThreshold.value,
PolicyThreshold.severity
FROM Policy INNER JOIN
PolicyCondition ON Policy.id = PolicyCondition.policy_id INNER JOIN
PolicyMetric ON PolicyCondition.policyMetric_id = PolicyMetric.id INNER JOIN
PolicyTarget ON PolicyCondition.policyTarget_id = PolicyTarget.id INNER JOIN
PolicyThreshold ON PolicyCondition.id = PolicyThreshold.condition_id
WHERE PolicyTarget.name like 'CentOS'
AND PolicyMetric.ciname like '~%' |
| 18. List profiles which are not synced as robots are down, or don't exist. |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip, robot.hub, robot.robot from ssrv2profile profile
inner join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller' and profile.status='notified'
inner join cm_nimbus_robot robot on robot.dev_id = dev.dev_id where robot.robot_active=0; |
| 19. List profiles which are not being pulled into the probe's configuration as spooler is down. |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip, robot.hub, robot.robot from ssrv2profile profile
inner join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller' and profile.status='notified'
inner join cm_nimbus_robot robot on robot.dev_id = dev.dev_id and robot.robot_active=1
inner join cm_nimbus_probe probe on probe.robot_id=robot.robot_id and probe.probe_name='spooler'
where probe.active=0; |
| 20. List all alarm policies and thresholds |
select * from policy p
join PolicyCondition pc on pc.policy_id = p.id
join PolicyMetric pm on pm.id = pc.policyMetric_id
join PolicyTarget pt on pt.id = pm.id
join policythreshold pot on pot.condition_id = pm.id |
| 21. List all device level alarm policies |
select p.name as policyname,
pm.name as QOS,
pm.probename as probe,
pm.description,
pm.shortunit as unit,
pt.name as device_group,
pt.type,
pot.operator,
pot.value as threshold,
pot.severity,
pot.type
from policy p
join PolicyCondition pc on pc.policy_id = p.id
join PolicyMetric pm on pm.id = pc.policyMetric_id
join PolicyTarget pt on pt.id = pm.id
join policythreshold pot on pot.condition_id = pm.id
where pt.type = 'DEVICE' |
| 22. List all group level alarm policies |
select ccs.name as devicename,
p.name as policyname,
pm.name as QOS,
pm.probename as probe,
pm.description,
pm.shortunit as unit,
pt.name as device_group,
pt.type,
pot.operator,
pot.value as threshold,
pot.severity,
pot.type
from policy p
join PolicyCondition pc on pc.policy_id = p.id
join PolicyMetric pm on pm.id = pc.policyMetric_id
join PolicyTarget pt on pt.id = pm.id
join policythreshold pot on pot.condition_id = pm.id
join cm_group cg on cg.name = pt.name
join cm_group_member cgm on cgm.grp_id = cg.grp_id
join cm_computer_system ccs on ccs.cs_id = cgm.cs_id
where pt.type = 'GROUP' order by ccs.name asc |
| 23. List all alarm policies target status |
Select * from ssrv2policytargetstatus |
| 24. List profiles not synced since the controller entry is missing for the poller device |
select profile.profileId, profile.profileName, profile.poller, profile.cs_id, profile.status, dev.dev_name, dev.dev_ip from ssrv2profile profile
left join cm_device dev on profile.poller=dev.cs_id and dev.probe_name='controller'
where profile.status='notified' and dev.dev_id is null;
Related notes:
cs_id and poller SHOULD be the same if the probe is a local probe not a remote probe.
For remote probes, cs_id and poller id should be different.
The 'poller' is the source (a.k.a. the monitoring device). |
| 25. List all robots that have a current profile applied to them |
select
cd.name,
sp.profileid,
sp.profilename,
sp.cs_id,
sp.status,
sp.ancestorprofile
from ssrv2profile sp
join cm_computer_system cd on sp.cs_id=cd.cs_id
where ancestorprofile=87 order by cd.name;
--where the ancestorprofile would be the profile ID at the group level.
|
| 26. List robots/devices where there were MCS profile changes. Optionally, change the value of the b.profileName in the WHERE clause for the specific profile that you're interested in determining which machines were changed/affected. |
select ccs.name, b.cs_id, b.status, b.profileid, b.ancestorprofile, a.id, a.timestamp, a.objectname, a.objectvalue, a.action
FROM SSRV2AuditTrail a
JOIN SSRV2Profile b
ON a.objectid = b.profileId
JOIN (SELECT objectid, max(id) AS id FROM SSRV2AuditTrail GROUP BY objectid) c
ON a.id = c.id AND a.objectid = c.objectid
JOIN CM_COMPUTER_SYSTEM ccs
ON b.cs_id = ccs.cs_id
WHERE b.profileName = 'Setup cdm'
ORDER BY name;
|
| 27. List Groups having devices for which group level profiles are not inherited or missing. |
select dg.name as 'Group Name', gp.profileName 'Group Level Profile Name', cs.name as 'Robot Name', dp.profileName as 'Device Level Profile Name'
from SSRV2Profile gp
inner join SSRV2DeviceGroup dg on gp.group_id=dg.id
inner join cm_group_member cgm on cgm.grp_id=dg.cm_group_id
inner join CM_COMPUTER_SYSTEM cs on cgm.cs_id=cs.cs_id
inner join SSRV2Template st on st.templateId=gp.template
left join SSRV2Profile dp on gp.profileId=dp.ancestorprofile and dp.cs_id=cgm.cs_id
where gp.group_id is not null and dp.profileId is null
and st.maxprofiles=1 and gp.template not in (select t.template from SSRV2Profile t where t.cs_id=cs.cs_id)
order by dg.name;
|
