3 Replies Latest reply on Nov 29, 2017 10:36 AM by m-milligan

    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)