Extracting Netflow 4.0 data

Version 3

    Initially I just wanted to be able to run batch SQL against the Solarwinds database and capture the data in a file in order to feed a Network Device Inventory system. 

     

    I found there is a command line utility to let you run batch SQL, sqlcmd.


    If you Google SqlCmdLnUtils.msi you will find links to the download from Microsoft.   The install put the executable I needed in

    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd"


    Next I had to get my SQL DBA to create DB credentials for me.  The credentials created during the Orion install wouldn’t work for what I wanted to do.  Not sure why but I never asked …


    Anyhow, I could now run my simple SQL in a batch file with something like:


    "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S <sql_server>  -U <Database_UserID>  -P <Database_password

         -i <SQL_query_filename> -o <output_file>  -s <field_seperator> -W (removes trailing spaces)


    and life was good.  Later some folk got interested in extracting some Netflow data.  My SQL batch worked great … until NTA 4.0!


    Now don’t get me wrong, there’s lots of cool stuff about NTA 4.0. 


    Your netflow data doesn’t get all rolled up into long time period intervals that smooths out all of the peaks.  Now you can keep the flows down to the 1 minute granular level for the entire period you keep the data.


    And moving it out of the SQL DB gives you a great performance boost for Orion.


    But that is also the bad thing … the data is no longer stored as SQL, it’s in a variant of a FastBit database. 


    There’s some interesting info about FastBit in this post:  http://thwack.solarwinds.com/message/228324#228324

     

    But now I needed a way to get Netflow data out of the new Flow Storage Database.  I knew there was an API, but I never got into using it. 


    But now I had to …


    Here's what I did:


    You will want to download the latest Orion SDK, at least 1.9.  Here’s a helpful link: http://thwack.solarwinds.com/thread/39001

     

    You have options on how to use the SWIS.  I looked at this Solarwinds lab video, http://thwack.solarwinds.com/docs/DOC-173676

    and opted for Powershell.


    I made a simple Powershell script from the examples in the …\SolarWinds\Orion SDK\Documentation\Orion SDK.pdf doc

     

    # --------------------------------------------------

    # You can run this at a command prompt with: c:> powershell -file c:\<whatev>\<your_PS_script.ps1

    # This sample runs a query that is stored as an external file

     

    Add-PSSnapin SwisSnapin      # makes the SWIS cmdlets available to PS

     

    # You can use Get_Credential to pop up a window asking for credentials

    # $creds  = Get-Credential 

     

    # or use this snippet of code to store credentials right in the script

    # to facilitate unattended batch processing

     

    $username = "<username>"

    $password = "<password>"

    $secstr = New-Object -TypeName System.Security.SecureString

    $password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}

    $creds = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr

     

    $swis = Connect-Swis -Credential $creds -Hostname <Orion_server> # create a SWIS connection object

     

    # Get the contents of the query file into a variable

    $query = Get-Content -path "C:\<whatev>\<your_query>.swql" | Out-String


    Get-SwisData $swis $query  # and run it

     

    # You can use other Powershell constructs to write out a csv file

    # Get-SwisData $swis $query | Export-Csv C:\<whatev>\your_output_file.csv

    # -----------------------------------------------------------

     

    The sample SWQL query:

     

    -- query top 10 protocols in the first hour of the month of June 2014

     

    SELECT TOP 10 f.Protocol.Name, SUM(f.Bytes) as TotalBytes FROM Orion.Netflow.Flows f

    WHERE f.TimeStamp > '2014-06-01 00:00:00' AND f.TimeStamp <= '2014-06-01 01:00:00'

    GROUP BY f.Protocol.Name

    ORDER BY SUM(f.Bytes) DESC

     

    The Orion SDK.pdf doc mentioned about has other examples and more information about SWQL.  The SDK also contains SWQL Studio, a tool that helps you determine what data you should extract and can help you build SWQL statements.  The Solarwinds lab video mentioned above is also very helpful in learning about these tools.


    Hope this helps!  Enjoy ...