Logstash Extract Elasticsearch Nested Fields

tusharnemade · June 14, 2021, 3:25am

Hello Badger

Thanks for your response.

Let me put it again properly ...

my logstash conf file is below one :

My Input if Elasticsearch Index version 7.8.0

input {
  elasticsearch {
    hosts => "10.10.10.10:9200"
    query => '{"_source" : ["userId", "timeStamp", "backupSettings.backgroundUploading", "backupSettings.connectionType", "backupSettings.contactBackup", "backupSettings.contactPermission", "backupSettings.photoBackup", "backupSettings.photoQuality", "backupSettings]storagePermission", "backupSettings.videoBackup", "appEvents.attribute_num.filesPendingForUpload","appEvents.attribute_num.filesUploadedSinceLastEvent"],"query" : { "match_all": {} }}'
    size => 10000
    scroll => "5m"
    index => "backupindex"
  }
}

Now I have filter added to take multi-nested fields into single new field.

filter {
    mutate {
        add_field => { "filesPendingForUpload" => "%{[[appEvents][attribute_num]][filesPendingForUpload]}" }
                add_field => { "filesUploadedSinceLastEvent" => "%{[[appEvents][attribute_num]][filesUploadedSinceLastEvent]}" }
    }
}

Now output goes to CSV File :

output {
  csv {
    fields => ["userId", "timeStamp", "[backupSettings][backgroundUploading]", "[backupSettings][connectionType]", "[backupSettings][contactBackup]", "[backupSettings][contactPermission]", "[backupSettings][photoBackup]", "[backupSettings][photoQuality]", "[backupSettings][storagePermission]", "[backupSettings][videoBackup]", "[filesPendingForUpload]", "[filesUploadedSinceLastEvent]"]
    path => "/tmp/exp.csv"
  }
}

tusharnemade · June 14, 2021, 5:21am

Still the implementation is unclear from my side in above explanation ?

tusharnemade · June 14, 2021, 10:03am

Hi

Could some one please help me ..

IF still my description of my issue is incomplete , please do let me know .. I will fill up the gap...

Badger · June 14, 2021, 1:47pm

tusharnemade:

add_field => { "filesPendingForUpload" => "%{[[appEvents][attribute_num]][filesPendingForUpload]}" }
                add_field => { "filesUploadedSinceLastEvent" => "%{[[appEvents][attribute_num]][filesUploadedSinceLastEvent]}" }

The output suggests that these are not working. Why do you have a second set of square brackets around [appEvents][attribute_num]?

tusharnemade · June 14, 2021, 1:53pm

Hi Badger

I tried with below options :

add_field => { "filesPendingForUpload" => "%{[appEvents][attribute_num][filesPendingForUpload]}" }
                add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][attribute_num][filesUploadedSinceLastEvent]}" }

add_field => { "filesPendingForUpload" => "%{[[appEvents][attribute_num]][filesPendingForUpload]}" }
                add_field => { "filesUploadedSinceLastEvent" => "%{[[appEvents][attribute_num]][filesUploadedSinceLastEvent]}" }

add_field => { "filesPendingForUpload" => "%{[appEvents][[attribute_num][filesPendingForUpload]]}" }
                add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][[attribute_num][filesUploadedSinceLastEvent]]}" }

I came across some forums : for multi nested path use [] double square brackets ...

I tried above all 3 options , but none worked...

Could you please help me to achieve data of , below fields in logstash CSV output plugin. How to mention such fields ..

appEvents.attribute_num.filesPendingForUpload 
appEvents.attribute_num.filesUploadedSinceLastEvent

tusharnemade · June 14, 2021, 4:52pm

I also tried below :

specifying "[appEvents][0][attribute_num][filesPendingForUpload]"

input {
  elasticsearch {
    hosts => "10.10.101.1:9200"
    size => 10000
    index => "backupindex"
  }
}



