How to display calculated fields as part of same graph

[pjbuchan596]

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

[lfedak_splunk]

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

[DalJeanis · Accepted solution]

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

[pjbuchan596]

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]

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