29 Replies Latest reply on Dec 5, 2007 9:27 AM by Debbi

    Expand Application and Service Ports table

    joesim123

      Recently, I tried to use Solarwinds Netflow to track down a rogue application on my network.


      I knew the rogue application was running in a high range of ports....but those fifty or so ports in the range were not defined in the Application and Service Ports table.


      When I drilled into the conversations, Netflow just listed all those undefined port ranges as "Unmonitored Traffic(-1)".


      Would it be possible to create a hotfix file that lists every single port number up to 65535....and at least list the applications name as being the port number... if no pre-defined name exists.


      Example:  port 48000 would be named application "48000"


      That way we could use the search tool to find ANY port in use and when drilling into a Netflow conversation, we could see the port # in use instead of seeing "Unmonitored Traffic(-1)".  That would really be great.

        • Re: Expand Application and Service Ports table
          denny.lecompte

          You can do this today.  You need to write a SQL Script to update the Applications and Service Ports.  For all of the unmonitored ports, name it with port number.  You'll be monitoring 65k+ ports. One consequence of this strategy is that you will not discard any traffic, which may impact your storage requirements. 
           

            • Re: Expand Application and Service Ports table
              joesim123

              I can already see all the conversations between hosts using these unknown ports, so I don't think its discarding them now.  Its just that it marks the port numbers as unknown. 


              Otherwise all the "other" data about the conversation is complete...ie hosts, bytes, times, etc...just no port numbers.


              Maybe someone can post a how-to on the SQL Script to update the applications and Service ports.


               PS...Thanks for your hard work on the Netflow App.

              • Re: Expand Application and Service Ports table
                easpowell

                 Hi,

                 "



                You need to write a SQL Script to update the Applications and Service Ports.  For all of the unmonitored ports, name it with port number."

                I would also like to implement this.  Could you provide a sample SQL Script that we could use to do this?  

                 

                Thanks for your assistance.

                 

                -EP 

                  • Re: Expand Application and Service Ports table
                    easpowell

                    Ok, 

                     

                    I figured out this much....  you need to run this command to update the ports and port names in the database. 

                    Insert INTO ServicePorts (Port,ServiceName,Enabled,System)

                    VALUES (33333,'Port 33333',1,1)

                    Is this the correct syntax?????

                    My problem is that I dont know how to create a Script to increment this number by one until 65,535 and also copy that number to the port name field.  

                    I understand that this isnt something Solarwinds Officially would support, but a nudge in the right direction would really help. I promise that I will only use my powers for good.    

                    It would be great to have this as an add-on app on the server to allow you to manipulate the netflow service ports.  The website is a little toooo cumbersome for some of this admin stuff and the java applets are a little squirelly in IE. (ie: sometimes the add service port windows disappears in the middle of typing something in the field.)   Would this be possible?  We use neftlow as first line of defense for our security and seeing odd ports showing up is a great indicator that something unusual is going on. 

                    -EP

                      • Re: Expand Application and Service Ports table
                        josh.clark

                         Here is a SQL script you can use.  Right now it will walk through every port from 1-65535 and create a new entry if one does not already exisit.  The Service Name will be "Port xyz" where xyz is the port number.

                        To use, just copy the sql below (between the dashed lines) into SQL Studio or Database Manager and excute it against your NTA database.

                         If you only want to create entries for a specific range of ports, you can change the values for @startPort and @endPort to suite your needs.

                        ---------------------------------------------------------------- 

                        declare @startPort int
                        declare @endPort int
                        declare @current int

                        set @startPort = 1
                        set @endPort = 65535

                        set @current = @startPort

                        while @current <= @endPort
                        begin
                            if not exists (select 1 from ServicePorts where Port = @current)
                            begin
                                insert into ServicePorts (Port, ServiceName, Enabled, System)
                                    select @current, 'Port ' + cast( @current as varchar(32)), 1, 0
                            end

                            set @current = @current + 1
                        end

                        ----------------------------------------------------------------
                         

                          • Re: Expand Application and Service Ports table
                            easpowell

                            That worked. Thanks a bunch!


                             


                            -EP

                            • Re: Expand Application and Service Ports table

                              I ran the script and it appears to have updated the list of ports available for netflow to report on, but I'm still seeing a vast majority of traffic labeled as unmonitored. I tried restarting both the netflow and the NNM services to no avail. Any ideas on what I'm missing? 

                              • Re: Expand Application and Service Ports table
                                BryanBecker


                                 Here is a SQL script you can use.  Right now it will walk through every port from 1-65535 and create a new entry if one does not already exisit.  The Service Name will be "Port xyz" where xyz is the port number.

                                To use, just copy the sql below (between the dashed lines) into SQL Studio or Database Manager and excute it against your NTA database.

                                 If you only want to create entries for a specific range of ports, you can change the values for @startPort and @endPort to suite your needs.

                                ---------------------------------------------------------------- 

                                declare @startPort int
                                declare @endPort int
                                declare @current int

                                set @startPort = 1
                                set @endPort = 65535

                                set @current = @startPort

                                while @current <= @endPort
                                begin
                                    if not exists (select 1 from ServicePorts where Port = @current)
                                    begin
                                        insert into ServicePorts (Port, ServiceName, Enabled, System)
                                            select @current, 'Port ' + cast( @current as varchar(32)), 1, 0
                                    end

                                    set @current = @current + 1
                                end

                                ----------------------------------------------------------------
                                 

                                 

                                 

                                 

                                Josh...can you step thru how to apply that?  There are several tables in the db and I'm no db expert so I just don't know where to copy/paste this.  Thanks.

                                BB

                                  • Re: Expand Application and Service Ports table

                                    Bryan,

                                    1. Copy the section between the dashes into the clipboard. 
                                    2. Open Database Manager that ships with Orion and connect to your Orion database.
                                    3. Select the ServicePorts table, right click and select New Query
                                    4. Paste the script from the clipboard into the query field (overwrite the default query)
                                    5. Change the radio button above the query to allow read-write access to the database
                                    6. Execute the query
                                      • Re: Expand Application and Service Ports table
                                        BryanBecker

                                         Ok...did what you said and it looks like it took but I still have alot of "unmonitored" ports.  I did noticed in the SQL query that some ports did not have the enable checkbox checked.

                                        BB

                                          • Re: Expand Application and Service Ports table
                                            SamuelB


                                             Ok...did what you said and it looks like it took but I still have alot of "unmonitored" ports.  I did noticed in the SQL query that some ports did not have the enable checkbox checked.

                                             

                                            I'm no SQL expert but this query worked for me to enable each port that exists in the table.

                                            update ServicePorts set Enabled = 'True'  where Enabled = 'False'

                                             
                                            EDIT: I realized that you may have been talking about the "System" checkboxes. You could change them in much the same way but I am not sure what the purpose of that checkbox is.
                                             

                                • Re: Expand Application and Service Ports table
                                  daxvancamp


                                  You can do this today.  You need to write a SQL Script to update the Applications and Service Ports.  For all of the unmonitored ports, name it with port number.  You'll be monitoring 65k+ ports. One consequence of this strategy is that you will not discard any traffic, which may impact your storage requirements. 
                                   

                                   



                                   

                                  What will the impact be ? I also would like to add more ports for applications we run, for example SAP runs int he range 3200-3500.

                                  I have major differences in total TCP traffic in Orion and the WAN optimization appliance, and it's possible that's because I don't see ALL traffic.

                                • Re: Expand Application and Service Ports table

                                  I think there may be a bug in here.  After adding all the ports using the script above, the unmonitored traffic did go away.


                                  By the way, support had told me yesterday that if you wrote to the Database while the service was running, that it would not get applied until a restart or stop/start of the service. So I stopped the services, ran the script, and started the services back up.  I saw the "unmonitored" traffic disappear almost immediately.


                                  Here is my point on the bug.  The "unmonitored" traffic went away BUT it was not seen as one of the new numerical ports that I added???


                                  That means it is being seen on a port that should have already been seeing it. 


                                   Seems odd to me?   Any thoughts??????