Forum Discussion

StefanRadtke's avatar
StefanRadtke
Copper Contributor
May 10, 2021

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)
  • 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)
    • StefanRadtke's avatar
      StefanRadtke
      Copper 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's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft
        | 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() ?

Resources