topic Re: How to combine three (x,y) coord series into one graph? in Dashboards & Visualizations

How to combine three (x,y) coord series into one graph?

dhruv101 — Tue, 26 Jun 2018 00:41:41 GMT

Hi,

I have 3 simple graphs generated by these three queries respectively

index=“app_event” | eval starttime = strftime ($$payload.beginVal$$, “%F %T.%9Q”) | chart count(starttime) as BeginVal by starttime 

index=“app_event” | eval endtime  = strftime ($$payload.endVal$$, “%F %T.%9Q”) | chart count(endtime) as EndVal by endtime

index=“app_event” | eval otherttime = strftime ($$payload.anotherVal$$, “%F %T.%9Q”) | chart count(othertime) as OtherVal by othertime

The count values are always 1. So coords can be assumed to be like

1. (1,1) , (3,1) (7,1)
2. (2,1), (5,1) (11,1)
3. (4,1), (8,1)

I want to merge these three charts into one chart by x axis value such that resultant chart looks like
(1,1) (2,1) (3,1) (4,1) (5,1) (7,1) and so on. But when I hover over the bar columns I want to be able to know the source of th column that is, is it BeginVal, EndVal or OtherVal.

Could someone please help me with the query.

Thanks!

Re: How to combine three (x,y) coord series into one graph?

DalJeanis — Tue, 26 Jun 2018 16:57:18 GMT

Seems like you are overcomplicating your life.

Try something like this...

index=“app_event” 
| eval myFan=mvrange(0,3)
| mvexpand myFan
| eval _time=case(myFan=0,$$payload.beginVal$$, myFan=1,$$payload.endVal$$, myFan=2,$$payload.anotherVal$$)
| eval phase=case(myFan=0,"Start", myFan=1,"End", myFan=2,"Other")
| timechart count by phase

The above assumes that the phase time values were epoch times (or your strftime would not have worked)

If timechart won't work for your purposes, you can do this as well...

| eval Time= strftime (_time, “%F %T.%9Q”)
| chart count by Time phase

... although you can choose the order, TIme phase or phase time, and so on.

Re: How to combine three (x,y) coord series into one graph?

dhruv101 — Tue, 26 Jun 2018 17:16:47 GMT

Thanks DalJeanis, this worked out. Now lets say "other" comes from a different index. How would we modify the query then? Should we use multisearch?

Re: How to combine three (x,y) coord series into one graph?

dhruv101 — Tue, 26 Jun 2018 21:39:02 GMT

Hey DalJeanis, could you also briefly explain the code you have written? I dont clearly understand why we need the myFan variable and what case(myFan=0) evaluates to since its not myFan == 0, but rather an assignment. Thanks, I would really appreciate your help.

Re: How to combine three (x,y) coord series into one graph?

DalJeanis — Wed, 27 Jun 2018 15:03:22 GMT

@dhruv101 - luckily, splunk can only do assignment in the context of an eval or stats-type aggregate function use, so that code is just an equality test. Splunk will let you code the test as = or == in almost all cases. (You are right that == is unambiguous, so please feel free to code it that way.)

'myFan', in combination with the mvexpand verb, is one method of turning a single event into 3 events. mvrange(0,3) produces a field that has three values - (0,1,2). Mvexpand then produces three events, one with each value. Multivalue fields are indexed using a zero base, so I'm in the habit of starting at zero. In this case, it could have been mvrange(17,20) and would have produced a different three values (17,18,19). The rest of the code creates a different kind of record for each value of myFan.

Here, we've reset the value of _time to a different thing for each type of record... that's the main reason why we couldn't just do the timechart all without splitting up the records.

Re: How to combine three (x,y) coord series into one graph?

DalJeanis — Wed, 27 Jun 2018 15:05:43 GMT

You can if you want, but it isn't needed. You just OR together whatever you need before the first pipe.

 ( index=“app_event” ... any other search terms to narrow the first kind of record ...) OR
 ( index=“foo”  ... any other search terms to narrow the second kind of record ...)
 | fields ...list all the fields that you need from either kind of record...

Re: How to combine three (x,y) coord series into one graph?

dhruv101 — Wed, 27 Jun 2018 17:08:14 GMT

I see. Thanks a lot for the detailed explanation @DalJeanis!

Re: How to combine three (x,y) coord series into one graph?

dhruv101 — Thu, 28 Jun 2018 18:50:52 GMT

Hey @DalJeanis
When we hover over the chart columns it shows the phase name and the count(as expected). Is it possible to add more labels to the column? I want to be able to see the value of $$payload.eventID$$ for each column. It is guaranteed that for all columns(with count 1 or more) the payload.eventID will be the same.

Re: How to combine three (x,y) coord series into one graph?

DalJeanis — Sat, 30 Jun 2018 20:23:15 GMT

@dhruv101 - that is a very different question. You should probably post a new question with those specific needs, and hope for an answer from one of my colleagues that specializes in dashboard behavior.

Re: How to combine three (x,y) coord series into one graph?

dhruv101 — Mon, 02 Jul 2018 19:07:12 GMT

Sounds good. Thanks so much!