Autodetect_column_names in condition

INS · February 24, 2023, 1:16pm

Hi

Maybe You have idea why in this case autodetect_column_names doesn't work as independent.

input:

"CLLI","SWREL","RPTDATE","RPTIME","TZ","RPTTYPE","RPTPD","IVALDATE","IVALSTART","IVALEND","NUMENTIDS"
"wifi06","EAGLE 47.0.0.0.0-79.13.0","2023-02-24","13:24:23","CET ","AVAILABILITY MEASUREMENTS ON LINK","LAST","2023-02-24","13:00:00","13:15:00",285

"STATUS","LSN","LOC","LINK","LNKTYPE","NEARMGIH","FARMGINH","NMDCLFLR","DRDCLFLR","SURCVERR","DRLKINHB","NDCFLABN","NDCLFSYNC","NDCFLXDA","NDCFLXER","NDCFLXDC","NDCLFALP","NDCLFINTR","NMFEPRO","NMLCLPRO","DRFEPRO","DRLCLPRO","SUSRECVD","SUSTRAN","PCRN1N2EXC"

"K","eip61sccip","1207","A  ","IPVL",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
"K","eip05sccip","1207","B  ","IPVL",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
"K","wifi41ip","1208","B  ","IPVL",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
"K","wifi41ip","1211","A  ","IPVL",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

input {
    file {
        mode => read
        path => "/opt/data/input/new_test1.csv"
        sincedb_path => "/dev/null"
        start_position => beginning
        file_completed_action => "log"
        file_completed_log_path => "/opt/data/logstash_files/fin_eir.log"
    }
}


filter{
        ruby { code => 'event.set("[@metadata][fields]", 1 + event.get("message").count(","))' }



        if [@metadata][fields] == 11 {
     csv {
         separator => ","
         columns => ["CLLI","SWREL","RPTDATE","RPTIME","TZ","RPTTYPE","RPTPD","IVALDATE","IVALSTART","IVALEND","NUMENTIDS"]

         }
#        ruby { code => "event.set('event_timestamp', event.get('RPTDATE') + ' ' + event.get('RPTIME'))"}
#        date {
#               match => ["event_timestamp", "dd-MM-yyyy H:m:s.SSS", "yyyy-MM-dd H:m:s.SSS", "ISO8601"]
#               timezone => "Europe/Warsaw"
#               target => "@timestamp"}
} else {
     csv {
         separator => ","

         autodetect_column_names => true
         skip_empty_columns => true
         skip_header => true
        }
}

mutate {
                        remove_field => ["message"]


}

}

output {


 stdout { codec => rubydebug }

}

output:

