locked
For the last hour (rolling window) show the aggregated number of requests RRS feed

  • Question

  • I am using event hub as input and powerbi as output.

    I have id, version, status, date in input. Date is in string. So i have converted it to date using dateadd(). It is working fine.

    I need  to create report- For the last hour (rolling window) show the aggregated number of requests for each HTTP status code 

    currently I'm using following query but it is not working.

    select
        status, version, id, count(id),
        dateadd(hour, 0, CONCAT(substring(respHdr.date, 8, 2), ' ', substring(respHdr.date, 13, 3), ' ', substring(respHdr.date, 19, 4), ' ', substring(respHdr.date, 26, 8))) as date
     INTO
        [powerbi-output]
    FROM
        [powerbi-input]
    group by version, id, respHdr.date, status, TumblingWindow(hour, 1)

    What would be the possible solutions to accomplish this?

    Thanks in advance.

    Monday, November 2, 2015 11:23 AM

Answers

  • Make sure you use TIMESTAMP BY clause when testing in the browser, otherwise all events get the same timestamp. Also you probably don't need to include respHdr.date as a grouping key - you can use System.Timestamp column to select window end time.

    Something like this. If you need more help, please provide example of your JSON data.

    select
         status, version, id, count(id),
         dateadd(hour, 0, CONCAT(substring(System.Timestamp, 8, 2), ' ', substring(System.Timestamp, 13, 3), ' ', substring(System.Timestamp, 19, 4), ' ', substring(System.Timestamp, 26, 8))) as date
      INTO
         [powerbi-output]
     FROM
         [powerbi-input] TIMESTAMP BY respHdr.date
     group by version, id, status, TumblingWindow(hour, 1)

    Tuesday, November 17, 2015 6:28 AM

All replies

  • Hi!

    When you say it is not working, what are you seeing or not seeing as it may be)?

    • Is data coming into the EventHub?
    • Are you getting any errors?
    • What happens if you pass the data through to PowerBI directly, i.e. no TumblingWindow, just to see that data is actually coming into PowerBI?

    Let us know, and perhaps we can help.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, November 3, 2015 3:49 AM
    • Yes data is coming from EventHub.
    • I'm not getting any errors. 
    • If I pass the data through to PowerBI directly, i.e. no TumblingWindow, data is coming in PowerBI

    Tuesday, November 3, 2015 5:32 AM
    • Yes data is coming from EventHub.
    • I'm not getting any errors. 
    • If I pass the data through to PowerBI directly, i.e. no TumblingWindow, data is coming in PowerBI

    OK, so stupid question; seeing that you have a TumblingWindow of 1 hour, have you waited an hour for the data. What would happen if you - for testing purposes - set the TumblingWindow size to something smaller, 1 minute, or 5 minutes or something like that?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, November 3, 2015 7:16 AM
  • Yes. But I think there is no need to wait for 1 hour, if i'm using TumblingWindow.
    Without tumblingwindow how can I show the aggregated number of requests for each HTTP status code for the last hour (rolling window)?
    Tuesday, November 3, 2015 9:35 AM
  • Hi!

    Have a look at following URL: https://msdn.microsoft.com/en-us/library/azure/dn835055.aspx

    It is my understanding that a tumbling window is "fixed" in the sense that it outputs after the window-size period. Just to test it out, change the time unit to minute, and see if you won't get a result.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, November 3, 2015 11:21 AM
  • Yes. But then how can I show the aggregated number of requests for each HTTP status code for the last hour (rolling window)? What will be the query?
    Tuesday, November 3, 2015 1:31 PM
  • OK, so what do you want to achieve? I.e., if during a two hour period, just as an example, you have following scenario

    • 12:01 - code 200
    • 12:10 - code 401
    • 12:15 - code xyz
    • 12:20 - code 200
    • 12:20 - code zyx
    • 12:45 - code 401
    • 12:50 - code xyz
    • 13:01 - code 401
    • 13:35 - code 200
    • 13:40 - code xyz
    • 13:43 - code 401
    • 13:55 - code 200
    • 13:58 - code zyx
    • 13:59 - code 200
    • 14:00 - code 401

    What would you like the output to be?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, November 3, 2015 5:17 PM
  • So for last 1 hour i.e 13:01 to 14:00, I have 8 number of requests. I need following output - 
    For code 401 - count is 3, code 200 - count is 3, code xyz - count is 1, code zyx - count is 1. 
    Wednesday, November 4, 2015 6:04 AM
  • So for last 1 hour i.e 13:01 to 14:00, I have 8 number of requests. I need following output - 
    For code 401 - count is 3, code 200 - count is 3, code xyz - count is 1, code zyx - count is 1. 

    Yes, and that is what you will get - isn't it. I.e at 14:00 there will be an output consisting of 401 - 3, 200 - 3, etc. Are you not receiving that?

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Wednesday, November 4, 2015 2:44 PM
  • Nope. It showing all the data. If you consider above example then it is giving output - 
    For code 401 - count is 5, code 200 - count is 5, code xyz - count is 3, code zyx - count is 2. :(
    Thursday, November 5, 2015 6:56 AM
  • Make sure you use TIMESTAMP BY clause when testing in the browser, otherwise all events get the same timestamp. Also you probably don't need to include respHdr.date as a grouping key - you can use System.Timestamp column to select window end time.

    Something like this. If you need more help, please provide example of your JSON data.

    select
         status, version, id, count(id),
         dateadd(hour, 0, CONCAT(substring(System.Timestamp, 8, 2), ' ', substring(System.Timestamp, 13, 3), ' ', substring(System.Timestamp, 19, 4), ' ', substring(System.Timestamp, 26, 8))) as date
      INTO
         [powerbi-output]
     FROM
         [powerbi-input] TIMESTAMP BY respHdr.date
     group by version, id, status, TumblingWindow(hour, 1)

    Tuesday, November 17, 2015 6:28 AM
  • Think i have the same problem, i only want to show the last value on my RT dashboard.

    Problem is that the PowerBI output is appending data. So when you create a gauge or text counter in powerbi it will allways sum, avg or other aggr over all the values in the dataset from PowerBI.

    Would be nice if someone could help us.

    Tuesday, November 24, 2015 7:31 PM
  • Found a solution:

    In PowerBI Dashboard ask a question (Q&A), for me it was something like:

    'Show Average CPU last 2 seconds'

    this will show a counter which you can pin to the dashboard and from there it will allways display in my case the Average CPU Utilization for the last 2 seconds.

    Tuesday, November 24, 2015 8:19 PM