Creating tiles in azure sentinel workbook using KQL

Question

I am using this query to display what I want in workbook, but I want to have individual tiles with their values respectively for very high, high, medium, etc. But when I write this query and turn on tiles in visualization, it wont give me options to create tile for each of the variable in tile settings. What can I do to achieve this?

InALogs_CL
| summarize VeryHigh=count(risk_level_s=="very-high" or risk_assessment_risk_level_s=="very-high"), High=count(risk_level_s=="high" or risk_assessment_risk_level_s=="high"), Medium=count(risk_level_s=="medium" or risk_assessment_risk_level_s=="medium"), Low=count(risk_level_s=="low" or risk_assessment_risk_level_s=="low"), VeryLow=count(risk_level_s=="very-low" or risk_assessment_risk_level_s=="very-low"), None=count(risk_level_s=="none" or risk_assessment_risk_level_s=="none")

score 0 · Answer 1 · answered Oct 07 '21 at 16:30

Each row result of the query will become a tile. so if you want tiles for each severity, you'd want to do something more like

| extend severity = case( 
    risk_level_s=="very-high" or risk_assessment_risk_level_s=="very-high". "Very High",
    risk_level_s=="high" or risk_assessment_risk_level_s=="high","High",
    risk_level_s=="medium" or risk_assessment_risk_level_s=="medium", "Medium", 
    risk_level_s=="low" or risk_assessment_risk_level_s=="low", "Low", 
    risk_level_s=="very-low" or risk_assessment_risk_level_s=="very-low", "Very Low" 
    risk_level_s=="none" or risk_assessment_risk_level_s=="none", "None",
    "unknown")
| summarize count() by severity

which would end up with a result like

severity	count_
Very High	1
Low	1
unknown	27

you could then use the "Thresholds" renderer in the tiles to assign specific icons to the severities as the title field of the tiles, and use the "Big Number" renderer in the left section of the tile.

you'd have no tiles for the severities that don't have any matching rows.

If you need all the tiles, even 0's, you could either anti-join with a datatable that has the individual rows with 0's, or you could keep something like your original query (though i think the count items you have above should be countif ?), and add a | evaluate narrow() to the end.

Not all data sources support the evaluate operator though (like Azure Resource Graph queries do not).

you might also want to use =~ in all those comparisons if there's any chance that the values could be in other cases, right now, you'd get "unknown" of the risk level value was "High" or "HIGH", as this is only looking for all lower case "high"

Hey, thanks for answering, it was helpful but I figured out what I needed which is quite similar to your answer. — Shikhin Dahikar, Oct 08 '21 at 04:13

score 0 · Accepted Answer · answered Oct 08 '21 at 04:11

I got the answer, I had to use datatable for those values to get converted into a separate table so that every severity category can be detected in tiles settings.

datatable (Count: long, status: string) [0, "Very High", 0, "High", 0, "Medium", 0, "Low", 0, "Very Low", 0, "None"]
| union
    (
    InALogs_CL
    | extend status = case(    
        risk_level_s == "very-high" or risk_assessment_risk_level_s
 == "very-high", "Very High",
        risk_level_s == "high" or risk_assessment_risk_level_s
 == "high", "High",
        risk_level_s == "medium" or risk_assessment_risk_level_s
 == "medium", "Medium",
        risk_level_s == "low" or risk_assessment_risk_level_s
 == "low", "Low",
        risk_level_s == "very-low" or risk_assessment_risk_level_s
 == "very-low", "Very Low",
        risk_level_s == "none" or risk_assessment_risk_level_s
 == "none", "None",       
        "True"
        )
    | where status != "True"
    | summarize Count = count() by status
    )
| summarize Count=sum(Count) by status

Creating tiles in azure sentinel workbook using KQL

2 Answers2