Dashboards & Visualizations
Highlighted

Charting three things and something else...

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(TROLLYSIZE) get the line repeated for each BAG, sum(TROLLYSIZE) gets me a line that's too big...

Mik

0 Karma
Highlighted

Re: Charting three things and something else...

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.

View solution in original post

0 Karma
Highlighted

Re: Charting three things and something else...

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
Highlighted

Re: Charting three things and something else...

Legend

@mikclrk surprisingly both dayhour and TROLLYSIZE 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.

0 Karma
Highlighted

Re: Charting three things and something else...

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.