output {
  csv {
    fields => ["userId", "timeStamp", "[backupSettings][backgroundUploading]", "[backupSettings][connectionType]", "[backupSettings][contactBackup]", "[backupSettings][contactPermission]", "[backupSettings][photoBackup]", "[backupSettings][photoQuality]", "[backupSettings][storagePermission]", "[backupSettings][videoBackup]", "[appEvents][0][attribute_num][filesPendingForUpload]"]
    path => "/tmp/exp.csv"
  }
}

specifying "[appEvents][attribute_num][filesPendingForUpload]"

Both did not helped me ... IN CSV , field was blank.

Badger · June 14, 2021, 4:54pm

Can you replace your output with

output { stdout { codec => rubydebug } }

and show us what [appEvents] looks like?

tusharnemade · June 15, 2021, 3:57am

Okay Sure ..

tusharnemade · June 15, 2021, 5:05am

{
         "timeStamp" => 1623203744873,
        "@timestamp" => 2021-06-15T04:55:11.613Z,
          "@version" => "1",
            "userId" => "c0135af5feb34a77baee92b7551c4d06",
         "appEvents" => [
        [0] {
            "attribute_num" => {
                "filesUploadedSinceLastEvent" => 0,
                      "filesPendingForUpload" => 2
            }
        }
    ],
    "backupSettings" => {
             "connectionType" => "WLC",
        "backgroundUploading" => "Y",
          "contactPermission" => "Y",
                "photoBackup" => "CAM",
               "photoQuality" => "Original",
                "videoBackup" => "OFF",
              "contactBackup" => "Y",
          "storagePermission" => "Y"
    }
}

Badger · June 15, 2021, 2:22pm

[appEvents] is an array, so you should be using "[appEvents][0][attribute_num][filesPendingForUpload]"

tusharnemade · June 16, 2021, 2:41am

Hello Badger

I have already tried using this ...

Could you please have a look over this post ..

Badger · June 16, 2021, 3:04am

I do not know what to say. Both the mutate+add_field and the csv output will event.get "[appEvents][0][attribute_num][filesPendingForUpload]". If that field exists it should be expanded.

Are you getting this for every event?

tusharnemade · June 16, 2021, 3:11am

Hi Badger

I did not used mutate this time ...

input {
  elasticsearch {
    hosts => "10.10.101.10:9200"
    size => 10000
    index => "backup"
  }
}



output {
  csv {
    fields => ["userId", "timeStamp", "[backupSettings][backgroundUploading]", "[backupSettings][connectionType]", "[backupSettings][contactBackup]", "[backupSettings][contactPermission]", "[backupSettings][photoBackup]", "[backupSettings][photoQuality]", "[backupSettings][storagePermission]", "[backupSettings][videoBackup]", "[appEvents][0][attribute_num][filesUploadedSinceLastEvent]", "[appEvents][0][attribute_num][filesPendingForUpload]"]
    path => "/tmp/exp.csv"
  }
}

Output :

6018a66855b6471ebf976b2d551c9731,1623241661046,Y,WLC,Y,Y,CAM,Original,Y,OFF,,

You can see last both fields are empty ...

they should contain data

tusharnemade · June 16, 2021, 3:25am

Could you please help me with what I am missing because of which my fields are not getting captured in csv file..

tusharnemade · June 16, 2021, 3:32am

There is one hope now ....

I used mutate fields ... and i got the record successfully.

Only problem is now if event is empty .. then it prints entire mutate field.

Could you please suggest ways to avoid this...

input {
  elasticsearch {
    hosts => "10.10.100.10:9200"
    query => '{"_source" : ["userId", "timeStamp", "backupSettings.backgroundUploading", "backupSettings.connectionType", "backupSettings.contactBackup", "backupSettings.contactPermission", "backupSettings.photoBackup", "backupSettings.photoQuality", "backupSettings.storagePermission", "backupSettings.videoBackup", "appEvents.attribute_num.filesPendingForUpload","appEvents.attribute_num.filesUploadedSinceLastEvent"],"query" : { "match_all": {} }}'
    size => 10000
    scroll => "5m"
    index => "backup"
  }
}



