DX NetOps Performance Management (PM) Event query including Device Name & IP Address

Products

• CA Performance Management
• Network Observability

Issue/Introduction

How to use MySql to query the EM (Event Manager) database in the DX NetOps Portal server for Events with Device Name and IP Address?

In PM, 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

DX NetOps CA Performance Management (CAPM) all supported releases

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"