Getting only unique values within a category in Kusto Query Language (Azure Monitor Logs)

user_11588947 0
Viewed 13786

I have data in this format :

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        1    <--
  A         200        1    <--
  A         200        2
  B         300        1
  B         300        1    <--

I need to remove the duplicate values of step names within each Session_ID. For example in ID = 200, there are three '1's which need to be changed to one '1', so the final data looks like :

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        2
  B         300        1
distinct-values kql azure-data-explorer azure
1 Answers

You should use distinct operator in your case:

your_table
| distinct Category, Session_ID, Step_Name

then you can get the expected output like below, it works at my side:

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        2
  B         300        1

And for your question in the comment, if you use the above query, the record like "A 100 1" would consider as one entity, and only if there are 2 or more exact same record like "A 100 1" would remain only 1 record if using distinct.

And if there is another record like "B 100 1", then the both 2 records "A 100 1" and "B 100 1" would remain.

user_101858160
Related