Much needed KQL query assistance

Question

I am completely new to KQL. Any help would be greatly appreciated. I am trying to figure out how to pull specific data out of a field. What I mean by that is this. I have this field called customeDimensions and the value in the field is this

{
    "Page": "1",
    "PageCount": "7",
    "Filter_0:School": "Krieger School of Arts and Sciences",
    "ResultCount": "195",
    "PageSize": "30",
    "Filter_6:Level": "Doctoral",
    "Filter_1:School": "Whiting School of Engineering",
    "Filter_8:Level": "Non-Degree",
    "Filter_9:Level": "NonDegree",
    "Filter_7:Level": "Post-Doctoral",
    "Filter_4:Level": "Upper Level Undergraduate",
    "Filter_10:MedicineMDOnly": "N",
    "Filter_5:Level": "Graduate",
    "Filter_3:Level": "Lower Level Undergraduate",
    "Filter_2:Term": "Intersession 2023"
}

You can see multiple field names starting with Filter_ (ex. Filter_0:School, Filter_6:Level, Filter_2:Term). What I need to be able to do is read through the entire customeDimensions field and pull the value where the field name starts with filter and then get the word after the :
So, for the examples I used, I would need the words School, Level and Term.

These filter fields are used on a search screen and then logged into app insights. I am trying to do analysis to see how often each filter is actually being used. So how many times is the School, Level and Term filter used.

Answer 1

you could try using a combination of mv-apply and parse-where.

for example:

print customDimensions = dynamic({
    "Page": "1",
    "PageCount": "7",
    "Filter_0:School": "Krieger School of Arts and Sciences",
    "ResultCount": "195",
    "PageSize": "30",
    "Filter_6:Level": "Doctoral",
    "Filter_1:School": "Whiting School of Engineering",
    "Filter_8:Level": "Non-Degree",
    "Filter_9:Level": "NonDegree",
    "Filter_7:Level": "Post-Doctoral",
    "Filter_4:Level": "Upper Level Undergraduate",
    "Filter_10:MedicineMDOnly": "N",
    "Filter_5:Level": "Graduate",
    "Filter_3:Level": "Lower Level Undergraduate",
    "Filter_2:Term": "Intersession 2023"
})
| mv-apply d = customDimensions on (
    parse-where d with * '"Filter_' i:int ":" key '"' *
    | where isnotempty(key)
)
| summarize count() by key

key	count_
School	2
Level	7
MedicineMDOnly	1
Term	1