CODEWITHSUNDEEP
kqlazure-sentinelazure-monitor-workbooks
Shikhin Dahikar
Shikhin Dahikar

Reputation: 11

Creating tiles in azure sentinel workbook using KQL

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")

Upvotes: 1

Views: 690

Answers (2)

Shikhin Dahikar
Shikhin Dahikar

Reputation: 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

Upvotes: 0

John Gardner
John Gardner

Reputation: 25136

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"

Upvotes: 0

Related Questions