SOLVED

Extracting a file Extension from a syslog message

New Contributor

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 ?
 
3 Replies
best response confirmed by StefanRadtke (New Contributor)
Solution
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 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 ? 

| 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() ?