Dashboards & Visualizations

Adding New Field to Dashboard Panel

mhtedford
Communicator

I have a dashboard panel of survey percentage results over time, divided into separate lines by region:
alt text

Here is the query:

index=webex_sentiment 
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M") 
| eval YearWeek=strftime(surveyDate,"%Y-%U") 
| eval Country=upper(Country) 
| lookup CountryDetails Country OUTPUT Region 
| stats count(Rating) as NumberRatings by YearWeek Rating Region 
| eventstats sum(NumberRatings) as TotalRatings by YearWeek Region 
| eval PercentageRatings=round(NumberRatings/TotalRatings,3) 
| where Rating=1 OR Rating=2 
| chart sum(PercentageRatings) as NegativeSentiment by YearWeek Region

I want to show this same line graph, but only include events that contain the value "A desk or huddle room (9 or fewer seats) phone" for the JoinedFrom field:
alt text

This JoinedFrom field should cover 100% of events, but for some reason it's only showing as covering 5%

This is the query that should work:

index=webex_sentiment 
| eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M") 
| eval YearWeek=strftime(surveyDate,"%Y-%U") 
| eval Country=upper(Country) 
| lookup CountryDetails Country OUTPUT Region 
| search JoinedFrom="A desk or huddle room (9 or fewer seats) phone" 
| stats  count(Rating) as NumberRatings by YearWeek Rating Region  
| eventstats  sum(NumberRatings) as TotalRatings by YearWeek Region  
| eval  PercentageRatings=round(NumberRatings/TotalRatings,3)  
| where  Rating=1 OR Rating=2  
| chart  sum(PercentageRatings) as NegativeSentiment by YearWeek Region

However, it's not coming out quite right: http://imgur.com/a/9TbaM

I want a similar line graph to the original, just with only the events where JoinedFrom="A desk or huddle room (9 or fewer seats) phone."

Please advise

0 Karma

cmerriman
Super Champion

try this:

 index=webex_sentiment "A desk or huddle room (9 or fewer seats) phone" 
 | eval surveyDate=strptime(Started,"%m/%d/%Y %H:%M") 
 | eval YearWeek=strftime(surveyDate,"%Y-%U") 
 | eval Country=upper(Country) 
 | lookup CountryDetails Country OUTPUT Region 
 | stats  count(Rating) as NumberRatings by YearWeek Rating Region  
 | eventstats  sum(NumberRatings) as TotalRatings by YearWeek Region  
 | eval  PercentageRatings=round(NumberRatings/TotalRatings,3)  
 | where  Rating=1 OR Rating=2  
 | chart  sum(PercentageRatings) as NegativeSentiment by YearWeek Region

that should narrow down your search to what you're looking for before you actually start evaluating anything.

0 Karma

niketn
Legend

@mhtedford, I believe you are getting "A desk or huddle room (9 or fewer seats) phone" from raw events of index=webex_sentiment. In that case you can add this string in your base search. If field extraction is already in place you can put the same with field name.

index=webex_sentiment "A desk or huddle room (9 or fewer seats) phone"

Or

index=webex_sentiment JoinedFrom="A desk or huddle room (9 or fewer seats) phone"
Filtering the results upfront will boost query performance by fetching only required events from Index.
Also refer to lookup command optimization, you should perform stats by country first and then perform lookup to change country to region.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup#Optimizing_your_lookup_se...

However, ignoring performance, I think above search should also give you only one series for "A desk or huddle room (9 or fewer seats) phone". So can you please illustrate what you are seeing vs what you expect?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...