cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

New mac address email alert variables

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
Highlighted
Level 10

Re: New mac address email alert variables

Jump to solution

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})}

View solution in original post

6 Replies
Highlighted
Level 13

Re: New mac address email alert variables

Jump to solution

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

0 Kudos
Highlighted
Level 7

Re: New mac address email alert variables

Jump to solution

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

Highlighted
Level 13

Re: New mac address email alert variables

Jump to solution

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)

Highlighted
Level 7

Re: New mac address email alert variables

Jump to solution

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

Highlighted

Re: New mac address email alert variables

Jump to solution

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 '{'.}

0 Kudos
Highlighted
Level 10

Re: New mac address email alert variables

Jump to solution

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})}

View solution in original post