How to get logstash works

Elastic Stack Logstash
1

my csv file like below

  id,title,original_air_date,production_code,season,number_in_season,number_in_series,us_viewers_in_millions,views,imdb_rating,imdb_votes,image_url,video_url

    10,Homer's Night Out,1990-03-25,7G10,1,10,10,30.3,50816,7.4,1511,http://static-media.fxx.com/img/FX_Networks_-_FXX/305/815/Simpsons_01_10.jpg,http://www.simpsonsworld.com/video/275197507879

    14,Bart Gets an "F",1990-10-11,7F03,2,1,14,33.6,59575,8.2,1638,http://static-media.fxx.com/img/FX_Networks_-_FXX/662/811/bart_gets_F.jpg,http://www.simpsonsworld.com/video/260539459671

    

    26,Homer vs. Lisa and the 8th Commandment,1991-02-07,7F13,2,13,26,26.2,58277,8,1329,http://static-media.fxx.com/img/FX_Networks_-_FXX/930/879/Simpsons_02_13.jpg,http://www.simpsonsworld.com/video/260820547692

    28,"Oh Brother, Where Art Thou?",1991-02-21,7F16,2,15,28,26.8,47426,8.2,1413,http://static-media.fxx.com/img/FX_Networks_-_FXX/372/934/Simpsons_02_16.jpg,http://www.simpsonsworld.com/video/272046659561

    32,Lisa's Substitute,1991-04-25,7F19,2,19,32,17.7,52770,8.5,1684,http://static-media.fxx.com/img/FX_Networks_-_FXX/238/11/Simpsons_02_19.jpg,http://www.simpsonsworld.com/video/288011331912

    37,Mr. Lisa Goes to Washington,1991-09-26,8F01,3,2,37,20.2,52098,7.7,1274,http://static-media.fxx.com/img/FX_Networks_-_FXX/305/315/MrLisaGoestoWashington.jpg,http://www.simpsonsworld.com/video/275196995796

    41,"Like Father, Like Clown",1991-10-24,8F05,3,6,41,20.2,45586,7.7,1262,http://static-media.fxx.com/img/FX_Networks_-_FXX/239/191/Simpsons_03_07.jpg,http://www.simpsonsworld.com/video/256874051703

   
    58,Bart's Friend Falls in Love,1992-05-07,8F22,3,23,58,19.5,48058,7.8,1160,http://static-media.fxx.com/img/FX_Networks_-_FXX/140/895/BartsFriendFallsInLove.jpg,http://www.simpsonsworld.com/video/280393283967

 
    65,Itchy & Scratchy: The Movie,1992-11-03,9F03,4,6,65,20.1,55740,8.2,1293,http://static-media.fxx.com/img/FX_Networks_-_FXX/672/279/Simpsons_04_05.jpg,http://www.simpsonsworld.com/video/278803011830

    69,Lisa's First Word,1992-12-03,9F08,4,10,69,28.6,62070,8.5,1350,http://static-media.fxx.com/img/FX_Networks_-_FXX/252/675/Simpsons_04_10.jpg,http://www.simpsonsworld.com/video/288026691837

    72,Selma's Choice,1993-01-21,9F11,4,13,72,24.5,56396,8,1153,http://static-media.fxx.com/img/FX_Networks_-_FXX/72/899/Simpsons_04_13.jpg,http://www.simpsonsworld.com/video/310386755912



    24,"One Fish, Two Fish, Blowfish, Blue Fish",1991-01-24,7F12,2,11,24,24.2,50206,8.8,1687,http://static-media.fxx.com/img/FX_Networks_-_FXX/671/595/Simpsons_02_11.jpg,http://www.simpsonsworld.com/video/260548675839


    82,Homer's Barbershop Quartet,1993-09-30,9F21,5,1,82,19.9,58390,8.4,1416,http://static-media.fxx.com/img/FX_Networks_-_FXX/698/755/Simpsons_05_01__100677.jpg,http://www.simpsonsworld.com/video/305674307809

    89,Boy-Scoutz 'n the Hood,1993-11-18,1F06,5,8,89,20.1,83238,8.7,1270,http://static-media.fxx.com/img/FX_Networks_-_FXX/280/1003/Simpsons_05_08.jpg,http://www.simpsonsworld.com/video/288056387698

    95,Lisa vs. Malibu Stacy,1994-02-17,1F12,5,14,95,20.5,61715,8.2,1187,http://static-media.fxx.com/img/FX_Networks_-_FXX/435/771/Simpsons_05_14.jpg,http://www.simpsonsworld.com/video/274260035625

            102,Lady Bouvier's Lover,1994-05-12,1F21,5,21,102,15.1,59503,7.5,1014,http://static-media.fxx.com/img/FX_Networks_-_FXX/706/62/Simpsons_05_22.jpg,http://www.simpsonsworld.com/video/305681987583

            107,Itchy & Scratchy Land,1994-10-02,2F01,6,4,107,14.8,72722,8.5,1277,http://static-media.fxx.com/img/FX_Networks_-_FXX/271/1022/Simpsons_06_03__786954.jpg,http://www.simpsonsworld.com/video/283752003577



    120,Homer vs. Patty and Selma,1995-02-26,2F14,6,17,120,18.9,60599,7.9,1006,http://static-media.fxx.com/img/FX_Networks_-_FXX/709/990/Simpsons_06_16.jpg,http://www.simpsonsworld.com/video/305686083529

    125,'Round Springfield,1995-04-30,2F32,6,22,125,12.6,56001,8.3,1084,http://static-media.fxx.com/img/FX_Networks_-_FXX/838/23/Simpsons_06_24__681923.jpg,http://www.simpsonsworld.com/video/275755587777

                           153,Summer of 4 Ft. 2,1996-05-19,3F22,7,25,153,14.7,65847,8.3,1119,http://static-media.fxx.com/img/FX_Networks_-_FXX/1013/746/Simpsons_07_24.jpg,http://www.simpsonsworld.com/video/277013571573

    157,"Burns, Baby Burns",1996-11-17,4F05,8,4,157,12.6,55497,7.7,1079,http://static-media.fxx.com/img/FX_Networks_-_FXX/47/258/Simpsons_08_09.jpg,http://www.simpsonsworld.com/video/299622467514

    160,Lisa's Date with Density,1996-12-15,4F01,8,7,160,,60912,7.8,1005,http://static-media.fxx.com/img/FX_Networks_-_FXX/361/767/Simpsons_08_05__410383.jpg,http://www.simpsonsworld.com/video/306394691862

    167,The Itchy & Scratchy & Poochie Show,1997-02-09,4F12,8,14,167,15.5,58581,7.9,1079,http://static-media.fxx.com/img/FX_Networks_-_FXX/324/459/Simpsons_08_16.jpg,http://www.simpsonsworld.com/video/436278339668

    168,Homer's Phobia,1997-02-16,4F11,8,15,168,15.3,63355,8.9,1666,http://static-media.fxx.com/img/FX_Networks_-_FXX/330/815/Simpsons_08_15.jpg,http://www.simpsonsworld.com/video/436284995621

