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

View solution in original post

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
Revered Legend

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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!