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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...