Netflow Custom reports

Hi,

I am looking for building a custom report that provides me the below information from the netflow i am collecting on my Cisco ASA:

1. Transmitter Full Hostname
2. Receiver Full Hostname
3. Application Port Number
4. Total Bytes (Kbytes)

I try to get help from support, but they came back saying that they don't support custom reports. So i think any one of you can help me in building a SQL query to get this info. I am not a SQL guy so couldn't get this done that easy.

Any help will be appreciated.


Thanks

Kalyan 

Parents
  • Hi Kalyan,

    in NTA 3.7 (which should be available very soon - see announcements at www.solarwinds.com ) we've added "Top 100 Conversations including applications" report. It doesn't have hostnames, but have IP addresses instead. 

    You can use this report and using Report Writer add Filter condition by Node vendor or type.

    Also, please notice that ASA export total amount of bytes per session. It's not possible to show Rx/Tx bytes separately.

    thanks

  • Hi GZytar

    what about if I would like to have the same kind of report as Top 100 Conversation with application but instead of IP, I would like to use Hostnames. IP address is only useful to Engineeers

  • Hello danielk,

    Please try attached customized report and let me know any issues with it.


    Regards    radekn

  • Hi Radekn

    it is working great. Thanks alot.

    One other question. How easy to create the same report but base on custom period? A period which we can key in manually?

    Happy New Year

  • Hi radekn--

    Thank you for sharing this with the community!  I would suggest uploading your customized report to the Content Exchange area as well,

    Thanks again!
    DH

  • how about a Netflow report that shows the top ten applications and their transfer rate for a day?

  • Hi danielk,

    To customize time period open custom reports in reportwriter.

    Show SQL content of report in report writer by click on SQL tab (reportwriter should be in design mode).

    Find "WHERE" and "GROUP BY" and replace content between these two words with time conditions.

    Here are some examples

    for last 7 days:

    DateTime>=GETDATE() -7 

    AND DateTime <=GETDATE()

    for this month( actual month):

    DateTime>= CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)

    AND DateTime<= CAST((FLOOR((CAST(GETDATE() AS float)+5e-6)))  AS smalldatetime)

     

    for last month(whole previous month):

    DateTime>= DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

    AND DateTime<= DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

     

    for custom date ( in format YYYY-MM-DD HH:MI:SS):

    DateTime>= CONVERT (datetime,'2011-12-13 23:59:00' , 120)

    AND DateTime<= CONVERT (datetime,'2012-1-2 23:59:00' , 120)

     

    let me know if it helps.

    Regards radekn

Reply
  • Hi danielk,

    To customize time period open custom reports in reportwriter.

    Show SQL content of report in report writer by click on SQL tab (reportwriter should be in design mode).

    Find "WHERE" and "GROUP BY" and replace content between these two words with time conditions.

    Here are some examples

    for last 7 days:

    DateTime>=GETDATE() -7 

    AND DateTime <=GETDATE()

    for this month( actual month):

    DateTime>= CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)

    AND DateTime<= CAST((FLOOR((CAST(GETDATE() AS float)+5e-6)))  AS smalldatetime)

     

    for last month(whole previous month):

    DateTime>= DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

    AND DateTime<= DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

     

    for custom date ( in format YYYY-MM-DD HH:MI:SS):

    DateTime>= CONVERT (datetime,'2011-12-13 23:59:00' , 120)

    AND DateTime<= CONVERT (datetime,'2012-1-2 23:59:00' , 120)

     

    let me know if it helps.

    Regards radekn

Children
  • Guys!

    I Really need this report,

    Please help emoticons_sad.png

  • Hi

    Thanks for the sample SQL query, but to make sure that this sample can be used for my need;

    if I need to filter the NTA history data only for a time period e.g. from previous month data starting from '2020-06-08 to 2020-07-08' (YYYY-MM-DD) but traffic between morning 07:00:00 to evening 16:59:00 only, will the below query will work?

    Appreciate your help on this.

    for last month(whole previous month):

    DateTime>= DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

    AND DateTime<= DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

    for custom date ( in format YYYY-MM-DD HH:MI:SS):

    DateTime>= CONVERT (datetime,'2011-12-13 23:59:00' , 120)

    AND DateTime<= CONVERT (datetime,'2012-1-2 23:59:00' , 120)