CA Performance Management Event query including Device Name & IP Address

Products

• CA Infrastructure Management
• CA Performance Management - Usage and Administration

Issue/Introduction

How to use MySql to query the EM (Event Manager) database in the CA Performance Center server for Events with Device Name and IP Address

After migrating from NPC/NetVoyant to CA Performance Management we need an example query to fetch events with the related Device Name and IP address values in the output.

In CA Performance Center this query returns the event type sought: 

USE em; SELECT * FROM events WHERE ProducerID=3 AND TypeID=18 LIMIT 25;

However, it does not return Device Name & IP address. How can we gather that in the same query?

Environment

Production r3.5 

Resolution

This query provides the device name but not the item name that raised the Event.

This is the query that provides output including the Device Name and IP Address the Event is raised on.

SELECT DISTINCT(e.NPCEventID),ei.NPCItemID,e.OccurredOn,i.ItemName,i.ItemTypeName,i.ItemSubTypeName,e.Description, 
V6_ntoa(d.address) device_address 
FROM em.events e 
JOIN em.event_items ei ON (e.NPCEventID=ei.NPCEventID) 
JOIN em.items i ON (ei.NPCItemID=i.ItemID) 
LEFT OUTER JOIN netqosportal.t_device d ON (ei.NPCItemID=d.itemid) 
WHERE e.ProducerID=3 AND e.TypeID=18 AND e.Description RLIKE '^A Threshold Violation event.*BGP_Down' AND e.OccurredOn > UNIX_TIMESTAMP() - 86400; 

This is the query that provides output including the Device Name, IP Address and specific Item Name that the Event is raised on.

SELECT DISTINCT e.NPCEventID,eid.NPCItemID rtrid, e.OccurredOn, d.ItemName router, 
V6_ntoa(d.address) device_address, epc.Value component, e.Description 
FROM em.events e 
JOIN em.event_items eid ON (eid.NPCEventID=e.NPCEventID) and eid.itemindex=0 
JOIN netqosportal.t_device d ON (eid.NPCItemID=d.itemid) 
JOIN em.event_properties epc ON e.npceventid = epc.npceventid and epc.Name = 'ItemName' 
WHERE e.ProducerID=3 AND e.TypeID=18 AND e.Description RLIKE '^A Threshold Violation event.*BGP_Down' 
AND e.OccurredOn > UNIX_TIMESTAMP() - 86400;

Additional Information

Different use cases may require some value changes.

The key values are:

1. e.TypeID is the ID the Event focused on is known as in the DB. It is e.TypeID=18 in this case. 
2. e.Description is the value from the events Description field that should be matched on. The one used here is "e.Description RLIKE '^A Threshold Violation event.*BGP_Down' " 
3. e.OccurredOn is the Unix Time Stamp based value to represent a time frame to search against. In this case it is set to find all events that Occurred On or after "> UNIX_TIMESTAMP() - 86400"