and my config file like below

input {
 file {
    path => "/Users/liu/work2/esegg/simpsons_episodes.csv"
    start_position => "beginning"
    sincedb_path => "/dev/null"
 }
}
filter {
  csv {

    separator => ","
    quote_char => "`"
    columns => [
      "id","title","original_air_date","production_code","season","number_in_season","number_in_series","us_viewers_in_millions","views","imdb_rating","imdb_votes","image_url","video_url"
    ]
  }

    mutate { 
    convert => {
      "id" => "integer"
      "season" => "integer"
      "number_in_season" => "integer"
      "number_in_series" => "integer"
      "us_viewers_in_millions" => "float"
      "views" => "integer"
      "imdb_rating" => "float"
      "imdb_votes" => "integer"
    }
    gsub => [
      # replace backslashes, question marks, hashes, and minuses
      # with a dot "."
      "title", "[\\&?#-]", "."
    ]
  }
 
  date {
        match => ["original_air_date" , "YYYY-MM-dd"]
			  target => "original_air_date"
        }

}
output {
  elasticsearch {
      hosts => "localhost"
      index => "simpsonlog"
      document_type => "simpson_doc"
   }
  stdout {
     
  }
}

but it didn't work. how to fix it?

2

What the debug logs of Logstash says ? Change the sincedb path to something like /tmp/sincedb and check it to find if the CSV file has been read by Logstash. I tested your configurations albeit a little modification in the input section and it worked.

input {
 file {
    path => "d:\logstash\simpsons_episodes.csv"
    start_position => "beginning"
    sincedb_path => "d:\fd\sincedb"
 }
}

