5 Replies Latest reply on Nov 5, 2018 10:13 AM by rainrockadmin

    New mac address email alert variables

    sh00ter0034

      Hello,

       

      I found this thread: UDT Emails - Alert me when a rogue MAC Address appears on the network - variables  which is exactly what I want to do.  I followed Craig Norborg's suggestion but am getting  but get the error below

       

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

       

      Any help would be much appreciated, im not that familiar with SQL

        • Re: New mac address email alert variables
          m-milligan

          Can you paste the code from your alert in this thread?

            • Re: New mac address email alert variables
              sh00ter0034

              Hi, here it is:

               

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

               

              Thanks for the input/help

                • Re: New mac address email alert variables
                  m-milligan

                  Those look correct. Can you open Database Manager on your Solarwinds server and run the queries below? In each query, replace ENDPOINT_ID with a valid Endpoint ID from your system. Do the queries run, or do they produce an error?

                   

                  -- Port query

                  SELECT Name from [dbo].[UDT_Port] WHERE  (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE  (EndpointID = ENDPOINT_ID)))

                   

                  -- IP query

                  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 = ENDPOINT_ID)

                   

                  -- Port Name query

                  SELECT PortDescription from [dbo].[UDT_Port] WHERE  (PortID = (SELECT PortID from [dbo].[UDT_PortToEndpointCurrent] WHERE  (EndpointID = ENDPOINT_ID)))

                   

                  -- Device query

                  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 = ENDPOINT_ID)

              • Re: New mac address email alert variables
                jmfig

                sh00ter0034,

                 

                We just battled through some of this yesterday and found that the alert parser needs to be updated to the UDT to hotfix 3 and the parser started to behave

                 

                here is the code we used, it is a bit different, because I want to only get an alert in if it is unacknowledged, due to the process that the system owners must follow.  deleting the alert will bring it back in even if the device is disconnected, it seems there is a delay time for UDT to release the MAC address even after removal.

                 

                also if the device is no longer connected, such as a quick connect and they determine it does no good and they disconnect you can no longer get to the port and the device since it is no longer in the UDT_porttoendpoint current

                 

                An Unknown MAC address has been detected by Solar Winds User Device Tracker

                 

                ${N=Alerting;M=AlertTriggerTime;F=DateTime}.

                 

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

                 

                Port: ${SQL: SELECT Name from [UDT_Port]

                WHERE  (PortID = (SELECT distinct asv.ActiveObject from UDT_PortToEndpointCurrent UDT, AlertStatusView ASV

                WHERE UDT.EndpointID = ${N=SwisEntity;M=EndpointID}

                and ASV.ActiveObject =${N=SwisEntity;M=EndpointID} and ASV.acknowledged = 0))}

                 

                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 [UDT_Port]

                WHERE  (PortID = (SELECT distinct asv.ActiveObject from

                UDT_PortToEndpointCurrent UDT, AlertStatusView ASV

                WHERE UDT.EndpointID = ${N=SwisEntity;M=EndpointID}

                and ASV.ActiveObject =${N=SwisEntity;M=EndpointID} and ASV.acknowledged = 0))}

                 

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

                 

                See if this helps, either the code or the hotfix