DX Unified Infrastructure Management

Hello team

Our management want to see the machines list which is being monitored on ump with their details as follows 

CPU usage available

Disk available 

Memory available 

IP address and hostname 

We need extract report from SQL query .Thus please provide single single query of each detail as mentioned above or if it can be fetch all from a single query would be great.

If both cannot be done then pls share the table names where these information can be found

Many thanks 

Try the following query. This is for SQL server.

select distinct s.source,d.dev_ip,s.probe,s.target,s.qos
from S_QOS_DATA s, CM_CONFIGURATION_ITEM_METRIC cim,CM_CONFIGURATION_ITEM ci,CM_DEVICE d,CM_COMPUTER_SYSTEM cs
where s.ci_metric_id=cim.ci_metric_id and cim.ci_id=ci.ci_id and ci.dev_id=d.dev_id and d.cs_id= cs.cs_id
and s.qos = 'QOS_CPU_USAGE' or s.qos = 'QOS_DISK_AVAILABLE' or s.qos = 'QOS_MEMORY_PERC_USAGE'
order by source asc;

Dear David

I have run the query 2 hrs ago. and still executing. Why is it taking so much time?

If you have a lot of data this could take a long time to complete. 

Did this complete? 

it took almost 4 hrs and complete with error. my db drive got full and i had to truncate the logs

You may want to investigate joining S_QOS_DATA (for machine name, QOS, and table_id) and S_QOS_SNAPSHOT (for the value)

Might be faster.

Regardless, that join should not have been that slow which would argue for an inadequately sized database server or a problem with how you are collecting your data - most likely the frequency at which you are collecting it.

And perhaps the query is in error:

You might want to try running:

select distinct s.source,d.dev_ip,s.probe,s.target,s.qos
from S_QOS_DATA s, CM_CONFIGURATION_ITEM_METRIC cim,CM_CONFIGURATION_ITEM ci,CM_DEVICE d,CM_COMPUTER_SYSTEM cs
where ( s.ci_metric_id=cim.ci_metric_id and cim.ci_id=ci.ci_id and ci.dev_id=d.dev_id and d.cs_id= cs.cs_id )
and s.qos in ( 'QOS_CPU_USAGE' , 'QOS_DISK_AVAILABLE' , 'QOS_MEMORY_PERC_USAGE')
order by source asc;

If that's too slow, drop the order by line.

And your ask was for the list of machines being monitored, which this is, but were you actually asking for the QoS current values for the systems being monitored?

-Garin