[INFO ] 2023-02-24 13:30:02.708 [Agent thread] agent - Pipelines running {:count=>1, :running_pipelines=>[:stp_test], :non_running_pipelines=>[]}
[WARN ] 2023-02-24 13:30:03.152 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"\"STATUS\",\"LSN\",\"LOC\",\"LINK\",\"LNKTYPE\",\"NEARMGIH\",\"FARMGINH\",\"NMDCLFLR\",\"DRDCLFLR\",\"SURCVERR\",\"DRLKINHB\",\"NDCFLABN\",\"NDCLFSYNC\",\"NDCFLXDA\",\"NDCFLXER\",\"NDCFLXDC\",\"NDCLFALP\",\"NDCLFINTR\",\"NMFEPRO\",\"NMLCLPRO\",\"DRFEPRO\",\"DRLCLPRO\",\"SUSRECVD\",\"SUSTRAN\",\"PCRN1N2EXC\"", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.154 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.156 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"\"K\",\"eip61sccip\",\"1207\",\"A  \",\"IPVL\",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.158 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"\"K\",\"eip05sccip\",\"1207\",\"B  \",\"IPVL\",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.160 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"\"K\",\"wifi41ip\",\"1208\",\"B  \",\"IPVL\",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.163 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"\"K\",\"wifi41ip\",\"1211\",\"A  \",\"IPVL\",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}
[WARN ] 2023-02-24 13:30:03.164 [[stp_test]>worker0] csv - Error parsing csv {:field=>"message", :source=>"", :exception=>#<NoMethodError: undefined method `empty?' for nil:NilClass>}


{
       "IVALEND" => "13:15:00",
      "@version" => "1",
          "CLLI" => "stp06",
       "RPTTYPE" => "AVAILABILITY MEASUREMENTS ON LINK",
         "RPTPD" => "LAST",
         "SWREL" => "EAGLE 47.0.0.0.0-79.13.0",
    "@timestamp" => 2023-02-24T13:01:04.567304Z,
       "RPTDATE" => "2023-02-24",
        "RPTIME" => "13:24:23",
     "IVALSTART" => "13:00:00",
     "NUMENTIDS" => "285",
          "host" => "0.0.0.0",
            "TZ" => "CET ",
      "IVALDATE" => "2023-02-24",
          "path" => "/opt/data/input/new_test1.csv"
}
{
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
    "@timestamp" => 2023-02-24T13:01:04.568660Z,
          "path" => "/opt/data/input/new_test1.csv"
}
{
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
    "@timestamp" => 2023-02-24T13:01:04.569210Z,
          "path" => "/opt/data/input/new_test1.csv"
}
{
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
    "@timestamp" => 2023-02-24T13:01:04.567866Z,
          "path" => "/opt/data/input/new_test1.csv"
}
{
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
    "@timestamp" => 2023-02-24T13:01:04.568395Z,
          "path" => "/opt/data/input/new_test1.csv"
}
{
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
    "@timestamp" => 2023-02-24T13:01:04.568938Z,
          "path" => "/opt/data/input/new_test1.csv"

INS · February 28, 2023, 6:48pm

@Badger Can You keep an eye on this case?

Badger · February 28, 2023, 8:00pm

I would suggest moving the remove_field => [ "message" ] to the two csv filters so that [message] is retained when it fails to parse.

INS · February 28, 2023, 8:54pm

so these below names from message should be column name


{
    "@timestamp" => 2023-02-28T20:51:31.095932Z,
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
          "path" => "/opt/data/input/new_test1.csv",
       "message" => "\"STATUS\",\"LSN\",\"LOC\",\"LINK\",\"LNKTYPE\",\"NEARMGIH\",\"FARMGINH\",\"NMDCLFLR\",\"DRDCLFLR\",\"SURCVERR\",\"DRLKINHB\",\"NDCFLABN\",\"NDCLFSYNC\",\"NDCFLXDA\",\"NDCFLXER\",\"NDCFLXDC\",\"NDCLFALP\",\"NDCLFINTR\",\"NMFEPRO\",\"NMLCLPRO\",\"DRFEPRO\",\"DRLCLPRO\",\"SUSRECVD\",\"SUSTRAN\",\"PCRN1N2EXC\""
}

for this value

{
    "@timestamp" => 2023-02-28T20:51:31.096275Z,
      "@version" => "1",
          "tags" => [
        [0] "_csvparsefailure"
    ],
          "host" => "0.0.0.0",
          "path" => "/opt/data/input/new_test1.csv",
       "message" => "K,eip61sccip,1207,A  ,IPVL,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0"
}

Badger · February 28, 2023, 10:43pm

See this issue. The workaround would be

if [message] =~ /^$/ { drop {} }

before the csv filter.

INS · March 1, 2023, 9:35am

Many thanks it works

INS · March 1, 2023, 10:36am

@Badger one thing more, how we can merge output from the first block of CSV to the every of below CSV column output?

{
         "@timestamp" => 2023-03-01T09:48:32.693075Z,
              "RPTPD" => "LAST",
                 "TZ" => "CET ",
           "IVALDATE" => "2023-02-24",
              "SWREL" => "EAGLE 47.0.0.0.0-79.13.0",
            "RPTDATE" => "2023-02-24",
               "path" => "/opt/data/input/new_test1.csv",
            "RPTTYPE" => "AVAILABILITY MEASUREMENTS ON LINK",
            "IVALEND" => "14:15:00",
    "event_timestamp" => "2023-02-24 14:24:14",
             "RPTIME" => "14:24:14",
          "IVALSTART" => "14:00:00",
          "NUMENTIDS" => "285",
               "host" => "0.0.0.0",
               "CLLI" => "stp06",
               "tags" => [
        [0] "_dateparsefailure"
    ],
           "@version" => "1",
            "message" => "\"stp06\",\"EAGLE 47.0.0.0.0-79.13.0\",\"2023-02-24\",\"14:24:14\",\"CET \",\"AVAILABILITY MEASUREMENTS ON LINK\",\"LAST\",\"2023-02-24\",\"14:00:00\",\"14:15:00\",285"
}
{
        "STATUS" => "K",
     "NDCLFINTR" => "0",
      "NEARMGIH" => "0",
      "DRLCLPRO" => "0",
       "LNKTYPE" => "IPVL",
      "SURCVERR" => "0",
      "NMLCLPRO" => "0",
          "path" => "/opt/data/input/new_test1.csv",
      "NMDCLFLR" => "0",
      "NDCFLXER" => "0",
      "SUSRECVD" => "0",
     "NDCLFSYNC" => "0",
      "NDCFLABN" => "0",
      "NDCFLXDA" => "0",
      "FARMGINH" => "0",
       "DRFEPRO" => "0",
      "@version" => "1",
      "DRDCLFLR" => "0",
    "@timestamp" => 2023-03-01T09:48:32.694621Z,
      "NDCFLXDC" => "0",
    "PCRN1N2EXC" => "0",
          "LINK" => "A  ",
       "NMFEPRO" => "0",
      "NDCLFALP" => "0",
      "DRLKINHB" => "0",
       "SUSTRAN" => "0",
          "host" => "0.0.0.0",
           "LSN" => "eip61sccip",
           "LOC" => "1207",
       "message" => "K,eip61sccip,1207,A  ,IPVL,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0"

expected output

"@timestamp" => 2023-03-01T09:48:32.693075Z,
              "RPTPD" => "LAST",
                 "TZ" => "CET ",
           "IVALDATE" => "2023-02-24",
              "SWREL" => "EAGLE 47.0.0.0.0-79.13.0",
            "RPTDATE" => "2023-02-24",
               "path" => "/opt/data/input/new_test1.csv",
            "RPTTYPE" => "AVAILABILITY MEASUREMENTS ON LINK",
            "IVALEND" => "14:15:00",
    "event_timestamp" => "2023-02-24 14:24:14",
             "RPTIME" => "14:24:14",
          "IVALSTART" => "14:00:00",
          "NUMENTIDS" => "285",
               "host" => "0.0.0.0",
               "CLLI" => "stp06",
        "STATUS" => "K",
     "NDCLFINTR" => "0",
      "NEARMGIH" => "0",
      "DRLCLPRO" => "0",
       "LNKTYPE" => "IPVL",
      "SURCVERR" => "0",
      "NMLCLPRO" => "0",
          "path" => "/opt/data/input/new_test1.csv",
      "NMDCLFLR" => "0",
      "NDCFLXER" => "0",
      "SUSRECVD" => "0",
     "NDCLFSYNC" => "0",
      "NDCFLABN" => "0",
      "NDCFLXDA" => "0",
      "FARMGINH" => "0",
       "DRFEPRO" => "0",
      "@version" => "1",
      "DRDCLFLR" => "0",
    "@timestamp" => 2023-03-01T09:48:32.694621Z,
      "NDCFLXDC" => "0",
    "PCRN1N2EXC" => "0",
          "LINK" => "A  ",
       "NMFEPRO" => "0",
      "NDCLFALP" => "0",
      "DRLKINHB" => "0",
       "SUSTRAN" => "0",
          "host" => "0.0.0.0",
           "LSN" => "eip61sccip",
           "LOC" => "1207",

system · March 29, 2023, 10:37am

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

Topic		Replies	Views
Autodetect_column_names is not working as expected in csv filter pluing Logstash	3	2009	May 15, 2019
Autodetect_column_name with 2 different CSV Logstash	5	527	September 29, 2022
Logstash read csv column problem Logstash	3	654	March 15, 2019
Autodetect column is taking data row as column name instead of header Logstash	1	1496	April 30, 2018
Autodetect columns from header row - csv ingest Logstash	1	1291	August 9, 2017

Autodetect_column_names in condition

Related topics