New mac address email alert variables

Hello,

I found this thread: UDT Emails - Alert me when a rogue MAC Address appears on the network - variables which is exactly what I want to do. I followed Craig Norborg's suggestion but am getting but get the error below

"Port: MACRO SQL ERROR - Incorrect syntax near '{'.
IP: MACRO SQL ERROR - Incorrect syntax near '{'.
Port Name: MACRO SQL ERROR - Incorrect syntax near '{'.
Device: MACRO SQL ERROR - Incorrect syntax near '{'. "

Any help would be much appreciated, im not that familiar with SQL

Find more posts tagged with

Accepted answers

Littlebee_26

Upon checking, here are the alert variables that still work for a UDT 3.3:

IP: ${SQL: SELECT N.IP_Address FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

Device: ${SQL: SELECT N.Caption FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

All comments

m-milligan

Can you paste the code from your alert in this thread?

sh00ter0034

Hi, here it is:

Port: ${SQL: SELECT Name from [dbo].[UDT_Port] WHERE (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE (EndpointID = ${N=SwisEntity;M=EndpointID})))}

IP: ${SQL: SELECT N.IP_Address FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID) JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID) WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

Port Name: ${SQL: SELECT PortDescription from [dbo].[UDT_Port] WHERE (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE (EndpointID = ${N=SwisEntity;M=EndpointID})))}

Device: ${SQL: SELECT N.Caption FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID) JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID) WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

Thanks for the input/help

m-milligan

Those look correct. Can you open Database Manager on your Solarwinds server and run the queries below? In each query, replace ENDPOINT_ID with a valid Endpoint ID from your system. Do the queries run, or do they produce an error?

-- Port query

SELECT Name from [dbo].[UDT_Port] WHERE (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE (EndpointID = ENDPOINT_ID)))

-- IP query

SELECT N.IP_Address FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID) JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID) WHERE (P2EC.EndpointID = ENDPOINT_ID)

-- Port Name query

SELECT PortDescription from [dbo].[UDT_Port] WHERE (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE (EndpointID = ENDPOINT_ID)))

-- Device query

SELECT N.Caption FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID) JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID) WHERE (P2EC.EndpointID = ENDPOINT_ID)

jmfig

sh00ter0034,

We just battled through some of this yesterday and found that the alert parser needs to be updated to the UDT to hotfix 3 and the parser started to behave

here is the code we used, it is a bit different, because I want to only get an alert in if it is unacknowledged, due to the process that the system owners must follow. deleting the alert will bring it back in even if the device is disconnected, it seems there is a delay time for UDT to release the MAC address even after removal.

also if the device is no longer connected, such as a quick connect and they determine it does no good and they disconnect you can no longer get to the port and the device since it is no longer in the UDT_porttoendpoint current

An Unknown MAC address has been detected by Solar Winds User Device Tracker

${N=Alerting;M=AlertTriggerTime;F=DateTime}.

Rogue MAC-Address: ${N=SwisEntity;M=MACAddress}

Port: ${SQL: SELECT Name from [UDT_Port]

WHERE (PortID = (SELECT distinct asv.ActiveObject from UDT_PortToEndpointCurrent UDT, AlertStatusView ASV

WHERE UDT.EndpointID = ${N=SwisEntity;M=EndpointID}

and ASV.ActiveObject =${N=SwisEntity;M=EndpointID} and ASV.acknowledged = 0))}

IP: ${SQL: SELECT N.IP_Address FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

Port Name: ${SQL: SELECT PortDescription from [UDT_Port]

WHERE (PortID = (SELECT distinct asv.ActiveObject from

UDT_PortToEndpointCurrent UDT, AlertStatusView ASV

WHERE UDT.EndpointID = ${N=SwisEntity;M=EndpointID}

and ASV.ActiveObject =${N=SwisEntity;M=EndpointID} and ASV.acknowledged = 0))}

Device: ${SQL: SELECT N.Caption FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

See if this helps, either the code or the hotfix

rainrockadmin

I am getting a similiar error when I try to use the above code. I am on version 3.3.1 with Hot Fix 3. I am only trying to use the IP: section...

IP: MACRO SQL ERROR - Incorrect syntax near '{'.}

Littlebee_26

Upon checking, here are the alert variables that still work for a UDT 3.3:

IP: ${SQL: SELECT N.IP_Address FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}

Device: ${SQL: SELECT N.Caption FROM [dbo].[Nodes] N JOIN [dbo].[UDT_Port] UP ON (UP.NodeID = N.NodeID)

JOIN [dbo].[UDT_PortToEndpointCurrent] P2EC ON (P2EC.PortID = UP.PortID)

WHERE (P2EC.EndpointID = ${N=SwisEntity;M=EndpointID})}