NetXMS Support Forum

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 Groups Sample -- Too big for original post.