Home > Net >  Create columns from json format text
Create columns from json format text

Time:07-12

Is there anyway I can get the following to work without using the "parse" function? My input contains a field with delimited text, where there is a column name followed by "=" and then the value. I would like to create a column named with what precedes the = and insert what is afterwards as the value.

let Traces = datatable(EventText:string)
[
"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)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces
| project data = replace_string(extract(@"\(([^\)] )", 1, EventText), "=", ":")
| extend data = parse_json(data)
| evaluate bag_unpack(data)

CodePudding user response:

Some regex (there multiple variations on this) mv-apply bag_unpack plugin

P.S.
Those timestamps are painful to watch.
I see no reason to use anything else but the ISO formats.

let Traces = datatable(EventText:string)
[
"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)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces
| project   kv = extract_all(@"(\w ?)=([^,)]*)", EventText)
| mv-apply  kv on (summarize kv = make_bag(bag_pack(tostring(kv[0]), kv[1])))
| evaluate  bag_unpack(kv)
lockTime previousLockTime releaseTime resourceName sliceNumber totalSlices
02/17/2016 08:40:01 02/17/2016 08:39:01 02/17/2016 08:40:01 PipelineScheduler 23 27
02/17/2016 08:40:00 02/17/2016 08:39:00 02/17/2016 08:40:00 PipelineScheduler 15 27
02/17/2016 08:40:01 02/17/2016 08:39:01 02/17/2016 08:40:01 PipelineScheduler 20 27
02/17/2016 08:41:01 02/17/2016 08:40:01 02/17/2016 08:41:00 PipelineScheduler 22 27
02/17/2016 08:41:00 02/17/2016 08:40:00 02/17/2016 08:41:00 PipelineScheduler 16 27

Fiddle

  • Related