Help Creating Simple Visualization

Elastic Stack Kibana
1

Help Graphing and Comparing Data

What I want to do it see the statistical change, or percentile difference between the numbers for the same file for different dates.

In my Index, I have the following fields:

name - text
number - number
date - date

Here is the mapping I have created for the index.

PUT my-csv-logs-v2.0
{
"mappings": {
"doc": {
"properties": {
"Name": { "type": "text" },
"Number": { "type": "integer","ignore_malformed": true},
"FileDate": { "type": "date" },
"Path": { "type": "text" },
"filename": {"type": "text"}
}
}
}
}

When I try to create a visualization for this data, and I select a bar graph, I can put the number on the Y axis but I am not able to select the name on the X axis.

Do I need to change the name type to keyword or boolean? How can I make it visible on the x axis?

Thank You Kindly

2

Yes, use keyword field type for name. Text field types are not aggregatable. https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html

3

Thank you I will try that.

4

I updated my mapping and created a new index and specified the Name field as a keyword, however, in the gui view of the index via kibana, it says that name is text and is regrettable. Below is my mapping and conf file:

Hi I have updated my mapping like this:

PUT nrdb-csv-logs-v2.1
{
"mappings": {
"doc": {
"properties": {
"Name": { "type": "text" },
"Number": { "type": "integer","ignore_malformed": true},
"FileDate": { "type": "date" },
"Path": { "type": "text" },
"filename": {"type": "keyword"}
}
}
}
}

Here is the conf file section :slight_smile:

