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

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.

13 Replies
Highlighted

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

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.

0 Kudos
Highlighted
Level 8

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

0 Kudos
Highlighted

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

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.

variable_insert.jpg

HTH!!

0 Kudos
Highlighted
Level 8

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

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.

Capture.JPG

0 Kudos
Highlighted

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

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}

Highlighted
Level 8

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

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:

0 Kudos
Highlighted
Level 8

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

Correct Tables i mean

0 Kudos
Highlighted
Level 8

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

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

0 Kudos
Highlighted

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

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.

0 Kudos