All Questions
2,812
questions
-1
votes
1
answer
37
views
What are the possible ways I can handle duplicate data in ADX
There are common ways to handle dup data as described Here
But I'm looking for more options.
Thanks
I have tried the options from here
https://learn.microsoft.com/en-us/azure/data-explorer/dealing-...
0
votes
0
answers
13
views
Issues with Defender Advanced Hunting using Python
In order to make some reports, I'm using KQL in Defender to read the existing tables. I make the KQL query in Defender and then i go to my python script and put the query inside and adjust the script ...
0
votes
1
answer
23
views
Azure DataBricks - Looking to query "workflows" related logs in Log Analytics (ie Name, CreatedBy, RecentRuns, Status, StartTime, Job)
We're looking to fetch logs of the section "workflows" from Azure DataBricks to our Log Analytics Workspace. We have our log analytics workspace connected with all logs enabled in diagnostic ...
1
vote
1
answer
26
views
KQL Query to filter Message based on Grafana Variable
In an KQL Query, displaying Azure Function App Logs, that will be used in Grafana, we want to have an Variable "Show Host Status Messages" in Grafana.
If "$ShowHostStatus" == True, ...
-1
votes
0
answers
36
views
Looking for Kusto query or a azure policy where an alert should be generated when azure blob data action role permissions are assigned on storage acct [closed]
AzureActivity
| where OperationName in ("MICROSOFT.AUTHORIZATION/ROLEASSIGNMENTS/WRITE", "MICROSOFT.AUTHORIZATION/ROLEDEFINITIONS/WRITE")
| where ActivityStatus == "Succeeded&...
0
votes
1
answer
26
views
How can I stack data correctly using kusto into a columnchart
I have the following kusto query:
AACHttpRequest
| where StatusCode != 200
| summarize ErrorCount=count() by bin(TimeGenerated, 1d), StatusCode
This gives me data like this:
Now I want to render ...
0
votes
0
answers
22
views
Why does ADX caching result from related dimension table/mv/function
I'm testing materialized views based on the queries below:
Lookup to another materialized view (bar_mv) storing last timestamps in field LAST_FOO_TS for some key(a,b,c)
.create materialized-view ...
1
vote
3
answers
71
views
How can I get all but the last row in a KQL query?
I've got a KQL query, and I want to strip out the last row of data. However, the number of rows returned is variable, so I can't use take (aka limit) because I don't know what number to pass it.
Is ...
0
votes
0
answers
27
views
How to Run control commands in KQL Function or any KQL Object
In extension to below url query ask.
Query Optimization in KQL || Pagination
I am trying to execute Stored Query result in one go means like in Function or any other object in KQL
Drop stored query ...
0
votes
1
answer
23
views
Issue with CASE operator - using different data type "Distinct types: I8,StringBuffer"
I'm having an issue with trying a pretty straightforward case in KQL, while I'm trying to set a dependency of the 'Breakdown by' field (a field that enables to breakdown of the graphs in various ways) ...
0
votes
1
answer
32
views
Query Optimization in KQL || Pagination
I have custom or Web UI where it fetches data from ADX streaming data. UI default fetch 20 records and if user want to see more records, scroll down and next 20 of records will be fetching from ADX ...
-2
votes
0
answers
21
views
Why there will use the blob when do the ingest using IngestFromStreamAsync, the connection string to blob is leaked during this process [closed]
I want to use the
var ingestClient = KustoIngestFactory.CreateDirectIngestClient(kcsb); var ingestionProperties = new KustoIngestionProperties(databaseName, tableName) { Format = DataSourceFormat....
0
votes
1
answer
72
views
How to create an alert for azure storage account if there is data action permissions assigned to a custom role or a built in role
How to create an alert using Kusto query when an Azure RBAC role (custom and built-in role) is assigned with a data action permission for azure storage account.
AzureActivity
| where OperationName == &...
0
votes
1
answer
40
views
KQL ingest query not working with 'Where' statement
I'm trying to ingest into a testing table in Kusto.
The table is created using this syntax:
.create table testing(id: string, label: string)
I'm trying to insert inline into this table using the ...
0
votes
2
answers
26
views
Append Values in Column from Table to Table In Kusto Control Command
If I have table A that has a column of Student Id, and I have table B that also has Student Id Column, what's the control command that to copy all values of Student Id from Table A to Table B?
0
votes
1
answer
63
views
Local Admin Report on log analytics workspace [closed]
Trying to make a local admin report on log analytics workspace, but having problems with the query.
enter image description here
This was created based on this tutorial https://www.systanddeploy.com/...
0
votes
0
answers
42
views
Connect Az managed Grafana instance to Azure Data Explorer cluster
I have an Azure Data Explorer cluster that's stood up in tenant A. I am trying to connect to it from my Grafana UI(adding a new data source), which is in tenant B. In the Azure Data Explorer cluster ...
0
votes
1
answer
54
views
How to ingest to kusto from local file with authentication through manage identity
Does manage identity auth support ingesting local csv file to Kusto? If it does, is there any sample code that you can refer me to?
If it doesn't, does subject name and issue certificate auth support ...
0
votes
1
answer
53
views
KQL Summarize unable to show Null values
I've stripped some of my query out but I am trying to count the entries from my query from 5 minute buckets.
There may occasionally be no data in these buckets as there were no requests but I want to ...
0
votes
2
answers
93
views
KQL - Break down timespan of how long an item is in a specific state by day
I am trying to calculate how long an item has been active in a specific state with KQL. Currently I am using window functions together with partitioning. This works well when I need to calculate the ...
0
votes
1
answer
38
views
KQL - How to enrich an event by matching an IP address to an IP range from a Sentinel Watchlist?
I am trying to enrich events from an Analytic Rule with a Watchlist as I did in Splunk.
My event contains a field named SourceIP that obviously contains an IP address.
I have a Watchlist that contains ...
0
votes
1
answer
35
views
Place KQL results into an indexable array
The problem is simple but the soution is not as straight forward.
I have a KQL query for extracting discinct values from a column
let Actions = EventLogs
| distinct DeviceVendor
| summarize action = ...
0
votes
1
answer
70
views
Using Kusto Query compare a time offset to current data set and calculate the difference
I'm trying to figure out how to compare a current data set for the previous 24 hours to a -7d offset and then display the difference in a percentage. I currently have the following :
let currentB =
...
0
votes
1
answer
58
views
Decimal Precision in KQL
I am trying to print decimal precision 2 in KQL but not able to achieve. When I use round() function it is rounding the number equal or greater than 5 number qualify.
My requirement is to print number ...
0
votes
1
answer
52
views
How would I look up an entire column by name and add that data to another table?
In KQL, given:
let A = datatable(XName:string, YName:string) [
"XData", "YData1",
"XData", "YData2",
];
let B = datatable(XData:double, YData1:double, YData2:...
0
votes
1
answer
21
views
How to Plot Pre-Averaged Time Series Data in KQL Without Using Summarize?
I am currently working on creating a dashboard graph, where my objective is to plot two different series on a timechart. The two series in question are represented by the processingRate and inputRate ...
-4
votes
0
answers
36
views
Regular Expression for IPv4 subnet [duplicate]
I am trying to create a regular expression for the IP subnet 192.168.224.0/22. The valid IP range is 192.168.224.1 to 192.168.227.254.
I can use this in Sentinel KQL query. Is the below correct?
192\....
0
votes
1
answer
34
views
KQL How to display default rows for every case even on no match
With KQL, is there a way to generate extra rows in the results table, for every possibility in a case statement, even if there were no matches from the dataset? This is to save the front end from ...
0
votes
1
answer
54
views
Logic Apps: Run query and visualize results Html Table displayed in Email
I would like to display the results of my query in a html table via email.
My attempt:
The above is attaching the .html to the email but i would like to display it in the email.
0
votes
1
answer
43
views
Why does "summarize count()" on a stored query result always give 0?
The following creates a stored query result named "tmp" with 100 rows from StormEvents:
Run query
.set stored_query_result tmp with (previewCount = 1, expiresAfter = 1h) <| StormEvents | ...
0
votes
1
answer
64
views
KQL Comparison to Find Changes
How would compare values in column to see if it's changed for a unique item. For example, how would I modify a query to only display a record if the serial number changed for a specific unique device. ...
0
votes
1
answer
55
views
How can I introduce a vertical reference line in KQL?
My question is similar to this one, except I want a vertical reference line (or dot) to mark point(s) in time.
How can I introduce a constant reference line based on an aggregation to a Kusto ...
0
votes
0
answers
55
views
How can I use kusto to show which permissions are being used by which users on the data plane
We are trying to analyze which Azure permissions are being used by which users, so that we can trim our custom roles back to only include permissions that are being used.
We have configured all of our ...
0
votes
1
answer
70
views
I am trying to create alerts when someone changes the IAM RBAC roles or permissions on azure storage accounts using Kusto query
AzureActivity
| where ResourceProviderValue contains "Microsoft.storage" and CategoryValue contains "Administrative"
| where OperationNameValue ==
"Microsoft. Authorization/...
0
votes
0
answers
44
views
can we apply KQL function on the KQL table data?
I have a KQL table with one newly added column(empty values), i want to insert values into that column with previous values of another column.
I am not able to apply previous logic on the entire table ...
1
vote
1
answer
44
views
KQL: bag unpack json into single row
I want the bag_unpack function into a single row instead of it turning each entity into a new row without explicitly making a summarize and make_set for every column. (This is because i will not know ...
0
votes
1
answer
26
views
Datetime and Timespan Caused Error in Kusto Command
I need to append a row that contains timespan and datetime in kusto command. However,
kusto is throwing error saying
Query schema does not match table schema.
QuerySchema=('string,string,string,...
0
votes
1
answer
60
views
Append New Row to Kusto Table
I want to manually add a new row to kusto table called "StudentTable". I was wondering what's the syntax? Assuming the table already exists and has column names of StudentID and StudentName
...
0
votes
1
answer
33
views
KQL: Filter the last full week of data
I want to filter the time column in a table by the last full week of data. without explicitly giving the dates of the last full week.
lets say i want the last full week today (wednesday 6/3), which ...
0
votes
1
answer
53
views
How can I parse a string and create a dynamic bag out of it?
Consider the following situation, where I have a column Column1 that can contain multiple key/value pairs in the form of key1=value1, key2=value2, ....
However, I don't know the name of the keys ...
0
votes
1
answer
72
views
Left Outer Join in KQL based on Date and Time
I'm trying to perform a left outer join in Kusto Query Language (KQL) between two tables, trips and alerts, based on a datetime condition. The trips table contains information about unit trips with ...
0
votes
0
answers
37
views
Azure Heartbeat Table: _ResourceId is blank
I recently wrote some logic for alerting on VMs offline outside of their start-stop schedules, based on the Heartbeat table. However, that produced some unexpected results, as the _ResourceId value ...
0
votes
1
answer
25
views
Filtering for strings containing dashes won't work
I have an issue when comparing strings containing dashes - that I can't understand. Consider the following straightforward table:
// Create table
.create table Mappings (AppId:int, Namespace:string);
...
1
vote
1
answer
51
views
How to copy a delta table into a Data Explorer Table?
I have a delta table created with Azure Synapse and I want to have the same table in Azure Data Explorer.
Linked service is created, I know I can use Azure Data Explorer Command activity or Copy.
But ...
0
votes
2
answers
111
views
How to combine values in different JSON areas in Kusto?
I am currently struggling with Kusto to get the data projected in the way I need it.
I found a solution for the following example already:
print sampleData = dynamic({
"url": "/api/...
0
votes
1
answer
62
views
Log Analytics Workspace / Azure Watchlist: KQL Filtering on datetime
I'm having trouble converting a date value in a watchlist to be understood as a datetime value.
I've tried using format_datime(), datetime(), date() but the value becomes blank.
steps to reproduce:
...
0
votes
1
answer
40
views
Why ID column empty when creating external table with ID used in partitioning?
I use the following KQL request to create an external table in Azure Data Explorer:
.create external table TESTS (ID: string, Key: string, Value: string)
kind=adl
dataformat=parquet
(
h@'abfss://...
0
votes
2
answers
84
views
Kusto query for memory/CPU usage as percent of max for an Azure Container App
Is it possible to write a Kusto query for memory/CPU usage as percent of max for an Azure Container App? In the table AppPerformanceCounters I can find data on the Private Bytes and % Processor Time. ...
1
vote
1
answer
54
views
KQL query | summarize arg_max
I have developed a powershell script that checkes the Status of Application Pool in IIS and gives me an output like this in Azure Function App:
enter image description here
the function app should run ...
0
votes
1
answer
53
views
How to avoid "PKIX path building failed" error while querying Kusto with Java SDK?
I'm using Java SDK for Kusto (Azure Data Explorer).
<groupId>com.microsoft.azure.kusto</groupId>
<artifactId>kusto-data</artifactId>
<version>5.0.3</version>
I'm ...