Forum Discussion

marked-data's avatar
marked-data
Copper Contributor
Mar 18, 2019
Solved

split and regex in Kusco

Hi all,   I have a query in Kusto to return Details from Table which returns multiple rows of sentence text: Table | project Details Output: Starting cycle 20349 Starting scheduling for cycle ...
  • marked-data's avatar
    marked-data
    Mar 20, 2019

    Yoni 

     

    Hey Yoni,

     

    Thanks for the example. I did get what I needed by building off your example: This query takes the log lines, splits into words, screens out numbers. and summarizes the frequency of the word occurrence.

     

    Table // which has a column named "Details"| project KeyWords = split(Details, " ")
| mv-expand KeyWords 
| where KeyWords matches regex @"^[a-zA-Z]+$" 
| summarize count() by tostring(KeyWords)

     

Yoni's avatar
Yoni
Icon for Microsoft rankMicrosoft
Mar 18, 2019

here's an example for using "split()" (depending on your actual use case, operators/functions linked-to below may be relevant as well):

 

split() function: https://docs.microsoft.com/en-us/azure/kusto/query/splitfunction 

parse operator: https://docs.microsoft.com/en-us/azure/kusto/query/parseoperator

reduce operator: https://docs.microsoft.com/en-us/azure/kusto/query/reduceoperator

replace() function: https://docs.microsoft.com/en-us/azure/kusto/query/replacefunction (for removing numeric values you mentioned was part of your goal)

 

MyTable // which has a column named "Details"
| extend Details = split(MyColumn, " ") // This assumes the "words" are separated by a single space
marked-data's avatar
marked-data
Copper Contributor
Mar 18, 2019

Hi Yoni 

 

Thanks for your reply, I was adding more text to the post when I got your reply.

 

I used a similar format but that gives rows containing an array of the split words. I need a row for each word or the syntax to unpack the arrays so that each member is a row.

 

Thanks

    • marked-data's avatar
      marked-data
      Copper Contributor
      Mar 19, 2019

      Yoni 

       

      Cool, mvexpand does the trick. 

       

      How can I perform further operations in the query on the expanded Keywords:

       

      Table // which has a column named "Details"
      | project KeyWords = split(Details, " ")
      | mv-expand KeyWords

       

      .... some way of referencing the Keywords output here

       

      I tried some permutations but now the output from mv-expand is dynamic which fails to accept additional operations.

       

      • Yoni's avatar
        Yoni
        Icon for Microsoft rankMicrosoft
        Mar 19, 2019

        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
        789

Resources