User Profile
Yoni
MicrosoftJoined Sep 23, 2017
User Widgets
Recent Discussions
Re: Query help
you could try something like this: datatable(s:string) [ 'ACTIVATE serviceIds: [501]', 'ACTIVATE serviceIds: [669]', 'ACTIVATE serviceIds: [20, 662]', 'ACTIVATE serviceIds: [20, 662]', 'ACTIVATE serviceIds: [20, 645, 669]', ] | parse s with * "serviceIds: " serviceIds:dynamic | mv-expand serviceId = serviceIds to typeof(long) | summarize count() by serviceId | render barchart1.2KViews0likes1CommentRe: How to troubleshoot kusto failures with corelationid
you can see the FailureReason by running ".show queries" and filtering to the ClientActitivityId which is relevant to the execution you're looking into. if you need assistance with optimizing the query after going through query best practices and making sure all of them have been implemented in your query, please share the query. https://docs.microsoft.com/en-us/azure/kusto/query/best-practices2.3KViews0likes0CommentsRe: Guide on optimizing joins on cross-cluster tables for a .NET data ingestion tool design
some relevant (generic) docs: https://docs.microsoft.com/en-us/azure/kusto/query/best-practices#join-operator https://docs.microsoft.com/en-us/azure/kusto/query/joincrosscluster if you need more specific help, please share more information about the queries you're running988Views0likes0CommentsRe: How to format output
for having both in the same output row, you could potentially use `countif()`: https://docs.microsoft.com/en-us/azure/kusto/query/countif-aggfunction let AuditLogs = datatable(LoggedByService:string, ActivityDisplayName:string, Result:string) [ "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "failure", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", ] ; AuditLogs | where LoggedByService == "Self-service Password Management" | summarize SuccessCount = countif(Result == "success"), FailureCount = countif(Result == "failure") by ActivityDisplayName alternatively, if you want the output in the form of a property bag, you could use `make_bag()` on top of your original aggregation: https://docs.microsoft.com/en-us/azure/kusto/query/make-bag-aggfunction let AuditLogs = datatable(LoggedByService:string, ActivityDisplayName:string, Result:string) [ "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "failure", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "User started security info registration for self-service password reset", "success", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", "Self-service Password Management", "Reset password (self-service)", "failure", ] ; AuditLogs | where LoggedByService == "Self-service Password Management" | summarize count() by ActivityDisplayName, Result | summarize Results = make_bag(pack(Result, count_)) by ActivityDisplayName1.7KViews0likes1CommentRe: Summarize dynamic array?
datatable(EventData:dynamic) [ dynamic({"Tiles":["1", "2", "3", ""]}), dynamic({"Tiles":["4", "1", "1", ""]}), dynamic({"Tiles":["5", "4", "1", ""]}), dynamic({"Tiles":["6", "4", "2", ""]}), ] | mv-expand Value = EventData.Tiles | where isnotempty(Value) | summarize count() by toint(Value) | top 3 by count_ | project Value6.3KViews0likes1CommentRe: Summarize dynamic array?
based on my understanding of your description, this should work (like in the original example). if my understanding isn't correct, please provide the input data set using the `datatable` operator, so that it's clearer how your input data set looks like datatable(EventData:dynamic) [ dynamic({"Tiles":["1", "2", "3", ""]}), dynamic({"Tiles":["1", "12", "9", ""]}), dynamic({"Tiles":["12", "12", "7", ""]}), dynamic({"Tiles":["4", "6", "9", ""]}), dynamic({"Tiles":["9", "5", "5", ""]}), ] | mv-expand with_itemindex = Tile Value = EventData.Tiles | where isnotempty(Value) | summarize c = count() by Value = toint(Value), Tile = Tile + 1 | summarize arg_max(c, Value) by Tile | project-away c this returns: Tile Value 1 1 2 12 3 96.3KViews0likes3CommentsRe: Summarize dynamic array?
using the `datatable` operator in that sample was just in order to have some input data set. the "point" (answer to your original question) was in the lines starting from `| mv-expand ...` if that doesn't solve your issue -perhaps, you can show a sample of how your _real_ data looks like, so that we can work with that?6.4KViews0likes5CommentsRe: Summarize dynamic array?
Here's one option: datatable(Data:dynamic) [ dynamic([9, 12, 24]), dynamic([17, 12, 37]), dynamic([9, 17, 37]), ] | mv-expand with_itemindex = ArrayIndex Data to typeof(int) | summarize c = count() by Data, ArrayIndex | summarize arg_max(c, Data) by ArrayIndex | project-away c6.4KViews0likes7CommentsRe: Summarize with dynamic variable involved
consider the following option: datatable(key:long, Timestamp:datetime, d:dynamic) [ 1234, datetime(2019-07-24 23:59:33), dynamic({"isA":false,"isRR":false,"isE":true, "isM":null,"isVS":0, "isBot":false}), 3938, datetime(2019-07-24 12:04:32), dynamic({"isA":true, "isRR":false,"isE":false,"isM":null,"isVS":1000,"isBot":false}), 3982, datetime(2019-07-24 01:33:13), dynamic({"isA":false,"isRR":true, "isE":false,"isM":null,"isVS":0, "isBot":true }) ] | summarize count_key = dcount(key), count_key_where_isA_is_true = dcountif(key, d.isA == true), count_key_where_isRR_is_true = dcountif(key, d.isRR == true) by Date = startofday(Timestamp)1.3KViews0likes0CommentsRe: Dual plot x-axis
one thought (which may not be great if you have a lot of series to compare, but here goes) would be "normalizing"/aligning the datetime values according to those of one arbitrary "base" series. as shown in the example below: let T = datatable(value:int, dt:datetime) [ 1, datetime(2019-07-18 15:26:01), 2, datetime(2019-07-18 15:27:01), 1, datetime(2019-07-18 15:28:01), 2, datetime(2019-07-18 15:29:01), 3, datetime(2019-07-18 15:30:01), 1, datetime(2019-07-18 15:31:01), 5, datetime(2019-07-18 15:32:01), 0, datetime(2019-07-18 16:26:01), 1, datetime(2019-07-18 16:27:01), 2, datetime(2019-07-18 16:28:01), 3, datetime(2019-07-18 16:29:01), 2, datetime(2019-07-18 16:30:01), 1, datetime(2019-07-18 16:31:01), 0, datetime(2019-07-18 16:32:01), ] ; let T_base = T | where dt between (datetime(2019-07-18 15:00)..1h) | project series = "15:00 to 16:00", dt, value ; let T_other = T | where dt between (datetime(2019-07-18 16:00)..1h) | project series = "16:00 to 17:00", dt, value ; let diff = toscalar(T_other | summarize min(dt)) - toscalar(T_base | summarize min(dt)); T_base | union ( T_other | extend dt = dt - diff ) | render timechart1.4KViews1like1CommentRe: how to select multiple rows the equal max value using summarize
let T = datatable(id:long, dt:datetime, value:long) [ 1, datetime(2019-01-01), 100, 1, datetime(2019-01-01), 100, 1, datetime(2019-02-01), 300, 1, datetime(2019-02-01), 200, 2, datetime(2019-01-01), 100, 2, datetime(2019-02-02), 200, 2, datetime(2019-02-02), 300, ] ; T | summarize dt = max(dt) by id | join kind=inner T on id, dt | project id, dt, value815Views0likes0CommentsRe: Cross Joins in Kusto
quoting from: https://docs.microsoft.com/en-us/azure/kusto/query/joinoperator#cross-join Kusto doesn't natively provide a cross-join flavor (i.e., you can't mark the operator with kind=cross ). It isn't difficult to simulate this, however, by coming up with a dummy key: X | extend dummy=1 | join kind=inner (Y | extend dummy=1) on dummy12KViews1like0CommentsRe: Partial query failure: Low memory condition Kusto
such errors can be a result of a non-optimized workload running against your cluster. the recommendation is to make sure your queries are following query best practices: https://docs.microsoft.com/en-us/azure/kusto/query/best-practices. This specific error is not necessarily coming from your own query (though it might) – it means that at least on one machines in the cluster the free memory is running low. If this issue persists and optimizing the query according to the guidelines above doesn't not help in alleviating it, you should open consider opening a support ticket for your resource26KViews0likes3CommentsRe: How to pick up records by taking last record in each group
generally speaking, getting the "last" record in each group can be achieved using "summarize arg_max(..)" or "summarize arg_min(..)". If you'd interested in providing a sample data set (e.g. using the "datatable" operator), this forum could assist with authoring the query. relevant links for operators/functions mentioned above: - https://docs.microsoft.com/en-us/azure/kusto/query/arg-max-aggfunction - https://docs.microsoft.com/en-us/azure/kusto/query/arg-min-aggfunction - https://docs.microsoft.com/en-us/azure/kusto/query/datatableoperator43KViews1like0CommentsRe: Best practice for tracking data source
If your source data is formatted as JSON, a JSON mapping will allow you to specify 2 (and only these 2) special transformations: SourceLocation and SourceLineNumber, which enable you to enrich your records with both the name of the file that included the record, and the line number of that record in the source file: https://docs.microsoft.com/en-us/azure/kusto/management/mappings#json-mapping as for viewing existing mappings which have already been created, you can use this command: https://docs.microsoft.com/en-us/azure/kusto/management/tables#show-ingestion-mappings1.7KViews0likes1CommentRe: Best practice for tracking data source
do the options mentioned in this blog post allow you to achieve your intention? (add another column, at ingestion time, but in a simple and efficient manner) https://yonileibowitz.github.io/kusto.blog/blog-posts/ingestion-time-metadata.html1.7KViews0likes3CommentsRe: split and regex in Kusco
i'm not sure i understand the question - so i usually find an example to be helpful - feel free to "manipulate" this to the form which demonstrates your intention: let Table = datatable(Details:string) [ "123 456 789 abc def", "The quick brown fox jumps over the lazy dog" ] ; Table | project KeyWords = split(Details, " ") | mv-expand KeyWords to typeof(string) | where KeyWords matches regex @"^\d+$" -> this will return: KeyWords -------- 123 456 78910KViews0likes1Comment
Recent Blog Articles
No content to show