filter {
    mutate {
        add_field => { "filesPendingForUpload" => "%{[appEvents][0][attribute_num][filesPendingForUpload]}" }
        add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}" }
    }
}

output {
  csv {
    fields => ["userId", "timeStamp", "[backupSettings][backgroundUploading]", "[backupSettings][connectionType]", "[backupSettings][contactBackup]", "[backupSettings][contactPermission]", "[backupSettings][photoBackup]", "[backupSettings][photoQuality]", "[backupSettings][storagePermission]", "[backupSettings][videoBackup]", "filesPendingForUpload", "filesUploadedSinceLastEvent"]
    path => "/tmp/exp.csv"
  }
}

6018a66855b6471ebf976b2d551c9731,1623241661046,Y,WLC,Y,Y,CAM,Original,Y,OFF,8,173
6018a66855b6471ebf976b2d551c9731,1623256154896,,,,,,,,,%{[appEvents][0][attribute_num][filesPendingForUpload]},%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}
6018a66855b6471ebf976b2d551c9731,1623254680762,,,,,,,,,%{[appEvents][0][attribute_num][filesPendingForUpload]},%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}

Badger · June 16, 2021, 3:44am

Possibly use a prune filter where the default value for blacklist_names will delete fields where a sprintf reference failed. I think the unresolved sprintf reference will be top-level so then you should get an empty field.

tusharnemade · June 16, 2021, 3:49am

Thank You for your response.

I will check and reply in here ...

tusharnemade · June 16, 2021, 11:30am

Hello Badger

I tried below ways .. but it did not helped me ...
I am sure , my writing of prune filter is not correct .. .hence its not working properly as expected ..

Could you please help me with its syntax to be mentioned ...

filter {
    mutate {
        add_field => { "filesPendingForUpload" => "%{[appEvents][0][attribute_num][filesPendingForUpload]}" }
        add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}" }
    }
    prune {
        blacklist_names => [ "filesPendingForUpload", "filesUploadedSinceLastEvent" ]
    }
}

filter {
    mutate {
        add_field => { "filesPendingForUpload" => "%{[appEvents][0][attribute_num][filesPendingForUpload]}" }
        add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}" }
    }
    prune {
        blacklist_names => [ "filesPendingForUpload", "filesUploadedSinceLastEvent" ]
        blacklist_values => [ "" , "" ]
    }
}

tusharnemade · June 16, 2021, 1:35pm

I also tried below :

filter {
    mutate {
        add_field => { "filesPendingForUpload" => "%{[appEvents][0][attribute_num][filesPendingForUpload]}" }
        add_field => { "filesUploadedSinceLastEvent" => "%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}" }
    }
    prune {
        blacklist_values => [ "filesPendingForUpload" ,"" , "filesUploadedSinceLastEvent", "" ]
    }
}

tusharnemade · June 16, 2021, 1:58pm

I would like to high light in here that for some documents we have this field missing ....

for example below is the set of document does not have
[appEvents][0][attribute_num][filesUploadedSinceLastEvent] and [appEvents][0][attribute_num][filesPendingForUpload]

{
        "userId" => "6018a66855b6471ebf976b2d551c9731",
    "@timestamp" => 2021-06-16T13:40:32.584Z,
      "@version" => "1",
     "timeStamp" => 1623256154896
}

In such cases , output is like below in csv file

6018a66855b6471ebf976b2d551c9731,1623256154896,,,,,,,,,%{[appEvents][0][attribute_num][filesPendingForUpload]},%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}
6018a66855b6471ebf976b2d551c9731,1623254680762,,,,,,,,,%{[appEvents][0][attribute_num][filesPendingForUpload]},%{[appEvents][0][attribute_num][filesUploadedSinceLastEvent]}