SQL Query for Oracle DB monitoring using vCOPS

[abhilashhb]

Hi Guys,

I have installed a General SQL Adapter to monitor an Oracle database in my environment. But i'm not able to get it working as there are many problems in writing a SQL Quries. I'm working with one of the DBA and we are still not able to write the right query. Can someone give me a sample query so that i can test it or tell me how to write the query for Oracle DB. The sample files that are given under /samples/conf folder in the Plug-in are not helping. So if anyone has any link or if anyone has done it please help me with the query files.

Thank you in Advance

[mlebied]

Any query that will run successully in SQL Explorer or similar tool, using the same credentials you have provided in the Gen SQL adapter instance configuration, should work just fine.

Check the logs under the Support tab to see if there are error messages related to running the query

[abhilashhb]

Thanks for the reply mlebied. but the issue is we are not sure how to start of with it. Like for example, We have a command in oracle which tells whether the DB instance is running or not i.e select STATUS from v$instance;. So should my query file be

SELECT

STATUS from v$instance;

??

Or is there more things that i have to put in or a format where in there are lot of other preceding commands that come with respect to general SQl Adapter for vcops like the resourcekind,adapterkind and all those things? You get my point right?

I hope i'm putting it across clearly :smileyconfused: 

Would be great if you can tell me an example

[mlebied]

The General SQL Adapter requires you to write a query and output the results of that query into the proper fields in vcops. I have pasted an example below which queries a SQL server database for Citrix. After the SELECT statement, you must map the fields of the target database into the RESOURCENAME and RESOURCEKINDs for vcops.

Definitely read the instructions posted on the ftp site

ftp://ftp.integrien.com/VCOPS-Third_Party_Tool_Adapters/General%20SQL/SQL-Loader-Adapter-Install-and-Config-Guide.pdf

SELECT

.[server_name] AS ,

'XenApp_Application_License' AS ,

'' AS ,

.[tstamp] AS ,

'permenant_license_count' AS ,

SUM(.[perm_license_count]) AS ,

'in_use_license_count' AS ,

SUM(.[in_use_license_count]) AS ,

'overdraft_license_count' AS ,

SUM(.[overdraft_count]) AS ,

'available_license_count' AS ,

SUM(.[perm_license_count]) - SUM(.[in_use_license_count]) AS

FROM

WITH (NOLOCK)

WHERE

.[tstamp] = (

SELECT

MAX(.[tstamp])

FROM

WITH (NOLOCK)

)

AND

.[product_family_name] NOT IN ('Startup')

GROUP BY

.[tstamp],

.[server_name]

[abhilashhb]

Now i get a clear picture Thank you so much mlebied
So  XenApp_Application_License, permenant_license_count etc are all your tables in SQL database?

[abhilashhb]

And what exactly is AS in your query?

[mlebied · Accepted solution]

correct. the biggest challenge with the SQL Adapter is writing the query in such as way the produces output that is easily consumable by vcops, and maps directly to RESOURCEKIND and RESOURCENAME in vcops. The result can also be hierarchical for more efficiency, i.e.

metric

-- submetric1

-- submetric2