CA Performance Management Event query including Device Name & IP Address

Document ID : KB000093614
Last Modified Date : 11/05/2018
Show Technical Document Details
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
Background:
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 
Instructions:
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"