Date formatting

I have a datafile I wish to ingest using Logstash. Historically I've always used CLI tools to manipulate the date/time fields to be easier to map using a .conf file (my imports tend to be CSV in nature). I was just wondering if someone here might have some guidance that can save me frustration.

Original sample had date and time in separate fields and here's a sample:

26,10119,100,60,KW,0,0
26,20119,200,60,KW,0,0
26,30119,300,60,KW,0,0
26,40119,400,60,KW,0,0
26,50119,500,60,KW,0,0
26,60119,600,60,KW,0,0
26,70119,700,60,KW,0,0
26,80119,800,60,KW,0,0
26,90119,900,60,KW,0,0
26,100119,1000,60,KW,0,0

In this example, the first line is January 1st 2019 at 1am and the last line is October 1st 10am.

Going back to old habits, I removed the trailing zeros and merged the fields

26,10119 1,60,KW,0,0
26,20119 2,60,KW,0,0
26,30119 3,60,KW,0,0
26,40119 4,60,KW,0,0
26,50119 5,60,KW,0,0
26,60119 6,60,KW,0,0
26,70119 7,60,KW,0,0
26,80119 8,60,KW,0,0
26,90119 9,60,KW,0,0
26,100119 10,60,KW,0,0

with the intention to use something like the following

    date {"match" => ["date" , "Mddyy H"]}

but this didn't work (no data was brought in for single digit months)

Should I just work on awk'ing or perl scripting this correction or is there perhaps a better way to use a config to properly parse this out?

That does not work because M will consume multiple characters, like this:

      "date" => "10119 1",
"@timestamp" => 0009-10-11T05:56:02.000Z

You could force it to be six characters using ruby

    grok { match => { "date" => "%{BASE10NUM:[@metadata][date]} %{BASE10NUM:[@metadata][hour]}" } }
    ruby {
        code => '
            date = event.get("[@metadata][date]").rjust(6, "0")
            hour = event.get("[@metadata][hour]")
            event.set("date", "#{date} #{hour}")
        '
    }
    date {"match" => ["date" , "Mddyy H"]}

I have not touched grok...but it looks like it's really useful for these logstash configs because I keep seeing it. I ended up using awk and sed to get this going:

awk -F, '{printf "%d,%06d,%d,%d,%d,%d\n" ,$1,$2,$3,$4,$5,$6}'

This awk added the necessary leading zero to get the date to a consistent length.

sed -E "s/,(.{2})(.{2})(.{2}),/,\1/\2/20\3,/g"

This sed gave me separators as well as added the 20 to the beginning of the year

awk -F, '{print $1","$2" "$3","$4","$5","$6}

This awk merged the date/time columns to one separated by space

    date {
    "match" => ["date" , "MM/dd/yyy Hmm"]

Finally this config seemed to do the trick. I guess I just didn't spend enough time on it before posting. Thanks for the good input though.

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