Howdy all,
I've spent the past few days reading forums, researching guides and using trial & error to get my head around the following situation, and now I realise I need help.
I want to be able to display/work on dates fields in Kibana in the format dd/MM/yyyy(eg. 24/11/2015) and seperate time of day fields in the format HH:mm (eg. 13:55).
(Please excuse my annotated examples below, first forum post & I've run out of space)
CSV Data Layout
I have a .csv file of radiology data; each line/record contains multiple seperate dates and time of day columns. Format of each line in the csv is as follows;
Lowest,Coonabarabran,OutPatient Bulk Billed,CBN0900370,5334793,XC,CBN-OP,CR,30/10/2014,11:52,30/10/2014,12:33,30/10/2014,12:35,30/10/2014,12:37,Mark Cohen,30/10/2014,12:38,30/10/2014,12:39,Mark Cohen,0:43,0:02,0:01,0:01,0:04,Thursday,Weekday,Yes,Yes,Yes,Yes
Logstash Filter Config
filter {
if [type] == "radtest" {
csv {
separator => ","
columns => [
...
"Registered Date",
"Registered Time",
"Last Image Date",
"Last Image Time",
... ]
}
date {
match => [ "Registered Date", "dd/MM/YYYY"]
}
date {
match => [ "Last Image Date", "dd/MM/YYYY"]
}
date {
match => [ "Registered Time", "HH:mm"]
}
date {
match => [ "Last Image Time", "HH:mm"]
}
}
}...
Extract of Elasticsearch Index Configuration & Field Mapping
elkadmin@vzeus:~$ curl -XGET 'http://localhost:9200/radtest/_mapping/radiology/?pretty'
{
"radtest" : {
"mappings" : {
"radiology" : {
"properties" : {
"@timestamp" : {
"type" : "date",
"format" : "strict_date_optional_time||epoch_millis"
},
"Authorised Date" : {
"type" : "date",
"format" : "dd/MM/yyyy"
},
"Authorised Time" : {
"type" : "date",
"format" : "HH:mm"
},
...
Fields as they're displaying during debugging in stdin/stdout
elkadmin@vzeus:~$ sudo /opt/logstash/bin/logstash -f /etc/logstash/debug.d -l /var/log/logstash/logstash-debug.log
stdout:
Sending logstash logs to /var/log/logstash/logstash-debug.log.
{
"message" => "Lowest,Coonamble,OutPatient Bulk Billed,CMB0871119,5858992,XC,CMB-OP,CR,27/08/2014,11:34,27/08/2014,11:39,27/08/2014,11:41,27/08/2014,11:44,Mark Cohen,27/08/2014,11:45,27/08/2014,11:46,Mark Cohen,0:07,0:03,0:01,0:01,0:05,Wednesday,Weekday,Yes,Yes,Yes,Yes",
"@version" => "1",
"@timestamp" => "1970-01-01T01:46:00.000Z",
"type" => "radtest",
"host" => "vzeus",
"Ds Priority" => "Lowest",
"Institution" => "Coonamble",
"Billing Type" => "OutPatient Bulk Billed",
"Accession No" => "CMB0871119",
"MRN" => "5858992",
"Scan Type" => "XC",
"Ward Code" => "CMB-OP",
"Modality" => "CR",
"Registered Date" => "27/08/2014",
"Registered Time" => "11:34",
"Last Image Date" => "27/08/2014",
"Last Image Time" => "11:39",
"Examined Date" => "27/08/2014",
"Examined Time" => "11:41",
"Reported Date" => "27/08/2014",
"Reported Time" => "11:44",
"Reported By" => "Mark Cohen",
"Interimed Date" => "27/08/2014",
"Interimed Time" => "11:45",
"Authorised Date" => "27/08/2014",
"Authorised Time" => "11:46",
"Authorised By" => "Mark Cohen",
"Registered To Examined" => "0:07",
"Hours Examined to Reported" => "0:03",
"Hours Reported to Interimed" => "0:01",
"Hours Interim to Authorised" => "0:01",
"Hours Examined to Authorised" => "0:05",
"Examined Day of the week" => "Wednesday",
"Examined Day Type" => "Weekday",
"Registered to Reported <xHrs" => "Yes",
"Registered to Examined <2Hrs" => "Yes",
"Examined to Reported <24Hrs" => "Yes",
"Interim to Authorised <24Hrs" => "Yes"
}
Format of fields displayed in Kibana
Questions
- Is it possible to display date fields in Kibana as dd/mm/yyyy without the time component? If so where am I going wrong?
- Is it possible to store time of day in format HH:mm without the day component?
My use case for this would be something like being able to focus on all records where field "Authorised Time" was between "09:05" & "17:30" etc.
I'm happy to post more information as needed.
Thanks,
Dan
