Visualizations using time only not date

Elastic Stack Kibana
1

I'm looking to create a graph that only uses the time data from my Date field which after going through a csv filter shows as

February 28th 2017, 14:43:29.000

An being being able to see the time a user logs in over the period of a month.

I'm sure i read that this can be done using a scripted field but unsure where to even start

2

@Pokecallum you can use the following Script to create a Scripted Field to extract the time from a Date field:

def dateFormat = new SimpleDateFormat("HH:mm:ss");
return dateFormat.format(doc['utc_time'].value);

Just in case you haven't created a scripted field before, to get there you'll click Management -> Index Patterns -> Scripted Fields -> Add Scripted Field as highlighted below:

1 Like
3

Hi thanks, although I get a runtime error when trying to use this. I've been trying to add the time field by itself so then Ill be able to use the range aggregation to get what I want. Would you happen to know the reason why my convert for hour => integer doesnt work. but size does ?

input {
    file {
        type => "csv"
        path => "/home/callum/Desktop/test2/*.csv"
        start_position => beginning
        sincedb_path => "/dev/null"
    }
}
filter {
        csv {
            separator => ","       
            columns => ["Date","User","Size","File Type","Device Class","Device Model","File Name","Time"]
        }

        date {
            match => ["Date", "yyyy-MM-dd'T'HH:mm:ssZZ"]

        }
        mutate {
            rename => {"Date" => "Time"}
            add_field => {"[hour]" => "%{+HH:mm:ss:z}"}
            convert => { "hour" => "integer" }
            convert => { "Size" => "integer" }
            remove_field => ["message","path","host","type"]
            lowercase => ["Date","User","Size","File Type","Device Class","Device Model","File Name"]
        }
        
}
    output {
            elasticsearch {
                hosts => "http://localhost:9200"
                index => "a"
            }

    }
4

@Pokecallum I failed to note that you'll want to replace the field that you're looking to take the Time from in the sample script that I posted, does the following Script work?

def dateFormat = new SimpleDateFormat("HH:mm:ss");
return dateFormat.format(doc['Date'].value);

You can definitely do it on the logstash side as well, what are you seeing when you try to use your provided logstash configuration?

5 
        "User" => "callum",
        "Size" => 61751,
   "File Type" => "xlsx",
        "Time" => "2017-02-25T13:19:03Z",
  "@timestamp" => 2017-02-25T13:19:03.000Z,
"Device Class" => "removable",
"Device Model" => "sandisk cruzer blade usb device, disk drive, (standard disk drives)",
        "hour" => "13",
    "@version" => "1",
   "File Name" => "j:my spreadsheet.xlsx"

Unfortuantly Im getting the same error with that script, although I think its probably me using it in the wrong way.
I'd much rather do it through logstash, as I understand that more although Im not getting any errors with my config.

6

@Pokecallum completely understand your preference to use Logstash to do so. I'd recommend posting this question in the Logstash category though, if that's the route you'd like to go.

7

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