topic Re: how to display multiple field values from different searches in pie chart in Splunk Search

how to display multiple field values from different searches in pie chart

sbollam — Fri, 05 Mar 2021 13:44:42 GMT

I have following query to display the results in pie chart. Problem here is I could not see the all the values in the pie chart

index=dummy ticket_number="*" sourcetype="tickets"
| eval status= "incident_" + status
| stats first(opened_at) as ticket_openedAt latest(status) as ticketStatus by ticket_number
| where NOT ticketStatus IN("ticket_Resolved", "ticket_Canceled", "ticket_Closed")
| eval openTime = strptime(ticket_openedAt, "%Y-%m-%d %H:%M:%S"), currentTime=now(), days = round((currentTime - openTime)/86400, 0)
| where days > 5
| stats count as ticket_count by ticketStatus
| appendcols
[ search index=dummy problem_number="*" sourcetype="problem"
    | eval status = "problem_" + status
    | stats first(opened_at) as problemOpenedAt latest(status) as problemStatus by problem_number
    | where NOT problemStatus IN("problem_Resolved", "request_Closed")
    | eval openTime = strptime(requestOpenedAt, "%Y-%m-%d %H:%M:%S"), currentTime=now(), days = round((currentTime - openTime)/86400, 0)
    | where days > 5
    | stats count as request_count by problemStatus ]
| appendcols
[ search index=dummy issue_number="*" sourcetype="issue"
    | eval status= "problem_" + status
    | stats first(opened_at) as issueOpenedAt latest(status) as issueStatus by issue_number
    | where NOT issueStatus IN("problem_Resolved", "problem_Closed Complete")
    | eval openTime = strptime(problemOpenedAt, "%Y-%m-%d %H:%M:%S"), currentTime=now(), days = round((currentTime - openTime)/86400, 0)
    | where days > 5
    | stats count as problem_count by issueStatus ]
| transpose

I would require your help in displaying the incident_count by incidentStatus, problem_count by problemStatus and issue_count by issueStatus in the pie chart. Also, is there a way to optimize this search

Re: how to display multiple field values from different searches in pie chart

richgalloway — Fri, 05 Mar 2021 15:11:32 GMT

That's not how pie charts work. They're designed to present a single series of data. If the Statistics tab of your search results shows more than 2 columns then you can't use a pie chart. Based on that, each subsearch in this query should be a separate pie.

Re: how to display multiple field values from different searches in pie chart

tscroggins — Sat, 06 Mar 2021 21:54:03 GMT

@sbollam

With trellis enabled, you can include a split-by field in addition to a category field.

For example, here's a reduced version of your search:

index=dummy ((sourcetype=tickets ticket_number=*) OR (sourcetype=problem problem_number=*) OR (sourcetype=issue issue_number=*))
| eval number=coalesce(ticket_number, problem_number, issue_number)
| stats first(opened_at) as opened_at first(status) as status by sourcetype number
| search (sourcetype=tickets NOT status IN (Resolved Canceled Cancelled Closed)) OR (sourcetype=problem NOT status IN (Resolved Closed)) OR (sourcetype=issue NOT status IN (Resolved "Closed Complete"))
| where strptime(opened_at, "%F %T")<relative_time(now(), "-5d")
| stats count by sourcetype status

On the Visualization tab, select the pie chart, and enable trellis and split by sourcetype. You should see three pie charts--issue, problem, and tickets--with counts for all status values not excluded by the search command.

Re: how to display multiple field values from different searches in pie chart

sbollam — Tue, 09 Mar 2021 15:52:19 GMT

@tscroggins, Thank you I can go with this approach and it looks good. But the problem here is when I update the trellis, all the three pie charts are aligned to the left side of the panel, Also I cannot increase the size the of the trellis to adjust to the entire panel, I mean size of the pie chart. I tried options medium, small, large but it did not work. How can I increase size of the trellis and adjust the float to the center using style

Re: how to display multiple field values from different searches in pie chart

tscroggins — Sat, 13 Mar 2021 18:55:41 GMT

@sbollam

Using strictly Simple XML, you'll need to use separate searches and standalone pie charts as @richgalloway suggested.

You can also use CSS to manipulate the dashboard display. I suggest posting a new question in an appropriate category for more information on using CSS to manipulate trellis output in dashboards.