topic Re: Using list in query fails to return records in Splunk Search

Using list in query fails to return records

nirmalya2006 — Tue, 24 Apr 2018 13:36:57 GMT

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.

Re: Using list in query fails to return records

p_gurav — Tue, 24 Apr 2018 14:02:30 GMT

Can you try something:

| stats count(Answer) by Question, Market

Re: Using list in query fails to return records

gyarici — Tue, 24 Apr 2018 14:06:13 GMT

Hi ,

You should add Answer and Market into the stats query.

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

Hope helps

Re: Using list in query fails to return records

nirmalya2006 — Thu, 26 Apr 2018 15:58:12 GMT

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.

Re: Using list in query fails to return records

nirmalya2006 — Thu, 26 Apr 2018 16:01:28 GMT

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.

Re: Using list in query fails to return records

somesoni2 — Thu, 26 Apr 2018 16:36:37 GMT

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

Re: Using list in query fails to return records

KailA — Thu, 26 Apr 2018 21:08:11 GMT

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

Re: Using list in query fails to return records

nirmalya2006 — Fri, 27 Apr 2018 09:28:33 GMT

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

Re: Using list in query fails to return records

KailA — Fri, 27 Apr 2018 12:48:03 GMT

Glad to help !