Forum Discussion
StefanRadtke
May 10, 2021Copper Contributor
Extracting a file Extension from a syslog message
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:
Failed to parse the query, no additional information is available. If issue persists, please open a support ticket. Request id: 837c1375-48c4-4daa-8012-46cf9b71100d
Any hint for me what I did wrong in the extract part ?
- You didn't "extend" / Split first CSVFields
Syslog
//| where ProcessName == 'qumulo'
| extend CSVFields = split(SyslogMessage, ',')
| extend Path1 = tostring(CSVFields[6])
| extend FileExt1 = extract((@"\.[^.\/:*?'<>|\r\n]+$"),1,Path1)
- CliveWatson
Microsoft
You didn't "extend" / Split first CSVFields
Syslog
//| where ProcessName == 'qumulo'
| extend CSVFields = split(SyslogMessage, ',')
| extend Path1 = tostring(CSVFields[6])
| extend FileExt1 = extract((@"\.[^.\/:*?'<>|\r\n]+$"),1,Path1)- StefanRadtkeCopper Contributor
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 ?
- CliveWatson
Microsoft
| extend Path1 = trim(@"[^\w]+",tostring(Path1)) // remove any non word characters
You can add this as the last line, or maybe integrate it into the extract() ?