Reporting

Adding Column to charts based on lookup table

aquinojason
Path Finder

Hi,

How am I able to add the BusinessUnit Column on my splunk query below?  The output 

index=xxxxxxx sourcetype=xxxxxxx | multikv forceheader=1 | dedup ACCOUNT_CODE DATE MVS_SYSTEM_ID CALCMIPS | eval DATE=strftime(strptime(DATE,"%d%b%y"),"%Y-%m-%d") | lookup Account_file.csv ACCOUNT_CODE OUTPUT Application BusinessUnit ApplicationRTO | eval _time=strptime(DATE." "."00:00:00","%Y-%m-%d %H:%M:%S") | table _time Application BusinessUnit MVS_SYSTEM_ID CALCMIPS | chart avg(CALCMIPS) by Application DATE limit=0

Output should look like something below with BusinessUnit after Application.

aquinojason_0-1618399858153.png

Thanks and Regards,

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 

Chart doesn't pass the column, in fact, it creates a table with two dimensions (Application and DATE in your instance), and one value (your average) for each cell. If you want the BusinessUnit passed through, you need to add it to the x dimension (Application) before the chart command

index=xxxxxxx sourcetype=xxxxxxx | multikv forceheader=1 | dedup ACCOUNT_CODE DATE MVS_SYSTEM_ID CALCMIPS | eval DATE=strftime(strptime(DATE,"%d%b%y"),"%Y-%m-%d") | lookup Account_file.csv ACCOUNT_CODE OUTPUT Application BusinessUnit ApplicationRTO | eval _time=strptime(DATE." "."00:00:00","%Y-%m-%d %H:%M:%S") | table _time Application BusinessUnit MVS_SYSTEM_ID CALCMIPS 
| eval Application=Application." - ".BusinessUnit
| chart avg(CALCMIPS) by Application DATE limit=0

 

0 Karma

aquinojason
Path Finder

Hi,

Thank you for the quick response. Can I use a different function to achieve it? I was hoping I can sort it by Application or by Business Unit.

Thanks and Regards,

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could split the field after the chart so long as you are not going to then try to visualise it

 

index=xxxxxxx sourcetype=xxxxxxx | multikv forceheader=1 | dedup ACCOUNT_CODE DATE MVS_SYSTEM_ID CALCMIPS | eval DATE=strftime(strptime(DATE,"%d%b%y"),"%Y-%m-%d") | lookup Account_file.csv ACCOUNT_CODE OUTPUT Application BusinessUnit ApplicationRTO | eval _time=strptime(DATE." "."00:00:00","%Y-%m-%d %H:%M:%S") | table _time Application BusinessUnit MVS_SYSTEM_ID CALCMIPS 
| eval Application=Application."|".BusinessUnit
| chart avg(CALCMIPS) by Application DATE limit=0
| eval BusinessUnit=mvindex(split(Application,"|"),1)
| eval Application=mvindex(split(Application,"|"),0)
| fields Application BusinessUnit *

 

aquinojason
Path Finder

Thank you very much!

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 ...