May 10 2021
12:22 AM
- last edited on
Apr 08 2022
10:47 AM
by
TechCommunityAP
May 10 2021
12:22 AM
- last edited on
Apr 08 2022
10:47 AM
by
TechCommunityAP
We are sending syslog messages to Azure Monitor where the message body looks like this:
10.220.200.26,groot-1,"AD\alice",smb2,fs_read_data,ok,123,"/source/folder/file.doc","/target/folder/file.doc"
It's a static CSV format so I can extract the fields relatively easy with
Syslog
| where ProcessName == 'qumulo'
| extend CSVFields = split(SyslogMessage, ',')
| extend ClientIP = tostring(CSVFields[0])
| extend UserID = tostring(CSVFields[1])
| extend Protocol = tostring(CSVFields[2])
| extend Operation = tostring(CSVFields[3])
| extend ResponseCode = tostring(CSVFields[4])
| extend MessageID = tostring(CSVFields[5])
| extend Path1 = tostring(CSVFields[6])
| extend Path2 = tostring(CSVFields[7])
Now I need to extract the file extension from Path1:
The regex to extract ".doc" from Path1 would look like:
\.[^.\/:*?'<>|\r\n]+$
Seems to work Ok (checked on regex101.com).
It would, for example extract ".txt" from /alice/pers.onal/resume.doc.txt
Now I want to build that into the KQL query (but focus here on files Path1 and FileExt1):
Syslog
| where ProcessName == 'qumulo'
| extend Path1 = tostring(CSVFields[6])
| extend FileExt1 = extract(("\.[^.\/:*?'<>|\r\n]+$"),1,tostring(CSVFields[6]))
This does fail:
May 10 2021 01:44 AM
SolutionMay 10 2021 03:44 AM - edited May 10 2021 03:47 AM
@CliveWatson Many thanks, Clive !
This solved it (almost). Unfortunately the Path here is stored with "" in the field:
"/this/file.txt" ... so the extract would deliver .txt"
How can I extend Path1 and trimming out the "" ? I did two extends but isn't there an easier way ?
Syslog
| extend CSVFields = split(SyslogMessage, ',')
| extend Path1tmp = tostring(CSVFields[6])
// now strip out the surrounding ""
| extend Path1 = extract((@'"([^"]*)'),1,Path1tmp)
// now extract the file extention from Path1
| extend FileExt1 = extract((@"\.[^.\/:*?'<>|\r\n]+$"),0,Path1)
Any other idea of how I can extract a string without the surrounding " in one step ?
May 10 2021 05:08 AM
May 10 2021 01:44 AM
Solution