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 Reply Children
  • 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

  • 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)