CODEWITHSUNDEEP
azure-application-insights
lmorreale
lmorreale

Reputation: 93

Application Insights query to get time between 2 custom events

I am trying to write a query that will get me the average time between 2 custom events, sorted by user session. I have added custom tracking events throughout this application and I want to query the time it takes the user from 'Setup' event to 'Process' event.

let allEvents=customEvents
| where timestamp between (datetime(2019-09-25T15:57:18.327Z)..datetime(2019-09-25T16:57:18.327Z))
| extend SourceType = 5;
let allPageViews=pageViews
| take 0;
let all = allEvents
| union allPageViews;
let step1 = materialize(all
| where name == "Setup" and SourceType == 5
| summarize arg_min(timestamp, *) by user_Id
| project user_Id, step1_time = timestamp);
let step2 = materialize(step1
| join
    hint.strategy=broadcast                      (all
    | where name == "Process" and SourceType == 5
    | project user_Id, step2_time=timestamp
)
on user_Id
| where step1_time < step2_time
| summarize arg_min(step2_time, *) by user_Id
| project user_Id, step1_time,step2_time);
let 1Id=step1_time;
let 2Id=step2_time;
1Id
| union 2Id
| summarize AverageTimeBetween=avg(step2_time - step1_time)
| project AverageTimeBetween

When I run this query it produces this error message:

'' operator: Failed to resolve table or column or scalar expression named 'step1_time'

I am relatively new to writing queries with AI and have not found many resources to assist with this problem. Thank you in advance for your help!

Upvotes: 7

Views: 25264

Answers (1)

John Gardner
John Gardner

Reputation: 25116

I'm not sure what the let 1id=step1_time lines are intended to do.

those lines are trying to declare a new value, but step1_time isn't a thing, it was a field in another query

i'm also not sure why you're doing that pageviews | take 0 and unioning it with events?

let allEvents=customEvents
| where timestamp between (datetime(2019-09-25T15:57:18.327Z)..datetime(2019-09-25T16:57:18.327Z))
| extend SourceType = 5;
let step1 = materialize(allEvents
| where name == "Setup" and SourceType == 5
| summarize arg_min(timestamp, *) by user_Id
| project user_Id, step1_time = timestamp);
let step2 = materialize(step1
| join
    hint.strategy=broadcast (allEvents
    | where name == "Process" and SourceType == 5
    | project user_Id, step2_time=timestamp
)
on user_Id
| where step1_time < step2_time
| summarize arg_min(step2_time, *) by user_Id
| project user_Id, step1_time,step2_time);
step2
| summarize AverageTimeBetween=avg(step2_time - step1_time)
| project AverageTimeBetween

if I remove the things I don't understand (like union with 0 pageviews, and the lets, I get a result, but I don't have your data so I had to use other values than "Setup" and "Process" so I don't know if it is what you expect?

you might want to look at the results of the step2 query without the summarize to just see what you're getting matches what you expect.

Upvotes: 4

Related Questions