Need help on generating KQL query

Gurudas Pise 0 Reputation points
2024-02-16T11:07:16.56+00:00

Currently, I am facing difficulty in constructing KQL to display a chart on our dashboard for the following requirements: Requirements: We're fetching log messages from Application Insights, which is a text as like
"#TotalVM:15,Running:10,highcompute:5". ( Now with this text we need to generate graph through KQL which shows below chart Now our goal is to write KQL that can parse this message and generate chart as given below.
KQL

So looking a help regarding writing KQL which generates above graph.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
501 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wilko van de Velde 2,226 Reputation points
    2024-02-19T07:40:43.8433333+00:00

    Hi @Gurudas Pise ,

    Indeed use the parse operator, like suggested. The query, with the render of the column chart, will be:

    
let Example = datatable(LogMessage: string)
    [
    "#TotalVM:15,Running:10,highcompute:5"
    ];
//
let temp = Example
| parse LogMessage with * "TotalVM:" TotalVM: long * ",Running:" Running: long * ",highcompute:" highcompute: long * 
| extend Stopped = TotalVM - Running, nonhighcompute = TotalVM - highcompute;
//
temp
| project Running, Stopped
| extend ColumnNr = 1, ColumnTitle= "Total VM"
| union (temp
| project highcompute, nonhighcompute
| extend ColumnNr = 2, ColumnTitle= "High Compute VM")
| render columnchart with (kind=stacked, xcolumn=ColumnTitle)

    I had to do a trick to make the two columns in the columnchart, so therefore I added the ColumnNr and ColumnTitle.

    More info about the column chart can be found here: https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/visualization-columnchart?pivots=azuredataexplorer

    Kind regards, Wilko

    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
  2. Gurudas Pise 0 Reputation points
    2024-02-22T12:05:50.05+00:00

    Here I wrote below Kusto Query

    datatable( VM_Category: string, VM_Type: string, Count: int ) [ "Running/Stopped VM", "Running", 10, "Running/Stopped VM", "Stopped", 5, "HighCompute/NonHightCompute VM", "HighCompute", 2, "HighCompute/NonHightCompute VM", "NonHighCompute", 8, ] | summarize sum(Count) by VM_Category, VM_Type | render columnchart with (kind=stacked, xcolumn=VM_Category, //ycolumn=sum_Count, title="VM Status by Type")

    which some how shown the grapht. But only issue is like I have hard-coded the count value in datatable. I need to get the value from any variable. I tried with let keyword but its not accessible inside the data table. Any workaround for the same?

    User's image