Dashboards & Visualizations

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

dhruv101
Path Finder

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!

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

dhruv101
Path Finder

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?

0 Karma

dhruv101
Path Finder

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

dhruv101
Path Finder

Sounds good. Thanks so much!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

dhruv101
Path Finder

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

dhruv101
Path Finder

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

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.