UDT Emails - Alert me when a rogue MAC Address appears on the network - variables

My Current Configure Action: Send an Email/Page Message body is coded like this....

An Unknown MAC address has been detected by Solar Winds User Device Tracker ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

Rouge MAC-Address: ${N=SwisEntity;M=MACAddress}
Port: ${SQL: SELECT Name from [dbo].[UDT_Port] WHERE  (PortID = ${SQL: SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE  (EndpointID = ${N=SwisEntity;M=EndpointID})})}
IP: ${SQL: SELECT IP_Address FROM [dbo].[Nodes] WHERE (NodeID = ${N=SwisEntity;M=DeviceID})}
Port Name: ${SQL: SELECT PortDescription from [dbo].[UDT_Port] WHERE  (PortID = ${SQL: SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE  (EndpointID = ${N=SwisEntity;M=EndpointID})})}
Device: ${SQL: SELECT Caption FROM [dbo].[Nodes] WHERE (NodeID = ${N=SwisEntity;M=DeviceID})}

View full object details here: ${N=SwisEntity;M=DetailsUrl}.
View full alert details here: ${N=Alerting;M=AlertDetailsUrl}
Click here to acknowledge the alert: ${N=Alerting;M=AcknowledgeUrl

The output looks like this....

An Unknown MAC address has been detected by Solar Winds User Device Tracker Wednesday, December 9, 2015 4:43 PM.

Rouge MAC-Address: 00:04:00:EE:79:B7

Port: Gi0/4

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

Port Name:

Device: MACRO SQL ERROR - Incorrect syntax near '{'.

Ok, lets try this:

An Unknown MAC address has been detected by Solar Winds User Device Tracker ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

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

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

IP: ${SQL: SELECT IP_Address FROM [dbo].[NodesData] WHERE (NodeID = ${N=SwisEntity;M=DeviceID})}

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

Device: ${SQL: SELECT Caption FROM [dbo].[Nodes] WHERE (NodeID = ${N=SwisEntity;M=DeviceID})}

View full object details here: ${N=SwisEntity;M=DetailsUrl}.

View full alert details here: ${N=Alerting;M=AlertDetailsUrl}

Click here to acknowledge the alert: ${N=Alerting;M=AcknowledgeUrl}

That is assuming you want the MAC address to be rouge (red) rather than rogue?     Although this isn't a rogue MAC address technically either, just a new one.  Unless you're using the rogue MAC address alert in which case this answer could be wrong.

A bit of explanation is probably in order.   When doing custom SQL queries for values, there are some limitations.   You'll notice to the side of the "Subject" and "Message" boxes there is an "Insert Variable" box.  If you click on that, you can peruse the variables you can use when creating a custom SQL query, or by checking the "Define SQL Variable (Advanced)" at the bottom of this box.    By default it shows variables for "Global" which can be used, but are usually not that useful.  If you change the dropdown to "New MAC Address", which is what this alert is, it shows you the variables that are not only available, but are most likely useful, for this "New MAC address" alert.  

Now, if you're there (or look below at my screenshot), you'll notice there is no EndpointID (which would equate to ${N=SwisEntity;M=EndpointID}), only PortID.  But, in you're queries you were looking for PortID anyway, you were just trying to leverage the (non-existent) EndpointID variable to get the PortID variable.  So in this case, by substituting the PortID for the subquery you had that used EndPointID we should get the desired result.  At least it seemed to work for me in my test.

HTH!!

My output with the code you suggested does this .....  Thanks for looking into the the "Rouge" coding.......

Message:

Rogue MAC-Address: 00:04:00:EE:79:B7

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

View full object details here: http://FBGUDT:8080/Orion/View.aspx?NetObject=UE-MAC:VAL=00:04:00:EE:79:B7.
View full alert details here:
Click here to acknowledge the alert:

Also I am using the "Rogue MAC Address" alert.

My output with the code you suggested does this .....  Thanks for looking into the the "Rouge" coding.......

Message:

Rogue MAC-Address: 00:04:00:EE:79:B7

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

View full object details here: http://FBGUDT:8080/Orion/View.aspx?NetObject=UE-MAC:VAL=00:04:00:EE:79:B7.
View full alert details here:
Click here to acknowledge the alert:

Also I am using the "Rogue MAC Address" alert.

Ok, very important info that..   Now the Rogue MAC Address is much trickier in that it's not so easy to get to the Nodes table.  Couple that with you trying to nest SQL "escapes" or defined SQL variables within other SQL "escapes" and I think you had a couple different problems going on.

So, first off, stop trying to do the ${SQL: SELECT ... ${SQL: SELECT }}, ie: putting one SQL escape inside another.   There is no reason for it.  Think of it this way, you've already "dropped down" into SQL, why do you need to do it again?   Or better yet, while ${SQL: SELECT} gets you into SQL from within the Orion Email message, that same construct doesn't work from within SQL - which you're effectively already in.

So, once that's figured out, its just a matter of figuring out what you have to work with.    Getting the "Port" becomes quite easy as you can see, I kind of kept it the same way you did, doing an embedded query within the WHERE clause, but without trying to do the escape sequence again.   Quite simple.

However, there is no "DeviceID" variable from the "Rogue MAC" alert.  The only thing you get is the "EndpointID", so from there its a matter of figuring out how to structure your query to get to the Nodes view (or NodesData table, whichever you prefer).   I choose to join the Nodes view with the UDT_Port and UDT_PortToEndpointCurrent table as shown below.  

Now, one other thing to remember.   These queries expect to have ONE value returned.   I'm not completely certain that this will be the case!!   A MAC address can potentially show up on multiple switches and ports (ie: uplinks and such), so its very possible this might still break.

But, that's a whole other complication.   If you've monitored the correct ports in UDT (ie: not uplinks), I'm guessing this has a good chance of working.  If you just blindly monitor all ports, probably less of a chance.   This would be inclusive of access points too.  ie: if you monitor an AP in UDT AND you monitor the switchport link to the AP, I'm guessing the MAC will show up on two ports.    I could be wrong on this too, just a theory...

But, here you go...

An Unknown MAC address has been detected by Solar Winds User Device Tracker ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

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

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

View full object details here: ${N=SwisEntity;M=DetailsUrl}.

View full alert details here: ${N=Alerting;M=AlertDetailsUrl}

Click here to acknowledge the alert: ${N=Alerting;M=AcknowledgeUrl}

Thanks looks like we are getting somewhere now with the correct databases for the information.   This is my output with the code you suggested

Message:

An Unknown MAC address has been detected by Solar Winds User Device Tracker Wednesday, December 9, 2015 4:43 PM.

Rouge MAC-Address: 00:04:63:5E:C4:67

Port: MACRO SQL ERROR - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

IP: 10.8.12.209

Port Name: MACRO SQL ERROR - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Device: OperationsSwitch2960-24Port

View full object details here: http://FBGUDT:8080/Orion/View.aspx?NetObject=UE-MAC:VAL=00:04:63:5E:C4:67.

View full alert details here:

Click here to acknowledge the alert:

Correct Tables i mean

Oh wait that Device is not the Devices name but the switch its connected to...  But still helpful information