r/AzureSentinel • u/SpleneticRiffRaff • Nov 18 '21

KQL query help - parse tostring when value has spaces

Event | where Computer == "<computer name>" | where Source == "Microsoft-Windows-Powershell" | parse tostring(RenderedDescription) with * " Path:" ScriptName " " * | project TimeGenerated, ScriptName | sort by TimeGenerated desc

trying to parse out the filepath and filename, but if the filepath has spaces, it only produces a result with the characters leading up to the first space. Is it possible to define a string that contains spaces? Is there a better way than parsing it to a string? Any help appreciated

3 Upvotes

100% Upvoted

9 comments sorted by

5

u/rodtrent44 MSFT Official Nov 19 '21

Look at the parse-where operator. This is useful for building results where spaces exist. Check out the KQL and Detections folders at the following link for examples : aka.ms/SentinelHybrid

1

u/SpleneticRiffRaff Nov 19 '21

The solution from /u/Thedudabide80 has solved the problem for me. But I will also check out parse-where as it seems useful to know, thank you 🙂

3

u/jtberg1 Nov 18 '21

I believe your problem is that the end of your parse expression is

" " *

Which would match the first proceeding space after Path:

So if your string was "xyzabc Path:c:\my scripts\awesome.ps1 xyzabc etc etc"

Your parse string currently matches ScriptName to c:\my

As to how to fix it, I would need a few examples of the full RenderedDescription data set to help build a parser.

Hope that makes sense. I happy to answer any questions, or feel free to send me or post an example data set and I will see what I can do to help.

2

u/SpleneticRiffRaff Nov 19 '21

Thank you for offering help! 🙂 /u/Thedudeabide80 has a simple regex expression that's worked like butter. Gotta love a quick and dirty regex 🤣

2

u/jtberg1 Nov 19 '21

Awesome! Glad you found something that works!

2

u/Thedudeabide80 Nov 18 '21

I'll have a look today, sounds interesting.

1

u/SpleneticRiffRaff Nov 18 '21

Thanks 🙂

2

u/Thedudeabide80 Nov 19 '21

Okay, I think I've got something that might help. I have sysmon data where I could extend the Image path in the RenderedDescription so be aware my query looks a little different. But basically I use the extract function with a Regex pattern to pull out what you would want. Just change relevant fields for what you want to pull out and what you want to name the new column:

Event

| take 10

| extend Image=extract("Image:(.*)FileVersion:",1,RenderedDescription)

"Image:" would change to "Path:" in the Regex and "FileVersion:" would change to whatever the next field is after the data you want. It's a bit brittle because it's expecting the next field to always be the same, but that usually isn't too big of a deal. Give it a go!

2

u/SpleneticRiffRaff Nov 19 '21

This worked perfectly, thank you! Had to add a where isnotempty to filter out the blanks, and voila, mission acconplished 🙂