filter {
  csv {

    separator => ","
    quote_char => "`"
    columns => [
      "id","title","original_air_date","production_code","season","number_in_season","number_in_series","us_viewers_in_millions","views","imdb_rating","imdb_votes","image_url","video_url"
    ]
  }

    mutate { 
    convert => {
      "id" => "integer"
      "season" => "integer"
      "number_in_season" => "integer"
      "number_in_series" => "integer"
      "us_viewers_in_millions" => "float"
      "views" => "integer"
      "imdb_rating" => "float"
      "imdb_votes" => "integer"
    }
    gsub => [
      # replace backslashes, question marks, hashes, and minuses
      # with a dot "."
      "title", "[\\&?#-]", "."
    ]
  }
 
  date {
        match => ["original_air_date" , "YYYY-MM-dd"]
			  target => "original_air_date"
        }

}

output {
  elasticsearch {
      hosts => ["localhost:9200"]
      index => "simpsonlog"
      document_type => "simpson_doc"
   }
  stdout 
  {
      codec => rubydebug
  }
}

[Album] Imgur: The magic of the Internet
3

I delete some csv content, cause it exceed the limitation of post,

  {
    "id": 29,
    "title": "Bart's Dog Gets an \"F\"",
    "original_air_date": "1991-03-07",
    "production_code": "7F14",
    "season": 2,
    "number_in_season": 16,
    "number_in_series": 29,
    "us_viewers_in_millions": 23.9,
    "views": 47780,
    "imdb_rating": 7.5,
    "imdb_votes": 1264,
    "image_url": "http://static-media.fxx.com/img/FX_Networks_-_FXX/361/699/bart.jpg",
    "video_url": "http://www.simpsonsworld.com/video/272034883946"
  },

I think the problem is logstash can not store special characters such as
original_air_date // it's a date type, is it the convetion right the one I wrote?
production_code // logstash do knot these strange strings?
title . // special characters almost all happens here such as $\.?
and I don't know how to solve these

the log as below

[2018-11-12T13:57:49,138][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"simpsonlog", :_type=>"simpson_doc", :_routing=>nil}, #<LogStash::Event:0x1441f6c>], :response=>{"index"=>{"_index"=>"simpsonlog", "_type"=>"simpson_doc", "_id"=>"A7x_BmcB9p0UhpMiUcCH", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [production_code]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \"RABF19\""}}}}}
[2018-11-12T13:57:49,138][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"simpsonlog", :_type=>"simpson_doc", :_routing=>nil}, #<LogStash::Event:0x69850d7f>], :response=>{"index"=>{"_index"=>"simpsonlog", "_type"=>"simpson_doc", "_id"=>"BLx_BmcB9p0UhpMiUcCH", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [production_code]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \"SABF09\""}}}}}

[2018-11-12T10:23:30,762][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"simpsonlog", :_type=>"simpson_doc", :_routing=>nil}, #<LogStash::Event:0x69df3c0b>], :response=>{"index"=>{"_index"=>"simpsonlog", "_type"=>"simpson_doc", "_id"=>"tLy7BWcB9p0UhpMiJ7ww", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [original_air_date]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \" Where Art Thou?\"\""}}}}}
[2018-11-12T10:23:30,767][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"simpsonlog", :_type=>"simpson_doc", :_routing=>nil}, #<LogStash::Event:0x406952a9>], :response=>{"index"=>{"_index"=>"simpsonlog", "_type"=>"simpson_doc", "_id"=>"yby7BWcB9p0UhpMiJ7ww", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [original_air_date]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \" Mad\""}}}}}
[2018-11-12T10:23:30,769][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"simpsonlog", :_type=>"simpson_doc", :_routing=>nil}, #<LogStash::Event:0x63323fe>], :response=>{"index"=>{"_index"=>"simpsonlog", "_type"=>"simpson_doc", "_id"=>"0Ly7BWcB9p0UhpMiJ7ww", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [original_air_date]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: \" (Annoyed Grunt)-bot\"\""}}}}}
4

For original_air_date you need to convert it to matching date pattern in the mutate section. The same is true for production_code, Probably, you need to convert it to String in the mutate section.

5

there's a filter , it that right?

  date {
        match => ["original_air_date" , "YYYY-MM-dd"]
			  target => "original_air_date"
        }
6

Yes its there, I overlooked it earlier. Try to remove the special characters using gsub which you have already used.

gsub => [
        "message", "\r\n", "",
        "message", "\\", ""
    ]

Also can you post the current logstash config along with trimmed CSV file.

7

config:

