Idea for dashboard to visualize robots/server and virtual/server (vmware) status.

[Miller Echagarreta]

Hi Everyone, I need to create a dashboard to visualize all my servers agroup for robot and virtual. This dash must show those key points.

*New: added in the last 24 hours to the UIM inventory

*New without monitoring: Are robots that was installed but it does not have the CDM probe at least

*Fail: Robots with fails and not work, lost monitoring.

Any idea. I think with some querys can I resolve this..!!!

[Rajesh Banuka]

Hi Miller,

Please find the queries for requirements stated.  

-- Non-Robot Inventory added in last 24 hours

 SELECT * FROM CM_COMPUTER_SYSTEM CS

 WHERE create_time >= GETDATE() -1

 AND NOT EXISTS ( 

SELECT 1 FROM CM_NIMBUS_ROBOT NR 

INNER JOIN CM_DEVICE CD ON NR.dev_id = CD.dev_id

WHERE CD.cs_id = CS.cs_id

 ) 

 

-- Robot Inventory added in last 24 hours

 SELECT * FROM CM_COMPUTER_SYSTEM CS

 WHERE create_time >= GETDATE() -1

 AND EXISTS ( 

SELECT 1 FROM CM_NIMBUS_ROBOT NR 

INNER JOIN CM_DEVICE CD ON NR.dev_id = CD.dev_id

WHERE CD.cs_id = CS.cs_id

 ) 

--New without monitoring: Are robots that was installed but it does not have the CDM probe at least

 

SELECT CS.* FROM CM_NIMBUS_ROBOT NR

 INNER JOIN CM_DEVICE CD ON CD.dev_id = NR.dev_id

 INNER JOIN CM_COMPUTER_SYSTEM CS ON CS.cs_id = CD.cs_id

 WHERE EXISTS (

  SELECT 1 FROM S_QOS_DATA SD

INNER JOIN S_QOS_SNAPSHOT SN ON SN.table_id = SD.table_id

INNER JOIN CM_CONFIGURATION_ITEM_METRIC CIM ON CIM.CI_METRIC_ID = SD.CI_METRIC_ID

INNER JOIN CM_CONFIGURATION_ITEM CI ON CI.CI_ID = CIM.CI_ID

INNER JOIN CM_DEVICE CD2 ON CD2.DEV_ID = CI.DEV_ID

WHERE CD2.cs_id = CS.cs_id

 )

-- *Fail: Robots with fails and not work, lost monitoring.

 SELECT * FROM CM_NIMBUS_ROBOT NR
 INNER JOIN CM_DEVICE CD ON CD.dev_id = NR.dev_id
 INNER JOIN CM_COMPUTER_SYSTEM CS ON CS.cs_id = CD.cs_id
 WHERE NR.robot_active = 0

Please note the queries can be optimized further as per your DB configuration.

Rgds,

Rajesh B

[Miller Echagarreta]

Hi Banuka, how are you...!!!

Thanks for your help. 
For this script is necesary another filter? i thinking that maybe not working for this requirement: it does not have the CDM probe at least. 
I thinked to filter with probe_name field <> cdm but this field only has controller

--New without monitoring: Are robots that was installed but it does not have the CDM probe at least

 

SELECT CS.* FROM CM_NIMBUS_ROBOT NR

 INNER JOIN CM_DEVICE CD ON CD.dev_id = NR.dev_id

 INNER JOIN CM_COMPUTER_SYSTEM CS ON CS.cs_id = CD.cs_id

 WHERE EXISTS (

  SELECT 1 FROM S_QOS_DATA SD

INNER JOIN S_QOS_SNAPSHOT SN ON SN.table_id = SD.table_id

INNER JOIN CM_CONFIGURATION_ITEM_METRIC CIM ON CIM.CI_METRIC_ID = SD.CI_METRIC_ID

INNER JOIN CM_CONFIGURATION_ITEM CI ON CI.CI_ID = CIM.CI_ID

INNER JOIN CM_DEVICE CD2 ON CD2.DEV_ID = CI.DEV_ID

WHERE CD2.cs_id = CS.cs_id

 )

Any idea ?

[Luc Christiaens]

You can try following query to list robots without a "cdm" probe:

