Rajashekar Allala

Queries to fetch monitored databases information.

Discussion created by Rajashekar Allala on Jan 10, 2017

Hi All,

 

We have been monitoring the databases using CA insight and we are now moving the DB monitoring to UIM.

 

We were using queries to fetch the monitored Databases (MS SQL and Oracle) information from eHealth (insight feeds DB data to eHealth) to service catalog.

 

Similarly, we need to use queries from UIM to feed the monitored databases information to Service catalog.

I am using the below queries for MS SQL and Oracle respectively to fetch DB server name and Database name.

 

For MS SQL

-------------------------------

SELECT REGEXP_SUBSTR(TARGET, '[^\]+', 1, 1) DBSERVER,

               REGEXP_SUBSTR(TARGET, '[^\]+', 1, 2) DBNAME

FROM S_QOS_DATA Where QOS Like 'QOS_SQLSERVER_DATABASE_STATE%' AND TARGET NOT LIKE '%_CON%' AND TARGET NOT LIKE '%_conn%'

ORDER BY DBSERVER, DBNAME

 

For MS SQL

-------------------------------

SELECT REGEXP_SUBSTR(TARGET, '[^-]+', 1, 1) DBSERVER,

               REGEXP_SUBSTR(TARGET, '[^-]+', 1, 2) DBNAME

FROM S_QOS_DATA Where QOS Like 'QOS_ORACLE_CHECK_DBALIVE%' AND TARGET LIKE '%-%' ORDER BY DBSERVER, DBNAME

 

But the problem is, if we have moved one oracle/MS SQL database from one server to another. We are deleting the old DB connection and profiles and re-configuring them to point to new server. Now, when I run the above two queries, they are listing the databases twice (one from old server and one from new server).

 

Please help me with the query that returns the current database server and DB name once the monitored DB is moved from old to new server.

 

 

Regards,

Rajashekar

Outcomes