Using list in query fails to return records

[nirmalya2006]

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.

[KailA · Accepted solution]

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]

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

[KailA]

Glad to help !

[somesoni2]

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

[gyarici]

Hi ,

You should add Answer and Market into the stats query.

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

Hope helps

[nirmalya2006]

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.

[p_gurav]

Can you try something:

| stats count(Answer) by Question, Market

[nirmalya2006]

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.