2 Replies Latest reply on Mar 5, 2014 6:23 PM by doogie8797

    Bulk update NTA App to protocol mapping

    johnny ringo

      Hello!

       

      I am trying to figure out the quickest and simplest way to update NTA's Application Name to TCP/IP Port numbers in dbo.NetFlowApps.  I have about 20-30 additional entries I would like to add.  If I modify the DB table directly will NTA automatically detect the change and characterize new flows appropriately?  Should I accomplish this through settings in the SolarWinds website?  Any help would be appreciated!

       

      thanks!

        • Re: Bulk update NTA App to protocol mapping
          Lawrence Garvin

          The simplest and quickest, and certainly most reliable way, would be to use the SDK, probably in conjunction with PowerShell, but you could also use VB, C#, or Java.

           

          The SolarWinds Information Service (SWIS) is fully documented in the SDK, and would definitely be a safer way to push data into the database than directly inserting/updating to the tables.

          SWIS will make sure that the right data gets in the right places.

           

          Also, of note, on November 13th, we'll be presenting a SolarWinds Lab episode specifically about the SDK, with some practical examples of how it can be used.

          For more information and to sign up to get a reminder about SolarWinds Lab episodes, visit us at http://lab.solarwinds.com.

          • Re: Bulk update NTA App to protocol mapping
            doogie8797

            Here is a SQL script that may we have used.  Assumptions are documented in the script.  Apply the script to the Solarwinds Orion NPM database.

             

            --*************************************************************
            --script to insert new port values into NetFlowApps table
            --if port value exists, just change name
            --if port value doesn't exist, create it with new app name
            --assume both udp and tcp transport for each entry
            --works for individual ports, not multi-port ranges
            --*************************************************************

            declare @startPort int
            declare @endPort int
            declare @current int
            declare @myappname nvarchar(50)

            set @startPort = 0
            set @endPort = 999999
            set @current = @startPort

            while @current <= @endPort begin

            --*****
            --*****  Add as many new port mapping entries as needed
            --*****  Can call it dual-use appnames if desired,
            --*****  Can include port# in appname if desired
            --*****

            if      (@current=69) SET @myappname = 'Trivial File Transfer'
            else if (@current=81) SET @myappname = 'MyCustomApp'
            else if (@current=519) SET @myappname = 'utime_port519'
            else if (@current=514) SET @myappname = 'syslog'
            else if (@current=554) SET @myappname = 'rtsp'
            else if (@current=1038) SET @myappname = 'mtqp'
            else if (@current=1720) SET @myappname = 'Q.931_Callsetup_h323gatestat'
            else if (@current=1775) SET @myappname = 'MyCustomApp_port1775'
            else if (@current=1789) SET @myappname = 'Hello_Or_MyCustomApp'

            else   set @myappname = ''  

            --*****
            --*****  For any of the above port mappings, if an entry already exists for that port in NetFlowApps,
            --*****  change the appname to the new one above, and enable monitoring of that port.
            --*****  Otherwise, create new entries in NetFlowApps and NetFlowAppPorts tables
            --*****

            if (len(@myappname) > 0)
            begin
              if (not exists (select 1 from NetFlowApps where Mapto = @current)) begin
               insert into NetFlowApps (mapto,tcp,udp,appname,multiport,enabled)select @current,1,1,@myappname,0,1 
               insert into NetFlowAppPorts (AppID, Port,Direction) select (select appid from NetFlowApps where mapto = @current), @current,3
               end
              else update NetFlowApps SET appname = @myappname, enabled = 1 where Mapto = @current
            end
            set @current = @current + 1
            end


            --*************************************************************
            --*****  list and observe results to help you verify correctness
            --*****
            select * from netflowapps
            order by appid
            select * from netflowappports
            order by appid

            --*************************************************************
            --*****  reset SW DB back to default apps
            --*****
            --GO
            --DECLARE @return_value int
            --EXEC @return_value = [dbo].[swsp_NetflowSetDefaultApps]
            --SELECT 'Return Value' = @return_value
            --GO