topic Re: Overlay Two Graphs Together in Dashboards & Visualizations

Overlay Two Graphs Together

mhtedford — Wed, 12 Jul 2017 19:00:34 GMT

I have two graphs in a Splunk dashboard that I want to combine/overlay into one, both concerning data from a survey.

The first shows the total number of survey responses: http://imgur.com/a/Q3ebx
Here is the search query:

index=webex_sentiment | eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M") | eval YearWeek=strftime(surveyDate,"%Y-%U") | search YearWeek!="2016-00" | chart  count(Rating) as NumberRatings by YearWeek | search YearWeek > 2016-12

The second shows the number of survey responses that contained negative sentiment, along with a moving average: http://imgur.com/a/H2ryv
Here is the search query:

index=webex_sentiment | eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M") |  eval YearWeek=strftime(surveyDate,"%Y-%U") |search YearWeek!="2016-00"| stats count(Rating) as NumberRatings by YearWeek Rating | eventstats sum(NumberRatings) as TotalRatings by YearWeek | eval PercentageRatings=round(NumberRatings/TotalRatings,3) | where Rating=1 OR Rating=2 | stats sum(PercentageRatings) as NegativeSentiment by YearWeek | trendline sma3(NegativeSentiment) AS MovingAverage(NegativeSentiment)

I want to display both of these lines graphs in a single chart. Please advise.

Re: Overlay Two Graphs Together

mhtedford — Wed, 12 Jul 2017 19:32:33 GMT

I'll give 100 karma to the right answer 🙂

Re: Overlay Two Graphs Together

woodcock — Thu, 13 Jul 2017 00:55:56 GMT

The simplest thing to do is to append them both together and run them through timechart.

Re: Overlay Two Graphs Together

lguinn2 — Thu, 13 Jul 2017 02:20:59 GMT

Try this:

index=webex_sentiment surveyDate=* Rating=*
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
| eval YearWeek=strftime(surveyDate,"%Y-%U")
| search YearWeek!="2016-00"
| eval Rating = "Rating" . Rating
| chart count by YearWeek Rating
| addtotals fieldname=NumberRatings Rating*
| eval NegativeSentiment = Rating1 + Rating2
| fields YearWeek NumberRatings NegativeSentiment
| streamstats window=5 avg(NegativeSentiment) as MovingAverage_NegativeSentiment

It is slightly different, but should do the same thing. streamstats calculates a moving average based on the current value plus the previous five values, but you could change that as you like. I just wanted to show an alternative.

Re: Overlay Two Graphs Together

mhtedford — Thu, 13 Jul 2017 13:12:31 GMT

@Iguinn

Thanks so much for your help. I input your code, but my search did not return any results.

Here is the job inspection: http://imgur.com/a/JMv3T

I believe the error may have something to do with the time range, but I'm not positive.

Best,

Matthew

Re: Overlay Two Graphs Together

mhtedford — Thu, 13 Jul 2017 13:13:30 GMT

@woodcock

How do I do that?

Best,
Matthew

Re: Overlay Two Graphs Together

lguinn2 — Thu, 13 Jul 2017 23:06:04 GMT

I think the error is because you put the word "search" at the very begging of the command line.

The search command is implied. By putting the word "search" in the box, you asked Splunk to identify events with the literal keyword "search" in them. There probably aren't any events like that in your data. 😄

Re: Overlay Two Graphs Together

mhtedford — Fri, 14 Jul 2017 12:57:27 GMT

@lguinn

I believe the problem may be elsewhere. I input your code here: http://imgur.com/a/In8ij

However, there are still no events shown.

In the picture from my last comment, the "search" term was shown because I was inspecting the job.

Thanks again; please let me know how to fix this if you can 🙂

Re: Overlay Two Graphs Together

lguinn2 — Fri, 14 Jul 2017 21:09:20 GMT

What do you get if you just run the first part of the search

 index=webex_sentiment surveyDate=* Rating=*
 | eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
 | eval YearWeek=strftime(surveyDate,"%Y-%U")

Re: Overlay Two Graphs Together

mhtedford — Mon, 17 Jul 2017 14:08:53 GMT

@lguinn thanks so much!

I found the problem was the "surveyDate=* Rating=*" at the start of the query.

I deleted that phrase then re-ran your initial code:

index=webex_sentiment 
 | eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M")
 | eval YearWeek=strftime(surveyDate,"%Y-%U")
 | search YearWeek > "2016-12"
 | eval Rating = "Rating" . Rating
 | chart count by YearWeek Rating
 | addtotals fieldname=NumberRatings Rating*
 | eval NegativeSentiment = Rating1 + Rating2
 | fields YearWeek NumberRatings NegativeSentiment
 | streamstats window=5 avg(NegativeSentiment) as MovingAverage_NegativeSentiment

I received this graph: http://imgur.com/a/sC0Xz

However, this displays the number of surveys with negative sentiment, rather than the percentage of surveys with negative sentiment.

How do I make this change?

Re: Overlay Two Graphs Together

mhtedford — Mon, 24 Jul 2017 13:45:49 GMT

@lguinn any update?

Re: Overlay Two Graphs Together

lguinn2 — Thu, 27 Jul 2017 05:44:41 GMT

Sorry, broke my arm so I am way behind on things that require typing.
Add this to the end of your search:

| eval PercentNegativeSentiment = (NegativeSentiment * 100) / NumberRatings

Or you could make this the next-to-the-last line an compute the moving average based on the Percent...

at the end you might want to use

| fields - NegativeSentiment

or something like it to clean up the graph. You might consider removing the number of ratings as well, because the difference in scale may make the graph hard to read. You could always put total ratings on a separate graph...