Event Log Reports

[cmatthews]

For PCI and systems management I made a few jasperReports that people may find useful. The reports in our report runner wrapper are drillable web reports but I'm sure others can find ways to make the reports useful.

I am attaching some samples of how the reports results look and the queries involved so people can either use it or improve upon it. I also have some other views of drill down data that summarizes events in different ways. The NetXMS tool is valuable in a variery of ways to my organization, so I wanted to share so of what I am doing with it.

For the "Event Systems" report.

SELECT
	id,
	primary_ip,
	agent_version,
	platform_name,
	uname
FROM
	NETXMS.netxms.NODES WITH (NOLOCK)
WHERE
	platform_name like 'windows%'
ORDER BY
	uname

For the "Event groups"

DECLARE @startDate INT
DECLARE @endDate INT

SET @startDate = (SELECT dbo.F_DATETIME_TO_UNIX_TIME('2011/02/01'))
SET @endDate = (SELECT dbo.F_DATETIME_TO_UNIX_TIME('2011/02/03'))

SELECT TOP 1500
	eventCfg.event_code,
	eventCfg.event_name,
	LEFT(event_message,20) as eventMessage,
	COUNT(*) as eventCount,
	COUNT(DISTINCT event_source) as nodeCount
FROM (
	SELECT
		*
	FROM
		NETXMS.netxms.EVENT_LOG WITH (nolock)
	WHERE
		event_timestamp BETWEEN @startDate and @endDate
	) eventDtl
	JOIN NETXMS.netxms.event_cfg eventCfg
		ON eventDtl.event_code = eventCfg.event_code
GROUP BY
	eventCfg.event_code,
	eventCfg.event_name,
	LEFT(event_message,20)
ORDER BY
	event_name

For the Event Details by System (Drill down)

SELECT
	event_id,
	eventCfg.event_code,
	eventCfg.event_name,
	dbo.F_UNIX_TIME_TO_DATETIME(event_timestamp) as event_datetime,
	event_message
FROM (
	SELECT
		*
	FROM
		NETXMS.netxms.EVENT_LOG WITH (nolock)
	WHERE
		event_source = $P!{eventSource}
	) eventDtl
	JOIN NETXMS.netxms.event_cfg eventCfg
		ON eventDtl.event_code = eventCfg.event_code
WHERE
	dbo.F_UNIX_TIME_TO_DATE(event_timestamp) BETWEEN '2011/01/01' AND '2011/01/31'
ORDER BY
	event_timestamp

Regards,

Clark

---

Event Systems.jpg

Event Details by System.jpg

[cmatthews]

Event Groups Sample -- Too big for original post.

---

Event Groups.jpg