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?
DX NetOps CA Performance Management (CAPM) all supported releases
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;
Different use cases may require some value changes.
The key values are:
"e.Description RLIKE '^A Threshold Violation event.*BGP_Down' "
"> UNIX_TIMESTAMP() - 86400"