Dashboards & Visualizations

Charting three things and something else...

mikclrk
Explorer

G'Day.

I'm trying to get a search and chart working, but it doesn't want to play.

The events I'm using are generated hourly and are like this:

TROLLY=1 TROLLY_SIZE =150 BAG=1 CONTENTS=15
TROLLY=1 TROLLY_SIZE =150 BAG=2 CONTENTS=25
TROLLY=1 TROLLY_SIZE =150 BAG=3 CONTENTS=10
TROLLY=1 TROLLY_SIZE =150 BAG=4 CONTENTS=10
TROLLY=1 TROLLY_SIZE =150 BAG=5 CONTENTS=15
TROLLY=1 TROLLY_SIZE =150 BAG=6 CONTENTS=20
TROLLY=1 TROLLY_SIZE =150 BAG=7 CONTENTS=25
TROLLY=2 TROLLY_SIZE =100 BAG=1 CONTENTS=15
TROLLY=2 TROLLY_SIZE =100 BAG=2 CONTENTS=15
TROLLY=2 TROLLY_SIZE =100 BAG=3 CONTENTS=10
TROLLY=2 TROLLY_SIZE =100 BAG=4 CONTENTS=10
TROLLY=2 TROLLY_SIZE =100 BAG=5 CONTENTS=15
TROLLY=2 TROLLY_SIZE =100 BAG=6 CONTENTS=20
TROLLY=2 TROLLY_SIZE =100 BAG=7 CONTENTS=10

What I've got at the moment is something that draws an area fill graph of the total contents of all the bags for the selected Trolly. (At the point of time above, Trolly 1 holds 120 items and Trolly 2 holds 95 items.

| search TROLLY=$tk_trolly$ | chart sum(CONTENTS) over day_hour by BAG

What I want to add is a line that shows the TROLLY_SIZE (basically a straight line at items=150 if Trolly 1 is selected and at 100 if Trolly 2 is selected). There may be more or less than 7 bags in a trolly.

Any hints on how to do it?

Charting avg(TROLLY_SIZE) get the line repeated for each BAG, sum(TROLLY_SIZE) gets me a line that's too big...

Mik

0 Karma
1 Solution

niketnilay
Legend

@mikclrk try the following:

 <yourCurrentSearch>
 | search TROLLY=$tk_trolly$ 
 | eval key=day_hour."-".TROLLY_SIZE
 | chart sum(CONTENTS) over key by BAG
 | rex field=key "^(?<day_hour>[^-]+)-(?<TROLLY_SIZE>\d+)"
 | fields - key
 | table day_hour TROLLY_SIZE *

key is created by combining day_hour with TROLLY_SIZE and then after charting day_hour and TROLLY_SIZE are split using rex command based on regular expression.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @mikclrk,

Did you get a chance to try out @niketnilay 's answer? If it worked, please approve it so other users will know that this topic is closed. If it didn't work, please give us an update, so our group can continue trying to help you!

0 Karma

niketnilay
Legend

@mikclrk try the following:

 <yourCurrentSearch>
 | search TROLLY=$tk_trolly$ 
 | eval key=day_hour."-".TROLLY_SIZE
 | chart sum(CONTENTS) over key by BAG
 | rex field=key "^(?<day_hour>[^-]+)-(?<TROLLY_SIZE>\d+)"
 | fields - key
 | table day_hour TROLLY_SIZE *

key is created by combining day_hour with TROLLY_SIZE and then after charting day_hour and TROLLY_SIZE are split using rex command based on regular expression.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

mikclrk
Explorer

Tried this. The key bit works, changing the series names, but the rex to split it back and the fields -key doesn't seem to do anything...

0 Karma

niketnilay
Legend

@mikclrk surprisingly both day_hour and TROLLY_SIZE are not supposed to have hyphen - character. So regex split should work on the key. You can try changing the delimiter from hyphen - to pipe | and test:

   ....
  | eval key=day_hour."|".TROLLY_SIZE
  | chart sum(CONTENTS) over key by BAG
  | rex field=key "^(?[^\|]+)\|(?\d+)"
   ....

Try the following run anywhere search based on Sample Data provided (command from | makeresults till | KV generates sample data) :

| makeresults 
| eval data="day_hour=1 TROLLY=1 TROLLY_SIZE =150 BAG=1 CONTENTS=15
 day_hour=1 TROLLY=1 TROLLY_SIZE =150 BAG=2 CONTENTS=25
 day_hour=1 TROLLY=1 TROLLY_SIZE =150 BAG=3 CONTENTS=10
 day_hour=2 TROLLY=1 TROLLY_SIZE =150 BAG=4 CONTENTS=10
 day_hour=2 TROLLY=1 TROLLY_SIZE =150 BAG=5 CONTENTS=15
 day_hour=3 TROLLY=1 TROLLY_SIZE =150 BAG=6 CONTENTS=20
 day_hour=4 TROLLY=1 TROLLY_SIZE =150 BAG=7 CONTENTS=25
 day_hour=4 TROLLY=2 TROLLY_SIZE =100 BAG=1 CONTENTS=15
 day_hour=4 TROLLY=2 TROLLY_SIZE =100 BAG=2 CONTENTS=15
 day_hour=4 TROLLY=2 TROLLY_SIZE =100 BAG=3 CONTENTS=10
 day_hour=4 TROLLY=2 TROLLY_SIZE =100 BAG=4 CONTENTS=10
 day_hour=5 TROLLY=2 TROLLY_SIZE =100 BAG=5 CONTENTS=15
 day_hour=5 TROLLY=2 TROLLY_SIZE =100 BAG=6 CONTENTS=20
 day_hour=5 TROLLY=2 TROLLY_SIZE =100 BAG=7 CONTENTS=10" 
| rex field=data "(?<data>[^\n]+\n)" max_match=20 
| mvexpand data 
| rename data as _raw 
| KV 
| eval TROLLY=1 
| eval key=day_hour."-".TROLLY_SIZE 
| chart sum(CONTENTS) over key by BAG 
| rex field=key "^(?<day_hour>[^-]+)-(?<TROLLY_SIZE>\d+)" 
| fields - key 
| table day_hour TROLLY_SIZE *

If the above does not work you might have to post the output of chart command as per your sample data.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!