Bar chart show a single bar of a month while different year

(kumar) #1

Hello,

I am new in kibana. i am using kibana 6.5.0.

I have a table which have column month, year and expenses_value. i have 12 month values in month column (e.g January, February, March etc). i have data from November 2017 to November 2018.

I have use logstash to insert data into elastic search.
now i am creating bar chart of this data.

i am taking expenses_value on y-axis ( aggregation>sum on expenses_value). and month on x-axis (aggregation>term on month field) and sub aggregation (aggregation>term on year field).
i have expenses_value of November 2017 and November 2018. now on visualization, it is showing a single bar of November 2017 and November 2018 (combine values of November 2017 and November 2018, separated by different color in November month bar).

i want to create separate bar of each month and order by month with year.

please suggest.

Thanks.

(Magnus Kessler) #2

In your logstash pipeline, try to combine the year and month into the same field, which ideally would be mapped to the date datatype in the Elasticsearch index. You could then use a date_histogram in your visualisation.

To combine the fields, you could for example use add_field in the mutate plugin.

(kumar) #3

Thanks for your reply.
In mysql database all fields are string type. i have convert two fields in integer.
this is my logstash conf file:

input {

jdbc {
jdbc_driver_library => "C:\Users\ELK_Data\mysql-connector-java-8.0.13.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name"
jdbc_user => "root"
jdbc_password => "mysql"
statement => "SELECT * FROM table_name"
type => "response"
}
}
filter {
mutate {convert => ["year", "integer"] }
mutate {convert => ["expenses_value", "integer"] }
}
output {
elasticsearch {
index => "total_expenses_transaction"
document_type => "%{type}"
hosts => "localhost:9200"
}
stdout {}
}

(Magnus Kessler) #4

Try something along these lines, before you convert year to integer:

filter {
  mutate {
    add_field {
      "my_date" => "%{year}-%{month}"
    }
  }
}

I'd also highly recommend to create an index template with the correct mappings for your data model.

(kumar) #5

Sir,
I have change filter plugin to:
filter {
mutate {
add_field {
"my_date" => "%{year}-%{month}"
}
}
mutate {convert => ["year", "integer"] }
mutate {convert => ["expenses_value", "integer"] }
}
i got this error:
[2018-11-22T16:45:25,673][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.4.0"}
[2018-11-22T16:45:26,331][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, => at line 14, column 15 (byte 398) after filter {\n mutate {\n add_field ", :backtrace=>["C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:41:in compile_imperative'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:49:incompile_graph'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:157:ininitialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline.rb:22:in initialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline.rb:90:ininitialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:38:in execute'", "C:/ELK/logstash/logstash-core/lib/logstash/agent.rb:309:inblock in converge_state'"]}

(Magnus Kessler) #6

Sorry, this should've read

filter {
  mutate {
    add_field => {
      "my_date" => "%{year}-%{month}"
    }
  }
}

When posting error messages or code examples, could you please use [code]...[/code] blocks to make the output easier to read?

(kumar) #7

Thanks Sir.

now separate bar showing for November 2017 and November 2018.
Its showing in this order:
2017-December
2017-November
2018-April
2018-August
2018-February
....

Can i sort the month wise data.
for example:
2018-November
2018-October
2018-September
..
....
2018-January
2017-December
2017-November

Thanks.

(Magnus Kessler) #8

Thanks for trying this out. However, I had assumed that the month would be a number between 01 and 12, leading to combined values such as 2018-11, which is easily converted into a date field value.

Is there any chance that you get a simple timestamp out of your SQL database?

(kumar) #9

Sir,
i have add another column req_date. data type of this column is varchar in database which have date timestamp value.
like this:
For November-2017, req_date= 2017-11-30 00:00:00.000
For December-2017, req_date= 2017-12-31 00:00:00.000
For March-2018, req_date= 2018-03-31 00:00:00.000

Thanks.

(kumar) #10

Sir,
I have used this date filter:
date {
match => [ "req_date", "ISO8601", "YYYY-MM-dd", "YYYY-MM-dd" ]
target => "req_date"
locale => "en"
}

In database req_date column having a value: 2017-11-30 00:00:00.000
It is display on bar graph: November 30th 2017, 00:00:00.000
Can i display only November 2017 on bar graph in visualization.

Thanks.

(Magnus Kessler) #11

Try using date histograms with a monthly interval.

(kumar) #12

Sir,

Please put some cherries on top of that..

Can i display data values on top of each bar?

Thanks.

(Magnus Kessler) #13

If you mean that the number is displayed as text on top of the bar, this doesn't appear to be possible with the standard vertical bars visualisation. You can explore different settings in the Metric and Settings and Panel Settings tabs when you create the visualisation.

(kumar) #14

Ok. Thank you Sir.