input {
file {
path => "/opt/sample-data/nrdb-csv-logs/*.csv"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}
filter {
csv {
separator => ","
columns => ["Name"{"type" => "keyword"},"Number"{"type" => "integer"}"]
}

grok {
match => {"path" => "-%{INT:year}-%{INT:month}-%{INT:day}.csv"}
add_field => ["filedate", "%{year}-%{month}-%{day}"]
}

grok {
match => {"path" => "%{WORD:filename:keyword}-%{INT:year}-%{INT:month}-%{INT:day}.csv"}
}

}

output {
elasticsearch {
hosts => "http://10.0.2.15:9200"
index => "nrdb-csv-logs-v2.4"
}
stdout {}
}

5

You need to adjust your mapping so Name is mapped to keyword. Looks like you are still mapping it to text.

If you don't create your mapping explicitly, usually elasticsearch will add both a text and a keyword version per field, e.g. you will see a name.keyword field available.

But as you have it, it looks like even though you have this line: columns => ["Name"{"type" => "keyword"},"Number"{"type" => "integer"}"]

You are still setting up your mapping with text, here: "Name": { "type": "text" }, unless that was just a typo? That is probably taking precedence, hence still being mapped to text instead of keyword.

6

Hi Stacey, I have kept the conf file the same but I have updated the the mapping like this but I cannot still see how to make a basic graph in Kibana that is going to give me the number value for each filename+date. Right now I just have one log for each of two months. It should be simple, but when I select the y axis as count, and the x axis as terms, then select the name - it shows nothing.

PUT nrdb-csv-logs-v2.5
{
"mappings": {
"doc": {
"properties": {
"Name":{
"type": "text",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"Number": { "type": "integer","ignore_malformed": true},
"FileDate": { "type": "date" },
"Path": { "type": "text" },
"filename": {"type": "keyword"}
}
}
}
}

7

Hi Stacey, After a lot of trial and error, I have a visualization working which is useful but not exactly what I am looking for.

Will I get better results using graphite as a visualization tool for me data if I plug it into the elastic search?

8

I'm unfamiliar with graphite.

Can you post the visualization you got working, and then explain what you were hoping to achieve? I'm not quite sure I follow what your goal is. You were able to select filename for a terms aggregation, but no results showed up?

9

I have a log with the same name for each month. In this example, I am looking at:

a log with Name: atippi

I search on "atippi" and all log files with this record are listed. I find a the record within the log file I am interested in with Name: "Total Target Records". I filter my search on "Total Target Records"

I get two returns on my filter, one for October and one for November.

filename: Atippi Name: Total Target Records Number 3,034,779 filedate: 17-10-29

filename: Atippi Name: Total Target Records Number 3,034,655 filedate: 17-11-29

I want to see the percentage change in the Number from October to November and the standard deviation from October to November. A small variance would indicate a normal data change. A large variance would indicate a problem with the underlying data. Therefore, I want a visualization that will show me something like this:

Y axis: Number

X Axis: The Name by date

In this way, I can see the change between October and November.

But when I try to have the visualization show the Name by Date on the X Axis, I cannot see how to do this.

The visualization I have created, only shows two rows, one for each record with the count and a standard deviation but it will not list the Name field.

I can upload a screen shot if you like? Thanks for your assistance.

10

I tried to use some sample data set up like yours and was able to create this standard deviation chart:

01 PM
Screen Shot 2017-12-12 at 2.04.01 PM.png2792×1624 308 KB

With this sample data and mapping:

00 PM
Screen Shot 2017-12-12 at 2.05.00 PM.png1818×1570 410 KB

Is that similar to what you are hoping to achieve?

Does Name.raw not show up as selectable? Because in your mapping you have Name.raw as a keyword field, it should. If it doesn't, can you send a screenshot of your Management -> Index Patterns page so we can see what fields you have and whether they are aggregatable?

29 PM
Screen Shot 2017-12-12 at 2.07.29 PM.png2330×1314 226 KB

Also, what version are you running?

11

Hi Im on the latest version of everything.....6.0. Here is a screen shot of the Index Patterns and also what I am seeing in Kibana, I created same as you but I get no results. Would you please expand the side panel so I can see what you have selected on the drop downs?

image
image.png1912×951 53.5 KB

image
image.png1292×864 32.7 KB

This is the only thing I was able to get working but I am not seeing how to add additional columns to show the name and the filedate

image
image.png1899×939 52.4 KB

12

Hmmm, perhaps Name.raw doesn't contain any data. What happens if you go to discover and type NOT Name.raw:null? What about NOT Name.raw:""? Also make sure the text fieldName` has data in it.

45 PM
Screen Shot 2017-12-13 at 1.35.45 PM.png3276×1308 436 KB

Can you post the output of running GET nrdb-csv-logs-v2.5/_mapping in Dev Tools?

Also make sure your selected time range covers a span that you have data indexed.

It seems like what you posted should work, if there was data in the Name.raw field and your chosen time range covered it.

13

Yes name.raw does have data.
{
"nrdb-csv-logs-v2.5": {
"mappings": {
"doc": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"FileDate": {
"type": "date"
},
"Name": {
"type": "text",
"fields": {
"raw": {
"type": "keyword"
}
}
},
"Number": {
"type": "integer",
"ignore_malformed": true
},
"Path": {
"type": "text"
},
"day": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"filedate": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"filename": {
"type": "keyword"
},
"host": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"message": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"month": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"path": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"year": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}
}

No When I select Visualization from the Discover-Filter I have created, it gives me closer to the format I am looking for but not quite, I can now see two bars, one for each of the counted records and number value specified in the X Axis, but actually what I care about is the difference in the value for the number. So I want the Number Value to show on Y and the record name and date on the X - then a line above that will show me the standard deviation between the two records.

image
image1867×947 41.6 KB

image
image1920×1040 71.3 KB

Do I need to create a Regexp Query first?

I am not interested in the count of how many records there are. All I want to see is the value expressed in the number and the difference between these numbers over time.

This example is more like what I am trying to do...

14

Hm, I think we need to find out why your visualization is responding with "no results found", which doesn't seem correct.

I notice now you are not using a time based index pattern, but that didn't help me to repro.

I am trying to duplicate your exact scenario. I used your mapping, then ran

POST nrdb3-csv-data/doc/_bulk
{ "index" : { } }
{  "Name": "Total Target Records", "Number": 3034779, "FileDate": "2017-10-29T00:00:00.000Z", "filedate": "17-10-29" }
{ "index" : { } }
{  "Name": "Total Target Records", "Number": 3034655, "FileDate": "2017-11-19T00:00:00.000Z", "filedate": "17-11-19" }

Then I created a non-time based index. Here is my discover page, looks very similar to yours:

22 PM
Screen Shot 2017-12-13 at 2.48.22 PM.png3380×510 48.1 KB

And my visualization:

27 PM
Screen Shot 2017-12-13 at 2.49.27 PM.png3436×1916 347 KB

What happens if you use a y-axis of count instead of standard deviation? What happens if you just try to create a very simple data table visualization with only Name.raw selected, like this:
10 PM

15

Hi Stacey, I am not trying to graph the count at all. The count tells me how many logs which is not what i need to know.

I am trying to graph the number value at specific dates., then the difference example, the second value for the November Date and the first value which is the October Date and show the percentage or deviation between the two.

So is it possible to take count out of the chart?

If I could see by day on X but Y needs to be the number not the count please. It will not allow me to do that. Also if I try to add the filedate instead of timestamp to the X it will not allow me even though the filedate is showing up in the index mapping as a date.

16

I am not trying to graph the count at all. The count tells me how many logs which is not what i need to know.

yes I understand, I'm just trying to figure out why standard deviation won't work, so want to make sure that at least count works. Then I can see if it's a problem with standard deviation on the Y axis, or something wrong with the Name.raw field on the x-axis, and ensure that Name.raw field contains values.

So is it possible to take count out of the chart?

Yes, you just have to select a different metric. For example:

12 PM
12 PM2320×926 127 KB

Also if I try to add the filedate instead of timestamp to the X it will not allow me even though the filedate is showing up in the index mapping as a date.

filedate, or FileDate? I see you have both fields in your mapping, filedate is text, FileDate is Date.

17

I will try that in the meantime is there a way to turn off the graph view and just see the table below when creating the visualization for the data table please? And What is the max value for a Split Services Sub Aggregation for a Term Order Descending? If I put a wildcard will it return everything? What is recommended to be the max...please?

18

I can visualize the data how I like now but still struggling how to see % trends between two values in a field over time, it will take some mo time I will eventually figure it out I guess. Thanks anyways.

19

Might be worth checking out the time series visual builder - https://www.elastic.co/blog/master-time-with-kibanas-new-time-series-visual-builder

20

Hi Stacey,

If you have time if you can even show me how I would just show the difference as in A-B for Dates A1 and Dates B1 that would be great. For example:

Data is:
Record Name: Record-1
Date: Dec 1
Value: 10
filename: file1

RecordName: Record-2
Date: Dec 5
Value: 15
Filename: file2

This is what I want to see is a separate column:

The result of Record2 Value - Record1 Value

so...15-10=5

I want to see that in a separate column called "diff"

Is it able to do that? Ie in excell you have have two columns and create your formula in the third column.

Thanks, Sherri