DX Unified Infrastructure Management

Hi Everyone,

We tried to make a total memory usage from two servers and we managed to create the query, but we cannot create an alarm if the results of the query exceed the threshold. Can CA UIM create a new alarm based on that SQL query output?

This is the query :

select top 1 (samplevalue+
   (select top 1 samplevalue
   from RN_QOS_DATA_0020
   where table_id=55
   order by sampletime desc))/14336*100 as 'Combine Usage'
from RN_QOS_DATA_0020
where table_id=33
order by sampletime desc

Thank You.

Best Regards,

Andre

I think that if I were doing this that I'd create an SLA that added these two values together instead of going direct at the database.

Creating your first SLA isn't a trivial task but there are some examples you could search here for.

If you are tied to this SQL approach, then you need to use LUA or some other language that UIM supports so you can invoke the alarm creation.

-Garin

You can create external command actions in your sql query.

In this example this query will create PU commands into a file to create deploy commands to upgrade all robots that don't have robot version 7.93 installed.

Instead of generating PU commands you can generate Nimalarm commands.

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

sqlcmd -S bgbulab53 -U sa -P *** -d CA_UIM -o c:\temp\luc.txt -Q "SELECT 'PU -u administrator -p xxxx /bgbulab53_domain/bgbulab53_hub/bgbulab53/automated_deployment_engine deploy_probe ""robot_update"" """" '+'/'+[domain]+'/'+[hub]+'/'+[robot] from cm_nimbus_package p, cm_nimbus_robot r with(nolock) where p.name = 'robot_update' and p.version > '7.93' and p.robot_id = r.robot_id"

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

Like Garin indicated this would be more flexible if done via LUA or Perl.

You can also use the sql_response probe to run your query on the UIM database and generate an alarm on threshold violation.

sql_response AC Configuration - CA Unified Infrastructure Management Probes - CA Technologies Documentation 

Hi Vishwanath Nair,

Thank you so much for your answer. sql_response is really the right probe to make an alarm based on the results of sql query. You Rock!

- Andre

I do agree that sql_response is the probe to use to get the results of an arbitrary query.

If you are operating on QoS though, going direct to the RN* tables is the incorrect approach. Consider that the name of the RN tables could (unlikely) change over time. The table id will definitely change over time - disovery_server will ensure that because of it's twitchiness.

As a result eventually your query criteria will break. The RN tables will become huge over time depending on the amount of data you keep - this SQL will potentially create a performance impact.

If you are intent on keeping this SQL and bypassing the SLA engine, then you need to tune this query - depending on your underlying database, you need to add nolock hints to the query at a minimum. Sub queries also create issues with the query optimizer and there is likely a much more efficient query than what is presented here - that tuning should be done on a system where you have a full set of test data - a table scan on a table with 2 days of data is going to be very different from one with 2 years.

You may also want to look at the snapshot table - this has the most recent value for each of the QoS which appears to be most of what your query is trying to calculate.

Also consider that the database structure is internal to the product - it is up to CA to decide what changes will happen in the next release. They have in the past changed the schema and the current schema is in a slow state of flux as it is with the introduction of the new database to store "big data". 

The final thing to consider here is the system that these alarms will be tied to - it may make no difference but when sql_response creates the alarm, it will tag it with the robot/source/origin of the robot running sql_response which might not be where you want the alert to go.

-Garin