Nested Json with ruby filter

chrigui94 · April 16, 2018, 1:23pm

I want to index this JSON file :

{"environment":"urban","envType":"outdoor","positionNumber":1,"frameIndex":15,"fcntUp":22324,"numberOfGateways":3,"numberOfTimestamps":0,"serverTimestamp":"23:44:36","lnsAppEui":"4883C7DF30040000","lnsDevEui":"4883C7DF3004223A","lnsNetId":"000007","lnsServerTimestamp":"27/10/2017 23:44:36","gtwFrequency":868.1,"gtwDataRate":"SF10_BW_125","estimatedLongitudeWGS84":null,"estimatedLatitudeWGS84":null,"deviceLongitudeWGS84":2.289511063734628,"deviceLatitudeWGS84":48.88373587201055,"spreadingFactor":10,"accuracy":null,"estimatedAccuracy":null,"averageISD":null,"hdopDevice":null,"hdopEstimated":null,"gateways":[{"antennaLongitude":2.2957080835783388,"rssi":-107.7376019773414,"rssiStandardDeviation":-95.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.89112433031253,"gatewayTimestamp":"27/10/2017 23:44:31","snr":-12.5,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-95.0,"gatewayID":"M15279"},{"antennaLongitude":2.2932473657963164,"rssi":-107.1244260279434,"rssiStandardDeviation":-103.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.88193968215203,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-2.0,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-103.0,"gatewayID":"M15073"},{"antennaLongitude":2.3014574627818787,"rssi":-104.59612087980607,"rssiStandardDeviation":-94.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.87684647097623,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-10.2,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-94.0,"gatewayID":"M15913"}],"loraDevAddr":"0F127D87"}

My goal is to map gateways positions on the kibana Map (position = [antennaLongitude, antennaLatitude]

I changed my template by adding a nested field here is my template:

    {
      "template" : "logstash-*",
      "version" : 50001,
      "settings" : {
        "index.refresh_interval" : "5s",
    	"index.mapping.ignore_malformed": true
      },
      "mappings" : {
        "_default_" : {
          "_all" : {"enabled" : true, "norms" : false},
          "dynamic_templates" : [ {
            "message_field" : {
              "path_match" : "message",
              "match_mapping_type" : "string",
              "mapping" : {
                "type" : "text",
                "norms" : false
              }
            }
          }, {
            "string_fields" : {
              "match" : "*",
              "match_mapping_type" : "string",
              "mapping" : {
                "type" : "text", "norms" : false,
                "fields" : {
                  "keyword" : { "type": "keyword", "ignore_above": 256 }
                }
              }
            }
          } ],
          "properties" : {
            "@timestamp": { "type": "date", "include_in_all": false },
            "@version": { "type": "keyword", "include_in_all": false },
            "geoip"  : {
               "type" : "geo_point" 
            },
    		"geoip_Dev": {
                "type" : "geo_point" 
              },
    		  "geoip_gwy": {
                "type" : "geo_point" 
              },
    		  "gateways":{ 	   
    		        "type": "nested",
    			    "properties": {
    						"antennaLatitude":{"type":"float"},
    						"antennaLongitude":{"type":"float"},
                "position": {"type": "geo_point"}
                
                        }
    		  }
                }
              }
          }
    }

and Here is my logstash conf file

    input {
    file {
        type => "gtw"
        path => ["D:/Users/G361164/Desktop/test/*STATIC*.json"]
        start_position => beginning
        # to read from the beginning of file
        sincedb_path => "/dev/null"
    	codec => json 
          }
    }
    filter{

    if [type] == "gtw"{

    mutate {
          add_field => { "[gateways]" => "%{[gateways]}" }
          #add_field => { "[gateways][position][lon]" => "%{[gateways][antennaLongitude]}" }
        }
       
    ruby {
       
      code => "
             event.get('gateways').each { |x|
                   event.set([gateways.position][lat] , event.get(x['antennaLongitude'])) 
    			    event.set([gateways.position][lon] , event.get(x['antennaLongitude'])) 
             }
    	   "
    }
    	
    }

    }

    output { 
    elasticsearch {  
      index => "logstash-gatewaysss222222%{+YYYY.MM.dd}"
      template => "D:/Users/G361164/Desktop/Demo/template/template_geoip.json"
      template_name => "gateways_positions"
      }
      stdout { codec => rubydebug }
    }

I received this error while indexing:
failed to execute bulk item (index) BulkShardRequest [[logstash-gatewaysss2222222018.04.16][0]] containing [index {[logstash-gatewaysss2222222018.04.16][gtw][AWLOld1-1vhpJFP5cFy-], source[n/a, actual length: [2.7kb], max length: 2kb]}]
org.elasticsearch.index.mapper.MapperParsingException: object mapping for [gateways] tried to parse field [null] as object, but found a concrete value

Can help me to attend my goal by adding a geo_point varibale "position" inside each gateway and contains latitude and longitude !!?

guyboertje · April 16, 2018, 3:23pm

I ran your ruby filter code as is and got a _rubyexception tag. I used the generator input for troubleshooting.

input {
  generator {
    message => '{"environment":"urban","envType":"outdoor","positionNumber":1,"frameIndex":15,"fcntUp":22324,"numberOfGateways":3,"numberOfTimestamps":0,"serverTimestamp":"23:44:36","lnsAppEui":"4883C7DF30040000","lnsDevEui":"4883C7DF3004223A","lnsNetId":"000007","lnsServerTimestamp":"27/10/2017 23:44:36","gtwFrequency":868.1,"gtwDataRate":"SF10_BW_125","estimatedLongitudeWGS84":null,"estimatedLatitudeWGS84":null,"deviceLongitudeWGS84":2.289511063734628,"deviceLatitudeWGS84":48.88373587201055,"spreadingFactor":10,"accuracy":null,"estimatedAccuracy":null,"averageISD":null,"hdopDevice":null,"hdopEstimated":null,"gateways":[{"antennaLongitude":2.2957080835783388,"rssi":-107.7376019773414,"rssiStandardDeviation":-95.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.89112433031253,"gatewayTimestamp":"27/10/2017 23:44:31","snr":-12.5,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-95.0,"gatewayID":"M15279"},{"antennaLongitude":2.2932473657963164,"rssi":-107.1244260279434,"rssiStandardDeviation":-103.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.88193968215203,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-2.0,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-103.0,"gatewayID":"M15073"},{"antennaLongitude":2.3014574627818787,"rssi":-104.59612087980607,"rssiStandardDeviation":-94.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.87684647097623,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-10.2,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-94.0,"gatewayID":"M15913"}],"loraDevAddr":"0F127D87"}'
    count => 1
  }
}

filter {
  json {
    source => "message"
  }
  ruby {
    code => 'event.get("gateways").each { |x|
                 event.set([gateways.position][lat] , event.get(x["antennaLongitude"]))
            event.set([gateways.position][lon] , event.get(x["antennaLongitude"]))
           }
    '
  }
}

