KQL Query output limit of 5 lakh rows
Hi , i have a kusto table which has more than 5 lakh rows and i want to pull that into power bi. When i run the kql query it gives error due to the 5 lakh row limit but when i use set notruncation before the query then i do not get this row limit error on power bi desktop but get this error in power bi service after applying incremental refresh on that table. My question is that will set notruncation always work and i will not face any error further for millions of rows and is this the only limit or there are other limits on ADE due to which i may face error due to huge volume of data or i should export the data from kusto table to azure blob storage and pull the data from blob storage to power bi. Which will be the best way to do it?
Azure ADX - UpdatePolicy fails to insert data
Hi Everyone, I believe everyone is doing good and safe. I am facing challenge with ADX. Please find the problem details below. Problem statement: We are unable to insert a result data into a target table from source table using an UpdatePolicy. Description: We have written an UpdatePolicy on a table. This UpdatePolicy will accept query parameters as an ADX function. This function returns output result in the form of table. Further, This table output result received should be inserted into target table. Additional Details: UpdatePolicy helps to update the data into a target table from source table dynamically/automatically. UpdatePolicy is almost equivalent to Triggers in SQL Server to do dynamic insert into a target table. Syntax of UpdatePolicy .alter table TargetTable policy update ``` [ { "IsEnabled": true, "Source": "SourceTable", "Query": "SourceTable | extend Result = G3MS_ClearAlarm(Id, CountryCode, OccuredTime) | project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, ClearTime = Result.ClearTime", "IsTransactional": true, "PropagateIngestionProperties": false } ] ``` Error Received when executed Error during execution of a policy operation: Request is invalid and cannot be processed: Semantic error: SEM0085: Tabular expression is not expected in the current context. If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.
External Table in ADX
Hi, I'm trying to create an external table in ADX which uses a Synapse Analytics (SA) database view (called undelivered). The undelivered view itself is query data from a Cosmos analytical store I've create a user defined idenity Added the identiy to the ADX cluster, SA and Cosmos Updated the ADX database: .alter-merge cluster policy managed_identity[ { "ObjectId": "a3d7ddcd-d625-4715-be6f-c099c56e1567", "AllowedUsages": "ExternalTable" } ] Created the database users in SA -- Create a database user for the ADX Managed Identity CREATE USER [adx-synapse-identity] FROM EXTERNAL PROVIDER; -- Grant read permissions ALTER ROLE db_datareader ADD MEMBER [adx-synapse-identity]; GRANT SELECT ON OBJECT::undelivered TO [adx-synapse-identity]; From within SA I can "SELECT * FROM undelivered" and the correct information is returned But when I come to create the external table in ADX: .create-or-alter external table MyExternalTable ( Status: string ) kind=sql table=undelivered ( h@'Server=tcp:synapse-xxxxx.sql.azuresynapse.net,1433;Database="Registration";ManagedIdentityClientId=<key>;Authentication=Active Directory Managed Identity;' ) with ( managed_identity = "<key>" ) I get the error: Managed Identity 'system' is not allowed by the managed_identity policy for usage: ExternalTable So even with me specifying the managed identity I want to use it is still trying to use the system one. How can I get the external table created with the correct managed identity? Any questions please just ask Thanks
KQL Query to summerize session counts vertically
I'm trying to find a "good" way to achieve what I think is a simple task but cannot think of a simple solution. I have logs with session information, one entry per session StartTime(datetime), EndTime(datetime), Duration(in seconds), Computer(string) I want to count how many sessions are active for each 5 minute interval and graph that. Keep in mind, the sessions will overlap. I included a graphic of what I'm trying to do. With the result below. The black boxes represent an entry with a starttime and an endtime. Which should return: Time + 5, 1 Time + 10, 3 Time + 15, 3 Time + 20, 2 I have found many similar examples but they all depend on the the thing I'm trying to bin, or group, to be a single point in time, but my problem is each entry has an active range, a start time and an end time per record.
[New blog post] Putting the SenseCAP T1000 GPS Tracker on the map using Azure Data Explorer
Check out my blog post on how to putt the Seeed Studio SenseCAP T1000 GPS Tracker on the map using Azure Data Explorer. Realtime positions, formatted and forwarded by the LoRaWAN The Things Network backend, are transformed using the Kusto Query Language and represented in an ADX Dashboard. https://sandervandevelde.wordpress.com/2023/10/03/putting-the-sensecap-t1000-gps-tracker-on-the-map-using-azure-data-explorer/
String to Columns
I want to extend DetectionMethods which is string data type in emailevents table. But this may apply to other tables and situations, EmailEvents | take 1000 | extend kqlt=parse_json(DetectionMethods) | extend DM_Phish=kqlt.Phish, DM_Spam=kqlt.Spam Above results in adding this, Values are still displayed as ["Value"] and not Value Is there a better way to do this. How can I bring the value out of [""] -------------------- If the string had more keys , is there a way to dynamically create columns. Seems like bag_unpack does it but I cannot use those in query such as filtering with where. {"Phish":["Spoof external domain"],"Spam":["Mixed analysis detection"]}How to connect with multiple kusto cluster and add users with reader permission?
I have to add appid and tenant id to grant permission to the application and we have almost 38 public kusto clusters. I would like to automate this process so i don't have connect the cluster each time and run the below command everytime: .add database [db_name] viewers('aadapp=APP_GUID_HERE;tenant_id here') "TEAM_NAME_HERE" is there a way, i can write a script which connect to all 38 clusters one by one and run the above command?If/Then/Or Statement Based on Values in Column Ranges?
Hi all! I need help with the below if/then or statement that seems simple in theory but I am struggling to write it in KQL. I am looking to get the results in the red "Adjusted Amount" column with a statement similar to this: "if all values in "Issue" are the same AND all values in "Level" are the same then return sum of "Amount" in "Adjusted Amount"- OR if all values in "Issue" are the same BUT "Level" is unique then return "Amount" in "Adjusted Amount". Example result: Issue level Amount Adjusted Amount ABCD 3 4995 5837 ABCD 2 7 7 ABCD 3 842 5837 WXYZ 3 3000 3000 WXYZ 4 500 500
Kusto Function Help Array_Sum, Array_Slice
Hi All, I am trying to partition an array in custom slices, then I would like to add all the sub arrays for each slice Example - For a Table T defined below, I can do the following: let T = datatable(key: int,t:dynamic) [ 1, dynamic([1,2,3,4,5,6,7, 8, 9, 10, 11, 12, 15, 26, 45]) // example array ]; T | extend slices = array_split(t, dynamic([6, 12])) // custom slices | mv-expand slices | extend sum = array_sum(slices) | summarize slices = make_list(sum)// sum the sub arrays but now If I want to define this as a function and apply to all of the rows in my table I would like to do something like this: let condense = (t:dynamic) { | extend arr = array_split(t, dynamic([6, 12])) | mv-expand arr | extend sum = array_sum(arr) | summarize arr = make_list(sum) // this can be a function }; Does anyone have an idea on how to approach this problem ?
