Search code examples
eventsazure-application-insightstracetimespan

Calculate average Time span in Azure Application Insights for Trace or Events

I'm currently evaluating a use case in Azure Application Insights but I'm open to use any other framework of infrastructure that would fit best.

So basically I have a desktop application who logs some events or traces (I don't exactly know which one it should be). Examples of events (or traces?)

 |    timestamp     |     state    | user |
 ------------------------------------------
 | yyyy-mm-dd 12:00 |  is_at_home  | John |
 | yyyy-mm-dd 15:00 |  is_at_work  | John |
 | yyyy-mm-dd 18:00 |  is_outside  | John |

Users are considered to be in the last state received until new event comes.

I need to extract data to answer questions like this:

  • I want to see if the total duration John is at home is growing or going down.
  • I want to get in which states the users pass most time.
  • I want the average duration of the state "is_at_work". And if it's going down or up over time.

So, Can the application insights output this kind of analysis? If not, which architecture/platform should I use? I'm I using the right keywords to describe what I want?

Thank you

Solution

  • the ai/log analytics query language (kql) supports all kinds of things like that. the trick you'll have is getting your queries exactly right, here you'll have to figure out exactly what you need to do so that you calculate the times between rows as "state" changes.

    here's my first attempt:

    let fakeevents = datatable (timestamp: datetime, state: string, user: string ) [
 datetime(2021-08-02 12:00), "is_at_home" , "John" ,
 datetime(2021-08-02 15:00), "is_at_work" , "John", 
 datetime(2021-08-02 18:00), "is_outside" , "John", 
 datetime(2021-08-02 11:00), "is_at_home" , "Jim" ,
 datetime(2021-08-02 12:00), "is_at_work" , "Jim", 
 datetime(2021-08-02 13:00), "is_outside" , "Jim", 
 ];
 fakeevents | partition by user (
    order by user, timestamp desc | 
    extend duration = prev(timestamp, 1, now()) - timestamp
 )

    gets me:

    timestamp state user duration
    2021-08-02T18:00:00Z is_outside John 06:20:23.1748874
    2021-08-02T15:00:00Z is_at_work John 03:00:00
    2021-08-02T12:00:00Z is_at_home John 03:00:00
    2021-08-02T13:00:00Z is_outside Jim 11:25:14.6912472
    2021-08-02T12:00:00Z is_at_work Jim 01:00:00
    2021-08-02T11:00:00Z is_at_home Jim 01:00:00

    before you send any data real data, you can create "fake" data by using the datatable operator to make a fake table full of data.

    you can then apply things like summarize to calculate things like which had the max, etc. note the use of partition by user to make sure each user is treated separately. in my assumption i use now() if there's no value to end the duration of an event, you'll want to do something there otherwise you'll have blank cells.