output {
  stdout { codec => rubydebug }
}

Reasons:

The first argument to Event set method must be a string.
You doing a double lookup in the second argument, you already have a reference to the inner Hash object as x
You have "antennaLongitude" for both lat and lon
You are using the dot notation instead of the LS [][][] syntax for nested fields.

Does this work for you?

input {
  generator {
    message => '{"environment":"urban","envType":"outdoor","positionNumber":1,"frameIndex":15,"fcntUp":22324,"numberOfGateways":3,"numberOfTimestamps":0,"serverTimestamp":"23:44:36","lnsAppEui":"4883C7DF30040000","lnsDevEui":"4883C7DF3004223A","lnsNetId":"000007","lnsServerTimestamp":"27/10/2017 23:44:36","gtwFrequency":868.1,"gtwDataRate":"SF10_BW_125","estimatedLongitudeWGS84":null,"estimatedLatitudeWGS84":null,"deviceLongitudeWGS84":2.289511063734628,"deviceLatitudeWGS84":48.88373587201055,"spreadingFactor":10,"accuracy":null,"estimatedAccuracy":null,"averageISD":null,"hdopDevice":null,"hdopEstimated":null,"gateways":[{"antennaLongitude":2.2957080835783388,"rssi":-107.7376019773414,"rssiStandardDeviation":-95.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.89112433031253,"gatewayTimestamp":"27/10/2017 23:44:31","snr":-12.5,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-95.0,"gatewayID":"M15279"},{"antennaLongitude":2.2932473657963164,"rssi":-107.1244260279434,"rssiStandardDeviation":-103.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.88193968215203,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-2.0,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-103.0,"gatewayID":"M15073"},{"antennaLongitude":2.3014574627818787,"rssi":-104.59612087980607,"rssiStandardDeviation":-94.0,"elapsedTimeSince1PPS":0.0,"antennaID":"1","antennaLatitude":48.87684647097623,"gatewayTimestamp":"27/10/2017 23:44:32","snr":-10.2,"frequencyOffset":0,"lnsServerTimestamp":"27/10/2017 23:44:36","rssiSignal":-94.0,"gatewayID":"M15913"}],"loraDevAddr":"0F127D87"}'
    count => 1
  }
}

