Custom SQL Script to Display NodeCaption columns from NCM_Nodes To NCM_RTNAudit

Hello Team,

Good day! This is an NCM feature but it is more on creating a View with a custom sql script to display the information. (NPM 12.1 and NCM 7.6)

I have setup the Orion NCM Real-time Change Detection feature for us to see any changes and automatically download configs for comparison. I have setup the RTCD feature fine but I'm having some trouble in creating a view that will show the NodeCaption on a custom table where I pull the objects (Column) on the NCM_RTNAudit.

I'm not an expert on SQL but I'm thinking SQL JOIN commands are needed to show information.

On the Web console I created a Custom table resource to display the infomation from NCM_RTNAudit:

Custom SQL Script on the resource:

Select TOP 30 * FROM NCM_RTNAudit

ORDER BY CONVERT (DateTime, DateTime) DESC

The display is perfect it's just that the table NCM_RTNAudit only shows NodeID and not the NodeCaption

While looking further I saw that the Node ID is also on the NCM_Nodes (View) and a NodeCaption column also exist.

Can someone help me on the script to show the NodeCaption, IP, DateTime and Message?

Thanks!!

Find more posts tagged with

rtcd

custom sql query

view

resource

Accepted answers

mesverrum

Select TOP 30 nodes.NodCaption, nodes.AgentIP, audit.DateTime, audit.Message

FROM NCM_RTNAudit audit

JOIN NCM_Nodes nodes on nodes.nodeid=audit.nodeid

ORDER BY CONVERT (DateTime, DateTime) DESC

All comments

mesverrum

Select TOP 30 nodes.NodCaption, nodes.AgentIP, audit.DateTime, audit.Message

FROM NCM_RTNAudit audit

JOIN NCM_Nodes nodes on nodes.nodeid=audit.nodeid

ORDER BY CONVERT (DateTime, DateTime) DESC

dpad

Wow! This worked like a charm!

Is there also a way to exclude any "orionsw" or "Admin" words/user accounts from the Message table? I only want to show the userid's and not the orion service account we are using.

Thanks!

mesverrum

so you want to exclude messages that contain orionsw?

This should work

Select TOP 30 nodes.NodCaption, nodes.AgentIP, audit.DateTime, audit.Message

FROM NCM_RTNAudit audit

JOIN NCM_Nodes nodes on nodes.nodeid=audit.nodeid

Where audit.message not like '%orionsw%'

ORDER BY CONVERT (DateTime, DateTime) DESC

dpad

Sorry for the late response but thank you!

I was also trying to add another filter statement but it seems I'm not getting it right

Select TOP 30 nodes.NodCaption, nodes.AgentIP, audit.DateTime, audit.Message

FROM NCM_RTNAudit audit

JOIN NCM_Nodes nodes on nodes.nodeid=audit.nodeid

Where audit.message not like '%orionsw%' and '%snmp%' <<<<<<------- is this the right query?

ORDER BY CONVERT (DateTime, DateTime) DESC

Cheers!