Dashboards & Visualizations

How to create a bar graph based on two fields

mahbs
Path Finder

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?

Tags (1)
0 Karma

cmerriman
Super Champion

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

harsmarvania57
Ultra Champion

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
0 Karma

mahbs
Path Finder

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

0 Karma

harsmarvania57
Ultra Champion

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...