1

I have a kusto query which returns all user's url, I need to take the userId from the url and only count the unique value (by userId). What I already made is: using
| project userIdSection = split(parse_url(url).Path, "/")[-1] in the query to extract userId out. But there are a lot of duplicates, how can I only count the unique user Ids?

user id returns successfully but with duplicates

when I apply distinct on it, it throws the error:

union isfuzzy=true requests | where cloud_RoleName contains "my-app" | project timestamp, id, name, userIdSection = split(parse_url(url).Path, "/")[-1], success | distinct userIdSection

What I expected is, to only get the unique userId from the url section per user. Example, currently I can only get a list of duplicate request per user who made 200+ requests. duplicate request count per user whenever count>200

With Peter's help, now I get a specific user visited more than 200 unique requests), but how to get all user whoever visited more than 200 unique requests? (userIdSection is the unique not customDimensions.userId.

enter image description here

jumpingbunny
  • 55
  • 5
  • *when I apply distinct on it, it throws the error* -> What error? – Peter Bons Apr 15 '23 at 09:51
  • Error: Distinct group key 'userIdSection' is of a 'dynamic' type. Please use an explicit cast as grouping by a 'dynamic' type is not supported. If the issue persists, please open a support ticket. Request id: 6e2fc1fa-d7ad-458a-8353-c0dea1be722e – jumpingbunny Apr 17 '23 at 22:39

1 Answers1

0

The reason is in the error description:

Error: Distinct group key 'userIdSection' is of a 'dynamic' type. Please use an explicit cast as grouping by a 'dynamic' type is not supported.

The result type of split is dynamic, you need to convert it to a string like this:

union isfuzzy=true requests 
| where cloud_RoleName contains "my-app" 
| project timestamp, id, name, userIdSection = tostring(split(parse_url(url).Path, "/")[-1]), success 
| distinct userIdSection

to get the count of distinct values use summarize dcount(userIdSection) instead of distinct userIdSection

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • One more question, is it easy to get the total number of the distinct userIdSection? I saw on the bottom of the query page, it shows 210 unique userIdSeciton value now, but looks like it's hard to apply count by distinct userIdSection. What I try to achieve is to check only if distinct userId count > 200 – jumpingbunny Apr 18 '23 at 23:06
  • you are such a great helper, thanks so much!!! I will update the final version of the query in the orginal post just in case it could help someone with the similar question. – jumpingbunny Apr 19 '23 at 16:58
  • I realzie one thing, the current query works for a specific user, my case (that I need to apply to an alert rule which uses kusto query) is to find out who (the userId from the customDimension column) has more than 200 requests in 24 hours. For now, I'm able to get non-unique user request counts per user (screen shot is added to the original post), but how to only get unique one (that userIdSeciton) per user, not a specific user? Thanks again! – jumpingbunny Apr 20 '23 at 02:44
  • @jumpingbunny Can you try `summarize dcount(userIdSection) by tostring(customdimensions.userId)`? – Peter Bons Apr 20 '23 at 14:31
  • thanks again to response fast and always help me to get closer. Tried, got error - "Ensure that expression: customdimensions.userId is indeed a simple name"
    I thought about to use subquery way, or sort of temp variable but not sure I went too far...if group by way not work.     – jumpingbunny Apr 20 '23 at 15:20
  • hi Peter, after changed customDimensions.userId to customDimensions_userId in the query, it works :) I'm not sure why customDimensions.userId in the project section works, but not in the summarize section. Cheers! – jumpingbunny Apr 21 '23 at 01:15
  • Hi @jumpingbunny I forgot, column names cannot have a "." in their name, those are replaced with a "_". – Peter Bons Apr 21 '23 at 13:22
  • You are right @Peter Bons, thanks a lot again – jumpingbunny May 08 '23 at 21:41