filter {
  json {
    source => "message"
  }
  ruby {
    code => '
      gateways_size = event.get("[gateways]").size
      gateways_size.times do |index|
        event.set("[gateways][#{index}][position][lat]", event.get("[gateways][#{index}][antennaLatitude]"))
        event.set("[gateways][#{index}][position][lon]", event.get("[gateways][#{index}][antennaLongitude]"))
      end
    '
  }
}

output {
  stdout { codec => rubydebug }
}

chrigui94 · April 16, 2018, 3:45pm

Thank you so much for your help!
It helps but I still can't see positions on kibana !!?

guyboertje · April 16, 2018, 4:56pm

So you don't get the Elasticsearch mapping error?

chrigui94 · April 16, 2018, 5:09pm

No I dont have error now I can see the position added to each gateways but I can't see it on kibana map

guyboertje · April 17, 2018, 7:31am

Kibana is not my speciality.

I think you should mark this question as fixed and post a new question about the Kibana part - perhaps in the Kibana forum even.

chrigui94 · April 17, 2018, 8:52am

I don't understand what is the benefit of the nested field "gateways" in the logstash ? how can I exploit it ?

guyboertje · April 17, 2018, 9:48am

I don't understand it either but it is in the original data so unless you transform the event to suit its going to be there as an array of objects.

Think about what visualizations you want to see about the real world events that the data is describing then think about how the data needs to change to give you the visualizations you want.

For instance, is each gateway the actual object of interest? If so then you need the split filter to create a document for each gateway and remove or transform the fields that come from the outer section.

chrigui94 · April 17, 2018, 9:57am

I avoided split filter because it duplicates the same document for each gateway and for that I found that the type nested can contains an array of gateways. Your solution helps me add a geo_point position in each gateway but when I go to see it in kibana it's not recognised as geopoint and I can't found a variable called "gateways.position". I tried to convert each antennaLangitude and antennaLatitude to float the result is to variables (lat, lont) but no geo_point

chrigui94 · April 17, 2018, 10:29am

Maybe the problem is that the name "gateways" is the same in the json input and in the template. if I change the template to be:
{
"template" : "logstash-",
"version" : 50001,
"settings" : {
"index.refresh_interval" : "5s",
"index.mapping.ignore_malformed": true
},
"mappings" : {
"default" : {
"_all" : {"enabled" : true, "norms" : false},
"dynamic_templates" : [ {
"message_field" : {
"path_match" : "message",
"match_mapping_type" : "string",
"mapping" : {
"type" : "text",
"norms" : false
}
}
}, {
"string_fields" : {
"match" : "",
"match_mapping_type" : "string",
"mapping" : {
"type" : "text", "norms" : false,
"fields" : {
"keyword" : { "type": "keyword", "ignore_above": 256 }
}
}
}
} ],
"properties" : {
"@timestamp": { "type": "date", "include_in_all": false },
"@version": { "type": "keyword", "include_in_all": false },
"geoip" : {
"type" : "geo_point"
},
"geoip_Dev": {
"type" : "geo_point"
},
"geoip_gwy": {
"type" : "geo_point"
},
"gtwys":{
"type": "nested",
"properties": {
"antennaLatitude":{"type":"float"},
"antennaLongitude":{"type":"float"},
"position": {"type": "geo_point"}

I changed "gateways" to "gtwys" how can I map the data to this new template to found a result like:
[gtwys][position] == [gateways ][position] ??

guyboertje · April 17, 2018, 10:32am

in the ruby filter change the event.set to event.set("[gtwys][#{index}][position][lat]", ....)

chrigui94 · April 17, 2018, 12:57pm

@guyboertje After doing that I received this: ttt

I want it like the format of geoip_Dev for example:
gtwys.0.position{
lat: ""
lon:""
}
gtwys.1.position{
lat: ""
lon:""
}

system · May 15, 2018, 12:57pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.