Question 104786 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Get a category name from the id in the url using kusto query

I need to get the category name from category id using kusto query.

First i have got the most searched url from the website using the below kusto query and ran it in app insights logs

Requests
| where resultCode==200
| where url contains "bCatID" 
| summarize count=sum(itemCount) by url
| sort by count
| take 1

From the above query i got the result like
https://www.test.com/item.aspx?idItem=123456789&bCatID=1282

So for corresponding categoryid=1282 i need to get the category name using kusto

Tags:

3

Answers


  1. 0

    you can use the parse operator.

    for example:

    print input = 'https://www.test.com/item.aspx?idItem=123456789&bCatID=1282'
| parse input with 'https://www.test.com/item.aspx?idItem=123456789&bCatID='category_id:long

    enter image description here

    Login or Signup to reply.
  2. 0

    parse_urlquery

    print url ="https://www.test.com/item.aspx?idItem=123456789&bCatID=1282"
| extend tolong(parse_urlquery(url)["Query Parameters"]["bCatID"])
    url Query Parameters_bCatID
    https://www.test.com/item.aspx?idItem=123456789&bCatID=1282 1282

    Fiddle

    Login or Signup to reply.
  3. 0

    Feedback to the OP query
    Not an answer

    1. KQL is case sensitive. The name of the table in Azure Application Insights is requests (and not Requests).
    2. resultCode is of type string (and not integer/long) and should be compared to "200" (and not 200)
    3. bCatID is a token and therefore can be searched using has or even has_cs, which should be preferred over contains due to performance reasons.
    4. URLs can be used with different parameters. It might make more sense to summarize only by the Host & Path parts + the bCatID query parameter.
    5. count is a reserved word. It can be used as alias only if qualified: ["count"] or [‘count’] (or better not used at all).
    6. sort followed by take 1 can be replaced with the more elegant top 1 by …
    requests
| where resultCode == "200"
| project url = parse_url(url), itemCount
| summarize sum(itemCount) by tostring(url.Host), tostring(url.Path), tolong(url["Query Parameters"]["bCatID"])
| top 1 by sum_itemCount
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search