Splunk Search

How to display calculated fields as part of same graph

pjbuchan596
Explorer

Hello, I'm attempting to display three calculated fields (total_meeting_hours, total_use_no_meeting_hours, and hours_not_in_use) as a part of a pie chart. Each of these fields should represent a calculated portion of the total pie chart, with their values given on mouse over and their field names showing as their labels for their respective portions on the graph.

I'm currently attempting to achieve this (seen below) by using mvappend to create a multi-value field (display_val) and then split on that field in the chart function. However when splitting by this field it displays the numeric time values for each of the fields instead of the field names (i.e "11.023" hour time instead of "total_meeting_hours"). How would I go about accomplishing this? I attempted to create a separate multi-value field and combine it with the other for labeling, but have so far been unable to do so. I'd greatly appreciate help!

| convert num(SM_C.value.data.elapsedTime) as use_duration num(SM_C.value.data.MeetingElapsedTime) as meeting_duration
| where (use_duration < 50000 and use_duration > 0)
| eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0)
| stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration
| eval total_meeting_hours = (total_meeting_duration / 60 / 60)
| eval total_use_no_meeting_hours = (total_duration / 60 / 60) - total_meeting_hours
| eval hours_not_in_use = (168) - (total_duration / 60 / 60)
| eval display_val=(mvappend(hours_not_in_use, total_use_no_meeting_hours, total_meeting_hours))
| eval display_label=(mvappend("hours_not_in_use", "total_use_no_meeting_hours", "total_meeting_hours"))
| mvexpand display_val
| mvexpand display_label
| chart eval(sum(display_val) / 168) as Percentage by display_val
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

How about something like this ...

 | convert num(SM_C.value.data.elapsedTime) as use_duration
           num(SM_C.value.data.MeetingElapsedTime) as meeting_duration
 | where (use_duration < 50000 and use_duration > 0)
 | eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0)
 | stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration

 | eval used_hours = round((total_meeting_duration / 3600),2)
 | eval reserved_hours = round((total_duration / 3600),2) 
 | eval unused_hours = 168-reserved_hours
 | eval reserved_unused = reserved_hours - used_hours

 | eval fan=mvrange(1,3.0001)
 | mvexpand fan
 | eval Usage=case(fan=1,"Used", fan="2","Reserved Not Used", fan=3,"Unused")
 | eval Value=case(fan=1,used_hours, fan="2",reserved_unused, fan=3,unused_hours)
 | chart sum(Value) by Usage

View solution in original post

0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

Hey @pjbuchan596, if DalJeanis solved your problem, remember to "√Accept" an answer to award karma points 🙂

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

How about something like this ...

 | convert num(SM_C.value.data.elapsedTime) as use_duration
           num(SM_C.value.data.MeetingElapsedTime) as meeting_duration
 | where (use_duration < 50000 and use_duration > 0)
 | eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0)
 | stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration

 | eval used_hours = round((total_meeting_duration / 3600),2)
 | eval reserved_hours = round((total_duration / 3600),2) 
 | eval unused_hours = 168-reserved_hours
 | eval reserved_unused = reserved_hours - used_hours

 | eval fan=mvrange(1,3.0001)
 | mvexpand fan
 | eval Usage=case(fan=1,"Used", fan="2","Reserved Not Used", fan=3,"Unused")
 | eval Value=case(fan=1,used_hours, fan="2",reserved_unused, fan=3,unused_hours)
 | chart sum(Value) by Usage
0 Karma

pjbuchan596
Explorer

Thank you very much! That answer is what I needed to get it. I made a few changes to it to have it work with my data, as I'm separating room use into both "occupied in call / meeting" vs "occupied only", apart from the room being out of use. But eval's case() function was exactly the solution. Thanks! This was my final solution:

| convert num(SM_C.value.data.elapsedTime) as use_duration num(SM_C.value.data.MeetingElapsedTime) as meeting_duration
| where (use_duration < 50000 and use_duration > 0)
| eval meeting_duration = if(meeting_duration < use_duration, meeting_duration, meeting_duration = 0)
| stats sum(use_duration) as total_duration sum(meeting_duration) as total_meeting_duration

| eval meeting_hours = round((total_meeting_duration / 3600), 2)
| eval used_no_meeting_hours = round(((total_duration / 3600) - meeting_hours), 2)
| eval hours_not_in_use = round((168) - (total_duration / 3600), 2)

| eval fan=mvrange(1, 3.0001)
| mvexpand fan
| eval Usage=case(fan=1,"Occupied", fan=2,"Meeting/Call", fan=3,"Unoccupied")
| eval Value=case(fan=1,used_no_meeting_hours, fan=2,meeting_hours, fan=3,hours_not_in_use)
| chart sum(Value) by Usage

DalJeanis
SplunkTrust
SplunkTrust

@pjbuchan596 - You're welcome. Thanks for posting the final solution. It helps folks to make their own connections.

I had understood "used-no-meeting" incorrectly, I had assumed that was when someone had reserved the room but not actually occupied it.

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