select r.ip,r.os_major,r.os_minor,r.domain,r.hub,r.robot,s.origin,r.is_hub,r.robot_active,r.user_tag_1,r.user_tag_2,r.address 
from cm_nimbus_robot r with(nolock), CM_COMPUTER_SYSTEM s with(nolock) 
where r.robot = s.name and  r.ip = s.ip and r.robot 
not in (select Robot from cm_nimbus_package p with(nolock), CM_NIMBUS_ROBOT r with(nolock) where p.robot_id = r.robot_id and name = 'cdm' )

[Miller Echagarreta]

Hi Luc, my database is oracle, and adjust the query and working now, but return many information... I think isn't working fine...

This table, not showing information... If not show data, maybe this part of query (where p.robot_id = r.robot_id and name = 'cdm' ) isn't working...?

[Miller Echagarreta]

Ey Luc, with this query i can find the robots that has installed cdm probe. But i don't how cross with another table and determine which robot does not have cdm installed. Any idea ?

select *  from cm_nimbus_probe 
package join cm_nimbus_robot robot on package.robot_id = robot.robot_id 
join CM_COMPUTER_SYSTEM ccs on robot.ip = ccs.ip 
and robot_active = 1 
and probe_name = 'cdm' 

[Luc Christiaens]

Hi Miller,

I forgot that the table: cm_nimbus_package is not activated by default.  This table contains installed probes and also custom packages and is activated by setting in the probe discovery_server under /setup/nis key: enable_cm_nimbus_package value: true

But you can also use table: cm_nimbus_probe. This table contains only the installed probes & is populated by default:

select r.ip,r.os_major,r.os_minor,r.domain,r.hub,r.robot,s.origin,r.is_hub,r.robot_active,r.user_tag_1,r.user_tag_2,r.address 
from cm_nimbus_robot r with(nolock), CM_COMPUTER_SYSTEM s with(nolock) 
where r.robot = s.name and  r.ip = s.ip and r.robot 
not in (select Robot from cm_nimbus_probe p with(nolock), CM_NIMBUS_ROBOT r with(nolock) where p.robot_id = r.robot_id and probe_name = 'cdm' )

[Miller Echagarreta]

Luc and Rajesh thanks for all your support.

I did accomplish the requirement with all these information.

[Rajesh Banuka]

Hi Miller,

 

I would suggest adding a filter to exclude controller, hub probe QOS. It will list the robot if there is a QOS collected from any monitoring probe. 

The change looks like below

SELECT CS.* FROM CM_NIMBUS_ROBOT NR

 INNER JOIN CM_DEVICE CD ON CD.dev_id = NR.dev_id

 INNER JOIN CM_COMPUTER_SYSTEM CS ON CS.cs_id = CD.cs_id

 WHERE EXISTS (

  SELECT 1 FROM S_QOS_DATA SD

INNER JOIN S_QOS_SNAPSHOT SN ON SN.table_id = SD.table_id

INNER JOIN CM_CONFIGURATION_ITEM_METRIC CIM ON CIM.CI_METRIC_ID = SD.CI_METRIC_ID

INNER JOIN CM_CONFIGURATION_ITEM CI ON CI.CI_ID = CIM.CI_ID

INNER JOIN CM_DEVICE CD2 ON CD2.DEV_ID = CI.DEV_ID

WHERE CD2.cs_id = CS.cs_id AND SD.probe NOT IN ('hub','controller')

 )

 

If you want to look for CDM or ntservices probe QOS, add condition like below. 

SELECT CS.* FROM CM_NIMBUS_ROBOT NR

 INNER JOIN CM_DEVICE CD ON CD.dev_id = NR.dev_id

 INNER JOIN CM_COMPUTER_SYSTEM CS ON CS.cs_id = CD.cs_id

 WHERE EXISTS (

  SELECT 1 FROM S_QOS_DATA SD

INNER JOIN S_QOS_SNAPSHOT SN ON SN.table_id = SD.table_id

INNER JOIN CM_CONFIGURATION_ITEM_METRIC CIM ON CIM.CI_METRIC_ID = SD.CI_METRIC_ID

INNER JOIN CM_CONFIGURATION_ITEM CI ON CI.CI_ID = CIM.CI_ID

INNER JOIN CM_DEVICE CD2 ON CD2.DEV_ID = CI.DEV_ID

WHERE CD2.cs_id = CS.cs_id AND SD.probe IN ('cdm','ntservices')

 )

 

 

Rgds,

Rajesh B