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

Need to report the IP address and the Device name in the alert email.   Please post your suggestions on how to do this.

  • Ok, was looking for more details on why you're having problems in the other message chain.    In the other chain they were trying to alert on New MAC Address and were trying to do it using a custom query for the Alert condition and were unsuccessful.   In the example I posted I used the built in alert for "New MAC Address" and the queries you were having problems with worked fine for both me and alangidlewski‌.  

    So, since your's isn't working, please give details on what you're alerting on, and how you have it set up.   A screen shot might be nice to clear up confusion.

  • 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?  emoticons_silly.png   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.

    variable_insert.jpg

    HTH!!

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

    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.

    Capture.JPG

  • 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:

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

  • Yes, and you're running into exactly what I said, the MAC address being visible on multiple ports in UDT.   That's going to be a problem from what I can see.

    The problem is that you need to somehow limit the query to returning 1 value, the problem is - what value should it return?   The answer is usually fairly simple, the switchport that only has a single MAC address associated with it.  But, the first problem you run into is how to both figure out and return that specific value via an SQL query?  I can't think of a good answer to this.   Not to mention you would have other potential problems where, if for instance, some of your switchports had VoIP phones on them, and a PC connected to the phone.  That switchport would have 2 MAC addresses, admittedly only 1 you care about.  Or a wireless AP?   If you had 30 clients connected, that would be 30 MAC addresses on the one port, which looks a lot more like an uplink.   So...

    The only good solution I see to it is to unmanage your uplink ports in UDT.   That way this query should only return a single value and you wouldn't have to care about the # of MAC addresses on the given port.

  • All the thanks Craig for your Input and helping me sort this out.  This makes perfect sense.  A little history here...This whole issue has been months to get something close to our purpose for purchasing this product.  We bought solarwinds UDT for one reason only,  to see when a new device that we didnt know was plugged into the network.  A salesman pitched the idea a year ago that we can get email alerts that can properly alert Network Personnel via an email of the area.   I called SolarWinds Support and created a ticket to assist me with the best way to use the product to be alerted of a new device that plugged in.   They pointed me in the direction of using "Rogue Devices".  Once I started using it i realized that my technicians are not getting any tangible information to actually support an issue or just throw it in the trash as a false positive.  SolarWinds Support refererred me to your last post to fix my issue instead to get more information in my email, which takes me to where i am right now.   Now with knowing the purpose of my use of UDT.  Do you think we should be using the Rogue Devices Alert in this fashion.  So far just knowing where someone might have plugged something in and which IP address is being used is alot more information than the Alert gives you to begin with.  I just dont understand why more people dont need to know things for the Rogue Devices by default such as IP, Device Name, Port being used, etc.... i feel i have the wrong product for the situation.emoticons_angry.png