Splitting one column into multiple columns with a re-usable function in KQL

Question

I'm very new to KQL and I could have completely the wrong idea about the method I'm using to solve this problem so feel free to suggest a better way but I'll try my best to explain what I'm trying to achieve.

I am querying a particular dataset with many different queries but one consistent thing about it is one of the columns needs to be parsed from a crazy text field into multiple columns.

The columns of the the original data are things like "Computer", "User", then a large string field like this:

"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)"

So I've defined a function(which works when not run in the function) to parse that string column into multiple string columns:

Let parseEventData = (EventData:string){
EventData
| parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices:long * "sliceNumber=" sliceNumber:long * "lockTime=" lockTime ", releaseTime=" releaseTime:date "," * "previousLockTime=" previousLockTime:date ")" *  
| project resourceName, totalSlices, sliceNumber, lockTime, releaseTime, previousLockTime
}

So now I want to output a new table which is "Computer","User","resourceName", "totalSlices", "sliceNumber", "lockTime", "releaseTime", "previousLockTime" e.g. the original table but with this column parsed out.

I've tried things like:

Events
| where parameter == "blah"
| project name,computer,parseEventData(EventData)

But it will give the error

Operator source expression should be table or column

Is there a way to add a table to 2 existing columns like this or am I barking up the wrong tree?

Note:

This does work if I don't use a function at all and just use another pipe and do the parsing there. The issue being is that I will be using this type of Parsing for almost every query I do and I feel like it could be really messy / time consuming to write out the entire thing each time! Hope that all makes sense.

Thanks :)

Answer 1

you can try this:

let EventData = datatable(Computer:string, User:string, EventText:string)
[
    "C1", "U1", "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)"
];
let parseEventData = (T:(EventText:string)) {
    T
    | parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices:long * "sliceNumber=" sliceNumber:long * "lockTime=" lockTime ", releaseTime=" releaseTime:date "," * "previousLockTime=" previousLockTime:date ")" *  
    | extend resourceName, totalSlices, sliceNumber, lockTime, releaseTime, previousLockTime
    | project-away EventText
};
parseEventData(EventData)

Computer	User	resourceName	totalSlices	sliceNumber	lockTime	releaseTime	previousLockTime
C1	U1	PipelineScheduler	27	23	02/17/2016 08:40:01	2016-02-17 08:40:01.0000000	2016-02-17 08:39:01.0000000

Alternatively, you can use the invoke operator as follows:

EventData
| where <condition>
| invoke parseEventData()