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
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.
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.
@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
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.
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.