How to use ELK to extract data from specific dates from Date field in csv

Elastic Stack Kibana
1

Hi all,
Need help in creating dashboard for defects.
I need to extract data based on date field column in a csv file and plot graphs based on it.
I want X axis to be based on specific date range field from the csv file.

For example, in the attached csv file, I want the count of Severity - 1,2,3,4 and count of Regression that are within submit Date 11/1/2015 to 11/15/2015 and similarly within submit Date 11/16/2015 to 11/30/2015.

How can this be achieved in kibana?

2

So you don't have the data in Elasticsearch yet? Then use Logstash to parse the CSV file and its date field. You'll need something like this:

input {
  file {
    ...
  }
}
filter {
  csv {
    ...
  }
  date {
    ...
  }
}
output {
  elasticsearch {
    ...
  }
}
3

Thanks for your inputs.

This is what I tried

date {
match => ["submit date", "DD/MM/YYYY"]
target => "@timestamp"
}

But still am not able to get to use the "submit date" as "timestamp".

4

It looks like the field is named "Submit Date" so don't use "submit date" in your filter. Secondly, "DD" in date patterns means "day of year", not "day of month". Use "dd" instead. See the Joda-Time documentation for details.

The Logstash log usually contains useful information about date parsing problems.

5

Thanks Magnus, as you suggested I have changed the date filter as below:

date {
match => ["Submit Date", "MM/dd/yyyy"]
target => "@timestamp"
}

In stdout console, I am able to get "timestamp" mapped to "Submit Date" as below :

2015-11-01T18:30:00.000Z POORNIMC-7RE9W 11/2/2015,N,,2,
2015-11-04T18:30:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-02T18:30:00.000Z POORNIMC-7RE9W 11/3/2015,N,,2,
2015-11-04T18:30:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-05T18:30:00.000Z POORNIMC-7RE9W 11/6/2015,N,Y,2,
2015-11-05T18:30:00.000Z POORNIMC-7RE9W 11/6/2015,N,,1,Y
2015-11-04T18:30:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-06T18:30:00.000Z POORNIMC-7RE9W 11/7/2015,N,Y,1,Y
2015-11-15T18:30:00.000Z POORNIMC-7RE9W 11/16/2015,N,,3,
2015-11-16T18:30:00.000Z POORNIMC-7RE9W 11/17/2015,N,Y,2,

But not able to see any updates in Kibana.
Even tried deleting and creating new index but still the latest logs are not seen in kibana.
Any inputs on how to proceed?

6

Yeah, this looks good. Keep in mind that @timestamp is UTC (and you're obviously in UTC+5.30). To reparse an old file with Logstash, delete the sincedb file or set sincedb_path to /dev/null. See the file input documentation for details.

7

Thanks Magnus :slight_smile:

I am able to get the timestamp as expected in the console as below :

2015-11-02T00:00:00.000Z POORNIMC-7RE9W 11/2/2015,N,,2,
2015-11-05T00:00:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-05T00:00:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-05T00:00:00.000Z POORNIMC-7RE9W 11/5/2015,N,,2,
2015-11-06T00:00:00.000Z POORNIMC-7RE9W 11/6/2015,N,Y,2,
2015-11-06T00:00:00.000Z POORNIMC-7RE9W 11/6/2015,N,,1,Y
2015-11-03T00:00:00.000Z POORNIMC-7RE9W 11/3/2015,N,,2,
2015-11-07T00:00:00.000Z POORNIMC-7RE9W 11/7/2015,N,,Others,Y
2015-11-16T00:00:00.000Z POORNIMC-7RE9W 11/16/2015,N,,3,
2015-11-17T00:00:00.000Z POORNIMC-7RE9W 11/17/2015,N,Y,2,

As you suggested, I routed sincedb path file and kibana worked.
But in Kibana I see timestamp still does not refer to submit date.Is there any other config change to be done in kibana?

8

For a particular message,

  • what's the original date,
  • what's the raw value of the @timestamp field, and
  • what's displayed in Kibana?
9

what's the original date,
I am expecting the 'submit date' to appear in timestamp field- [Ex: 11/17/2015]

what's the raw value of the @timestamp field, and

@timestamp:February 16th 2016, 14:36:11.042

what's displayed in Kibana?

message:11/17/2015,N,,2, @version:1 @timestamp:February 16th 2016, 14:36:11.042 host:POORNIMC-7RE9W path:C:/ELK/logstash-2.1.1/logstash-2.1.1/bin/Test/Test.csv Submit Date:11/17/2015 Status:N Regression: - Severity:2 Customer-track-number: - _id:AVLpVHARcKyJnxyGOLqG _type:logs _index:logstash-2016.02.47 _score:

10

@timestamp:February 16th 2016, 14:36:11.042

That's not the raw value. I expected something like "2016-02-16T...".

In your last post you said you got

2015-11-17T00:00:00.000Z POORNIMC-7RE9W 11/17/2015,N,Y,2,

in your console, which looks correct. If you're getting February instead of November in Kibana I suspect you're looking at the wrong events.

11

In console, am able to get the correct timestamp field but not in kibana.

12

Yes, but again I think that's because you're not looking at the right data in Kibana. What's printed to the console is that same data that's sent to Elasticsearch.

13

I modified time picker and could see the data in kibana. Thanks for the pointers Magnus :slightly_smiling: