Overlay Two Graphs Together

[mhtedford]

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.

[lguinn2 · Accepted solution]

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.

[mhtedford]

@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

[lguinn2]

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

[mhtedford]

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

[lguinn2]

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")

[mhtedford]

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

[lguinn2]

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

[mhtedford]

@lguinn any update?

[woodcock]

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

[mhtedford]

@woodcock

How do I do that?

Best,
Matthew

[mhtedford]

I'll give 100 karma to the right answer 🙂