topic How to create a bar graph based on two fields in Dashboards & Visualizations

How to create a bar graph based on two fields

mahbs — Tue, 29 Sep 2020 17:09:02 GMT

Hi,

I've got two fields:

04_DEC_2017      05_DEC_2017
5                     5
6                     6
7                     9

I want to create a bar graph with the dates 04_DEC_2017 05_DEC_2017 on the axis and the sum for each field plotted on the graph.

So this is what I've got at the moment:

index=xxxsourcetype=csv source=xxxx| table DEPT, *_2017, *_2018 | fields - 03_DEC_2017 | fillnull | where DEPT=xx OR DEPT=xxOR DEPT=xxOR DEPT=xxOR DEPT=xxOR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx| stats sum(*_2017)

Any ideas?

Re: How to create a bar graph based on two fields

harsmarvania57 — Wed, 06 Dec 2017 11:59:23 GMT

Hi @mahbs,

Can you please try below query ?

| makeresults
| eval 04_DEC_17=4, 05_DEC_17=5
| append [ | makeresults | eval 04_DEC_17=4, 05_DEC_17=6 ]
| eval tmp=1
| chart sum(*_17) AS *_17 over tmp

Re: How to create a bar graph based on two fields

mahbs — Wed, 06 Dec 2017 12:49:51 GMT

Hi, the query is good, but the only issue is, the dates are automatically populated from data sent by a database periodically.

Re: How to create a bar graph based on two fields

harsmarvania57 — Wed, 06 Dec 2017 12:57:38 GMT

ok, so can't you run below this query based on query which you provided ?

index=xxxsourcetype=csv source=xxxx| table DEPT, *_2017, *_2018 | fields - 03_DEC_2017 | fillnull | where DEPT=xx OR DEPT=xxOR DEPT=xxOR DEPT=xxOR DEPT=xxOR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx
| eval tmp=1
| chart sum(*_2017) AS *_2017 over tmp

Re: How to create a bar graph based on two fields

cmerriman — Thu, 07 Dec 2017 13:29:52 GMT

this will get you a sum of each date by dept (in the transpose, the 0 indicates no limit to how many columns/rows get transposed):

index=xxxsourcetype=csv source=xxxx (DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx)| table DEPT, *_2017, *_2018 | fields - 03_DEC_2017 | fillnull | stats sum(*) as * by DEPT|transpose 0 header_field=DEPT column_name=DEPT

and this should get you the sum for all depts by basically creating a dummy field (called dept) and using that as the by command:

index=xxxsourcetype=csv source=xxxx (DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx OR DEPT=xx)| table DEPT, *_2017, *_2018 | fields - 03_DEC_2017 | fillnull |eval dept="all depts"| stats sum(*) as * by dept|transpose 0 header_field=dept column_name=dept