6 Replies Latest reply on Nov 29, 2007 12:24 PM by Debbi

    Netflow Report on botnet attack needed!


      I need to write a report on what source IP addresses have accessed (or been accessed by) 5 specific known botnet addresses in the last 30 days so we can clean the PCs.  Though I am good at writing reports using Report Writer, I cannot get this one to show:


      Either the source or destination should be one of the botnet addresses.  Sounds like the ideal simple report request, but it's stumping me. I am happy to do 5 separate reports if needed, one for each address.

      If this can only be done in a direct SQL query, please let me know: 1. Why? and 2. How to write the query.  I can only query using SQL Enterprise Manager, not the db manager that comes with Orion (permissions issue).



        • Re: Netflow Report on botnet attack needed!

          I would imagine that this is one of the primary reasons that an organization would want to use a netflow collector... when we get a report that certain IPs on the internet are implicated in attacks on our network, and can plant trojans, we would want to see which of our hosts have been communicating with it over the last month to find those infected (conversations).  Another use might be to monitor what destinations a particular host on our network has been communicating with in a list form.  If you choose a month time frame in the web interface, it seems to be just too much data to pull up.  Perhaps I am missing something obvious in setting up this report (or query).  If no one knows I will open a support ticket.  Thanks.  -Debbi

            • Re: Netflow Report on botnet attack needed!

              OK, so I really need you guys.  Solarwinds techies do not write reports for customers.  If you are skilled at SQL queries have an idea of how to do this, I would appreciate the info.  Thanks!  -Debbi

                • Re: Netflow Report on botnet attack needed!

                  I was looking at it. It won't be pretty but you can try modifying this to fit your need. You might have to run int on the NetFlowSummary1&2 tables. The majority of my data is in Summary2. The addresses do not show up formatted, but rather as 11 or 12 digit numbers.

                  The SourceIPSort would be the IP address you want to find. After that are the day before and day after the ones you are looking for.

                  It isn't pretty, but it should give you what you are looking for. I'm just beginning to get into Sequel myself because of the NTA module.

                  I was able to paste this into the report writer and get it to run there.

                  SELECT     *, NodeID AS Expr1, SourceIPSort AS Expr2, SourcePort AS Expr3, DestIPSort AS Expr4, DestPort AS Expr5, StartTime AS Expr6
                  FROM         NetFlowSummary2
                  WHERE     (SourceIPSort = '10100144250') AND (StartTime > CONVERT(DATETIME, '2007-10-30 00:00:00', 102)) AND (StartTime < CONVERT(DATETIME,
                                        '2007-11-01 00:00:00', 102))
                  ORDER BY DestIPSort

                  • Re: Netflow Report on botnet attack needed!

                    The one challenge with your report is that you're looking for something very specific within a large amount of data. The database isn't optimized for that kind of query, so you may encounter some timeouts.  If that happens, you would need to run a series of reports across a smaller time frame than 30 days.