13 Replies Latest reply on Jan 6, 2016 4:32 PM by Craig Norborg

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

    bladren

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

       

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

          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.

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

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

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

                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!!

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

                    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

                     

                     

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

                        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}

                        1 of 1 people found this helpful
                  • Re: UDT Emails - Alert me when a rogue MAC Address appears on the network - variables
                    Craig Norborg

                    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.

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

                        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.

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

                            The Rogue Device can do just that if I'm not wrong, but I don't use it much.   But, in order for it to function the way I think you want it to, you might have to do one more step and that would be to set up your "White List" in the UDT settings section of the Settings page.  What that does is it defines a set of rules of hosts that are ok, and you can have different criteria applied as to what is safe and what is not.   Things like the IP address they get, the hostname they get, or the MAC address they have. 

                             

                            In general at our company any MAC address that can be identified as things like "Apple", "Dell", "HP" or "IBM" would definitely be considered safe by us, while other MAC addresses would be suspicious - or "Rogue".   By default I believe everything is whitelisted in Orion, so nothing would trigger the "Rogue" alert.   You would have to set this up in order for this to start functioning.   It will probably take some time to tune it to get all the MAC ranges in that you want, but once done it should tell if you if any off-brand devices get put on your network.

                             

                            In all the companies I have worked for, I have never seen anyone have the desire to implement this.   That's probably why the alerts haven't been fleshed out much, just from lack of use.   Should they be using it?  Good question, lots of potential overhead for limited gain.  Even when I was managing high-security government networks we didn't try to alert on rogue devices.   Our security teams did record what MAC addresses came on the network, but I never heard of them using the information at all, it was more there for a "oh sh*t" moment when they had to figure out a security breach or something.  

                             

                            Part of the problem is the administration required to set it up at first, if you want to get really good security out of it instead of using MAC ranges you would really have to input the MAC address of every device that was permitted, not a fun thing.   With the ability to do MAC ranges its better, but you run the risk of permitting all Apple MAC addresses and having someone connect to your network and try to hack using an iPad or something.

                             

                            Then you also run into the problem where it is blindingly simple to "spoof" a MAC address.   The vast majority of people just use the BIA (burned-in address or built-in address) for the MAC address on their PC.  But you can have duplicate mac addresses since there are a limited # assigned to each vendor, so as part of the NIC setup you can change this BIA to something else, whatever you want.   It's as simple as going into the NIC properties, to the "Advanced" settings and in the case of an Intel NIC at least, edit the "Locally Administered Address" - shown below.   Couple that with the fact that all sorts of devices from printers to laptops have a label on the outside with the MAC address right on it and it makes it every easy for a hacker to find a valid MAC address for your network to use.

                             

                            bia.jpg

                             

                            Because of this, trying to implement security based on MAC addresses is kind of like digging a hole in dry sand one grain at a time.  Even when you make significant progress, the hole can fill itself in.   You go through a LOT of overhead to produce what is actually very little security.   Moving to something more solid like preventing access to both wired and/or wireless via 802.1X, which is the core of NAC (Network Access Control) solutions like Cisco Clean Access, is a much better solution.  Rather than trying to find rogue clients when they connect, instead deny them access before they can connect to your network.

                             

                            That being said, if you want to continue on your course, UDT is the only product I know of off-hand that can do something like this.    There are Unix based solutions to listen to arp's on a LAN and record all the MAC's that connect, but they tend to be even more overhead and not really an alerting solution.   I'm sure there are others out there, but I don't know of them off-hand...