input {
 file {
    path => "/Users/liuteamyi/work2/esegg/simpsons_episodes.csv"
    start_position => "beginning"
    sincedb_path => "/dev/null"
 }
}
filter {
  csv {

    separator => ","
    quote_char => "`"
    columns => [
      "id","title","original_air_date","production_code","season","number_in_season","number_in_series","us_viewers_in_millions","views","imdb_rating","imdb_votes","image_url","video_url"
    ]
  }

    mutate { 
      convert => {
        "id" => "integer"
        "season" => "integer"
        "number_in_season" => "integer"
        "number_in_series" => "integer"
        "us_viewers_in_millions" => "float"
        "views" => "integer"
        "imdb_rating" => "float"
        "imdb_votes" => "integer"
    
      }
      gsub => [
        # replace backslashes, question marks, hashes, and minuses
        # with a dot "."
        "title", "[\\&?#-]", "."
        "message", "\r\n", ""
        "message", "\\", ""
      ]
  } 
 


  date {
        match => ["original_air_date" , "YYYY-MM-dd"]
			  target => "original_air_date"
        }
 

  
}
output {
  elasticsearch {
      hosts => "localhost"
      index => "simpsonlog"
      document_type => "simpson_doc"
   }
  stdout {
     
  }
}

csv

id,title,original_air_date,production_code,season,number_in_season,number_in_series,us_viewers_in_millions,views,imdb_rating,imdb_votes,image_url,video_url

10,Homer's Night Out,1990-03-25,7G10,1,10,10,30.3,50816,7.4,1511,http://static-media.fxx.com/img/FX_Networks_-_FXX/305/815/Simpsons_01_10.jpg,http://www.simpsonsworld.com/video/275197507879

12,Krusty Gets Busted,1990-04-29,7G12,1,12,12,30.4,62561,8.3,1716,http://static-media.fxx.com/img/FX_Networks_-_FXX/245/843/Simpsons_01_12.jpg,http://www.simpsonsworld.com/video/288019523914

14,Bart Gets an "F",1990-10-11,7F03,2,1,14,33.6,59575,8.2,1638,http://static-media.fxx.com/img/FX_Networks_-_FXX/662/811/bart_gets_F.jpg,http://www.simpsonsworld.com/video/260539459671

17,Two Cars in Every Garage and Three Eyes on Every Fish,1990-11-01,7F01,2,4,17,26.1,64959,8.1,1457,http://static-media.fxx.com/img/FX_Networks_-_FXX/660/859/Simpsons_02_01.jpg,http://www.simpsonsworld.com/video/260537411822

19,Dead Putting Society,1990-11-15,7F08,2,6,19,25.4,50691,8,1366,http://static-media.fxx.com/img/FX_Networks_-_FXX/662/811/Simpsons_02_08.jpg,http://www.simpsonsworld.com/video/260539459670

21,Bart the Daredevil,1990-12-06,7F06,2,8,21,26.2,57605,8.4,1522,http://static-media.fxx.com/img/FX_Networks_-_FXX/662/811/Simpsons_02_06.jpg,http://www.simpsonsworld.com/video/260539459702

23,Bart Gets Hit by a Car,1991-01-10,7F10,2,10,23,24.8,56486,7.8,1340,http://static-media.fxx.com/img/FX_Networks_-_FXX/673/547/bart_gets_hit_by_car.jpg,http://www.simpsonsworld.com/video/260550723760

26,Homer vs. Lisa and the 8th Commandment,1991-02-07,7F13,2,13,26,26.2,58277,8,1329,http://static-media.fxx.com/img/FX_Networks_-_FXX/930/879/Simpsons_02_13.jpg,http://www.simpsonsworld.com/video/260820547692

28,"Oh Brother, Where Art Thou?",1991-02-21,7F16,2,15,28,26.8,47426,8.2,1413,http://static-media.fxx.com/img/FX_Networks_-_FXX/372/934/Simpsons_02_16.jpg,http://www.simpsonsworld.com/video/272046659561

30,Old Money,1991-03-28,7F17,2,17,30,21.2,44331,7.6,1243,http://static-media.fxx.com/img/FX_Networks_-_FXX/418/939/old_money.jpg,http://www.simpsonsworld.com/video/263504963955

32,Lisa's Substitute,1991-04-25,7F19,2,19,32,17.7,52770,8.5,1684,http://static-media.fxx.com/img/FX_Networks_-_FXX/238/11/Simpsons_02_19.jpg,http://www.simpsonsworld.com/video/288011331912
8

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