10 Replies Latest reply on May 31, 2013 2:50 PM by michalB

    Mac detection

    Dentifrice

      Hi,

       

      simple question, is it possible for UDT to give you an alert when there are more thant X Mac address on a port ?

       

      For example, we want to be alerted when someone connect a small hub/switch at his desk without persmission. For several reasons, right now we can't implement port security. So if UDT could send an alert when some ports have more than one mac address, that would be great.

       

      Thank you

        • Re: Mac detection
          michalB

          You could create a custom SQL alert that would be triggered anytime there was a port with more MAC addresses, but you would not get any details about what happened, as this is currently not supported. You could, however, create a report that would list you all the ports with multiple MAC addresses. And perhaps, if you got that alert with no details, you could run the report to see them.

           

          This is SQL query you can use to trigger an alert when there is at least one port with more than one connections:

           

          select PortID, Count(EndpointId) as PortCount from UDT_PortToEndpoint

          group by PortID

          having Count(EndpointId) >1

           

          This is SQL query for Advanced SQL report that shows Node name, Port Name, and MAC Address (along with their IDs you can hide and use for mapping the Web URL in Report Designer):

           

          SELECT DISTINCT

                Nodes.NodeID

                ,Nodes.Caption

                ,UDT_Port.Name as PortName

                ,UDT_PortToEndpoint.EndpointID

                ,UDT_Endpoint.MACAddress

                ,UDT_Port.PortID as PortID

            FROM UDT_PortToEndpointCounts

            join UDT_PortToEndpoint on UDT_PortToEndpoint.PortID = UDT_PortToEndpointCounts.PortID

            join UDT_Port on UDT_Port.PortID = UDT_PortToEndpoint.PortID

            join UDT_Endpoint on UDT_Endpoint.EndpointID = UDT_PortToEndpoint.EndpointID

            join Nodes on UDT_Port.NodeID = Nodes.NodeID

            Where (UDT_PortToEndpointCounts.EndpointCount > 1

            and  UDT_PortToEndpoint.ConnectionType = 1)

            Order by Nodes.Caption, UDT_Port.Name desc

           

           

          Please note that the query above filters to direct connections only. If you do not want to use the filter, remove "and  UDT_PortToEndpoint.ConnectionType = 1" from the query.

            • Re: Mac detection
              Dentifrice

              Thanks ! That is great.

               

              I'm no SQL expert...this works great and I have customized it a little to fit my needs but I'm trying to change something else. Instead of getting all MAC addresses from a port, is it possible to just have the sum ? I'm talking about the second query.

               

              So, for example, instead of getting 5 MAC addresses from a port, I would like to show "4".

               

              thanks

                • Re: Mac detection
                  michalB

                  How about this one?

                   

                  SELECT Distinct

                        Nodes.NodeID

                        ,Nodes.Caption

                        ,UDT_Port.Name as PortName

                        ,UDT_Port.PortID as PortID

                        ,UDT_PortToEndpointCounts.EndpointCount

                    FROM UDT_PortToEndpointCounts

                    join UDT_PortToEndpoint on UDT_PortToEndpoint.PortID = UDT_PortToEndpointCounts.PortID

                    join UDT_Port on UDT_Port.PortID = UDT_PortToEndpoint.PortID

                    join UDT_Endpoint on UDT_Endpoint.EndpointID = UDT_PortToEndpoint.EndpointID

                    join Nodes on UDT_Port.NodeID = Nodes.NodeID

                    Where (UDT_PortToEndpointCounts.EndpointCount > 1

                    and

                    UDT_PortToEndpoint.ConnectionType = 1)

                    Order by UDT_PortToEndpointCounts.EndpointCount desc

                    • Re: Mac detection
                      Dentifrice

                      thanks again !

                       

                      Now it's better but there is one small problem to solve yet. I don't know why but there are some trunk port in the ouput.

                       

                      If I remove "UDT_PortToEndpoint.ConnectionType = 1" from the "where", I see all trunk ports in the listing (which I suppose it's normal ?). If I put it back, most of them don't show up but there are still about 10 trunk ports that show up. All others ports seems to be normal output.

                       

                      Do you have an idea what can cause this ?

                       

                      thanks

                        • Re: Mac detection
                          michalB

                          I am not sure, but if the port type is determined correctly by UDT, then you could filter them out in the where condition as well. Ethernet ports have type 6, trunk ports have 54 (I think, check the values in UDT_Port.PortType column).

                          So my query without the ports with type "54" looks like this (the text I added is in bold):

                           

                          SELECT Distinct

                                Nodes.NodeID

                                ,Nodes.Caption

                                ,UDT_Port.Name as PortName

                                ,UDT_Port.PortID as PortID

                                ,UDT_Port.PortType

                                , UDT_Port.PortIfDescr

                                ,UDT_PortToEndpointCounts.EndpointCount

                            FROM UDT_PortToEndpointCounts

                            join UDT_PortToEndpoint on UDT_PortToEndpoint.PortID = UDT_PortToEndpointCounts.PortID

                            join UDT_Port on UDT_Port.PortID = UDT_PortToEndpoint.PortID

                            join UDT_Endpoint on UDT_Endpoint.EndpointID = UDT_PortToEndpoint.EndpointID

                            join Nodes on UDT_Port.NodeID = Nodes.NodeID

                            Where (UDT_PortToEndpointCounts.EndpointCount > 1

                            and

                            UDT_PortToEndpoint.ConnectionType = 1

                          and

                            UDT_Port.PortType <> 54)

                            Order by UDT_PortToEndpointCounts.EndpointCount desc