CODEWITHSUNDEEP
maxazure-log-analyticskql
Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61509

How to use max(timestamp) with summarize when using other aggregated columns (count()) in kusto (kql)?

I am trying to write a query in kusto (kql), where I would check which users how many times accessed what projects.

pageViews
| extend projectId = extract(@"/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})", 1, url)
| where url matches regex @'/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})' 
and timestamp > ago(90d)
and isnotempty(user_Id)
| project timestamp, user_AccountId, user_Id, url, projectId
| summarize projectRequests = count() by  user_Id, projectId

I need to extend this functionality by when was the last access

I tried:

pageViews
| extend projectId = extract(@"/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})", 1, url)
| where url matches regex @'/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})' 
and timestamp > ago(90d)
and isnotempty(user_Id)
| project timestamp, user_AccountId, user_Id, url, projectId
| summarize projectRequests = count() by  user_Id, projectId, LastEdit = max(timestamp)

but it gave me

Function 'max' cannot be invoked in current context

How could I extend this query to include when was the project accessed last?

Upvotes: 0

Views: 2014

Answers (2)

Slavik N
Slavik N

Reputation: 5328

You should replace:

| summarize projectRequests = count() by user_Id, projectId, LastEdit = max(timestamp)

with

| summarize projectRequests = count(), LastEdit = max(timestamp) by user_Id, projectId

Upvotes: 1

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61509

I had to flip around LastEdit = max(timestamp) and ProjectRequests = count() by user_Id, projectId.

like:

pageViews
| extend projectId = extract(@"/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})", 1, url)
| where url matches regex @'/projects/([0-9a-f]{32}|[0-9a-zA-Z]{21,22})' 
and timestamp > ago(90d)
and isnotempty(user_Id)
| project timestamp, user_AccountId, user_Id, url, projectId
| summarize LastEdit = max(timestamp), ProjectRequests = count() by  user_Id, projectId

Upvotes: 0

Related Questions