Splunk Search

Using list in query fails to return records

nirmalya2006
Path Finder

Hi All

I have data in the below fomat

Market=UK, Question=Where do you live, Answer=London 
Market=USA, Question=Where do you live, Answer=New York 
Market=UK, Question=What is you pet, Answer=dog 
Market=USA, Question=What is you pet, Answer=cat

... and so on

The problem is the question is not exhaustive and it can keep changing. So I cannot hard code a question in the query.
I am trying to create pie charts for each question.
I have written a query to get the count of answers based on market for a specific question from a list of question.

index=index1 sourcetype=app_logs 
| dedup Question 
| stats list(Question) as questions  
| eval question=mvindex(questions, 1) 
| where Question = question 
| chart count as Count over Answer by Market

The problem is, when I include the 3rd line (| stats list(Question) as questions ) the query returns all the events and not the statistics
So I am not able to get any records for charting.

I am sure there is something wrong with the query but not able to figure it out.
Can someone help me please.

Tags (2)
0 Karma
1 Solution

KailA
Contributor

Hi,

You can try something like this :

 index=index1 sourcetype=app_logs 
| stats count(Answer) BY Question,Answer

After that, choose the Pie Chart Visualisation and activate Trellis.
So check Use Trellis Layout and select Split by Question
You will have have 1 Pie chart by question with it answers without hard coding any questions.

Tell me if it works 🙂

Kail

View solution in original post

KailA
Contributor

Hi,

You can try something like this :

 index=index1 sourcetype=app_logs 
| stats count(Answer) BY Question,Answer

After that, choose the Pie Chart Visualisation and activate Trellis.
So check Use Trellis Layout and select Split by Question
You will have have 1 Pie chart by question with it answers without hard coding any questions.

Tell me if it works 🙂

Kail

nirmalya2006
Path Finder

Fabulous !! Thanks a ton. Its exactly what I was trying to do manually.

0 Karma

KailA
Contributor

Glad to help !

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What's you pie chart should show? Are you trying to create a pie chart showing how many time a question is being answered? If yes, try this

 index=index1 sourcetype=app_logs 
 | stats count by Question

If you're looking to count how many markets that questions is asked, try this

 index=index1 sourcetype=app_logs 
 | stats dc(Market) as Markets by Question
0 Karma

gyarici
Path Finder

Hi ,

You should add Answer and Market into the stats query.

...| stats list(Question) as questions by Answer,Market|....

Hope helps

0 Karma

nirmalya2006
Path Finder

It didn't work. So my query is
| dedup Question
| stats list(Question) as questions by Answer, Market
| eval question = mvindex(questions, 1)
| where Question = question
| chart count as Count over Answer by Market

I am trying to have 1 pie chart for 1 question
So i need to filter by the questions without hard coding the question in query.
It displayed all the events like before and no charts or statistics.

0 Karma

p_gurav
Champion

Can you try something:

| stats count(Answer) by Question, Market
0 Karma

nirmalya2006
Path Finder

That is not something I am trying. I wanted to have 1 pie chart for each question.
So i need to filter by the questions without hard coding the question in query.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...