How to Provide mapping to the API before injesting data to the elasticsearch using logstash conf file

[Divyank_Mahalle]

Hi,

I am using Twilio API for ingesting data to elasticsearch.

It is providing Output in the form of JSON.

curl -G https://api.twilio.com/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json \
    -u 'ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:your_auth_token'
EXAMPLE JSON RESPONSE
{
   "first_page_uri": "/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json?Page=0&PageSize=50",
   "previous_page_uri": null, 
   "uri": "/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json", 
   "page_size": 50,
   "usage_records": [
      {
         "category": "shortcodes-customerowned", 
         "count": "0", 
         "price_unit": "usd", 
         "subresource_uris": null, 
         "description": "Customer Owned ShortCodes", 
         "end_date": "2012-09-30",
         "as_of": "2012-09-30T21:59:05+00:00",
         "usage_unit": "shortcodes", 
         "price": "0", 
         "uri": "/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json?Category=shortcodes-customerowned&StartDate=2012-09-01&EndDate=2012-09-30", 
         "account_sid": "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", 
         "usage": "0", 
         "start_date": "2012-09-01", 
         "count_unit": "shortcodes"
      }, 
      {
         "category": "sms-inbound-shortcode", 
         "count": "0", 
         "price_unit": "usd", 
         "subresource_uris": null, 
         "description": "Inbound ShortCode SMS", 
         "end_date": "2012-09-30",
         "as_of": "2012-09-30T21:59:05+00:00",
         "usage_unit": "messages", 
         "price": "0", 
         "uri": "/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json?Category=sms-inbound-shortcode&StartDate=2012-09-01&EndDate=2012-09-30", 
         "account_sid": "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", 
         "usage": "0", 
         "start_date": "2012-09-01", 
         "count_unit": "messages"
      }
      . . .
   ], 
   "next_page_uri": null,
   "page": 0

As the Numeric fields such as "price" , "count" represent as "string" as it is present in "double quotes" while data is being injested using logstash conf fille to elasticsearch due to which I am unable to build visualization on fields such as "price."

How do I change mapping fields present in API.

P.s- I tried changing the mapping of Index post data ingestion, after that field type "price" changed to "long" but docs are not getting updated in TSVB charts.

My conf file-Conf file is working fine.

input {
   exec {
      command => 'curl -XGET https://api.twilio.com/2010-04-01/Accounts/ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Usage/Records/LastMonth.json -u "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX:your_auth_token"'
      interval => 10
      codec => "json"
}}
output {
    stdout {
        codec => rubydebug
    }
    elasticsearch {
       #host ip to be changed to new test system
       hosts => ["<HOSTNAME>"]
       index => "billing"
       user => "<U>"
       password => "< P>"
   }
}

[Divyank_Mahalle]

Hi,

Using filter in logstash conf file Nested JSON is removed.
As API has 50 pages of data ,when the curl command is executed once 50 docs are getting ingested in kibana.

Is there any way to compiled 50 docs consolidated into single doc in kibana.
Any changes required in logstash filter?

Filter-

filter {

  split { field => "[usage_records]" }

  mutate {
    add_field => {
      "category" => "%{[usage_records][category]}"
      "description" => "%{[usage_records][description]}"
      "start_date" => "%{[usage_records][start_date]}"
       "uri" => "%{[usage_records][uri]}"
       "usage" => "%{[usage_records][usage]}"
       "usage_unit" => "%{[usage_records][usage_unit]}"
       "count" => "%{[usage_records][count]}"
       "price_unit" => "%{[usage_records][price_unit]}"
       "api_version" => "%{[usage_records][api_version]}"
       "account_sid" => "%{[usage_records][account_sid]}"
       "end_date" => "%{[usage_records][end_date]}"
       "as_of" => "%{[usage_records][as_of]}"
       "price" => "%{[usage_records][price]}"

    }
    remove_field => [ "[usage_records]" ]
  }

}