Splunk Search

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

sbollam
Explorer

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

Labels (1)
0 Karma
1 Solution

tscroggins
Influencer

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

View solution in original post

0 Karma

tscroggins
Influencer

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

0 Karma

sbollam
Explorer

@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

 
 
Tags (1)
0 Karma

tscroggins
Influencer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...