Query to find percentage of memory used, am able to find raw numbers but can't turn into percentage

[dbeyer085]

All I’m trying to do is show the percentage of used memory (available/total).  I have the query that returns the Available and the Total from one of the dashboards:

index=*index* sourcetype=tss:action host=*host*

category=monitoring_wp OR category=monitoring_as

measure="memory health status" OR measure=mem OR measure="available bytes"

| eval "Mem Health" = case(measure == "memory health status", value)

| eval memory = case(measure == "mem", round(value/1024/1024/1024,2))

| eval "Avail Memory" = case(measure == "available bytes",round(value/1024/1024/1024,2))

| bin span=5m _time

| timechart avg("Avail Memory") as "Available Memory (Gb)" avg(memory) as "Heap Mem(Gb) Avg" span=5m

This seems pretty straight forward.  I thought all I would need to do is add another ‘eval’ statement to find the fraction for  the percentage of used memory:

index=*index* sourcetype=tss:action host=*host*

category=monitoring_wp OR category=monitoring_as

measure="memory health status" OR measure=mem OR measure="available bytes"

| eval "Mem Health" = case(measure == "memory health status", value)

| eval memory = case(measure == "mem", round(value/1024/1024/1024,2))

| eval "Avail Memory" = case(measure == "available bytes",round(value/1024/1024/1024,2))

| eval "Percentage" = 'Avail Memory'/memory  <----- *****Here is what I added ******

| bin span=5m _time

| timechart avg("Avail Memory") as "Available Memory (Gb)" avg(memory) as "Heap Mem(Gb) Avg" avg("PercentageUsed") as "Percentage"  span=5m

But that doesn’t work, that field (PercentageUsed) always returns as empty (null) in the results set.  The  ‘eval’ statement and format works correctly if I replace it with only one of the variables:

(| eval "Percentage" = 'Avail Memory')

or if I just replace one of the variables with a number:

(eval "Percentage" = 'Avail Memory'/2)

Any thoughts on what I could do here?  I was thinking the problem might be that the “Avail Memory” and “memory” are coming from different reports, so when one is not null, the other will be null.  Or I just don’t exactly understand how Splunk is generating these results.

[richgalloway]

The problem is the 'mem' field can never be both "mem" and "available bytes" in the same event so you won't have values for the 'Avail Memory' and 'memory' fields.

[dbeyer085]

Hey thanks for the info.  Any thoughts on the best way to get the percentage I'm looking for?  I'm still new to this query syntax.  Is there a way to run the query twice with saved values?  Or some other solution that I'm not thinking of?

[richgalloway · Accepted solution]

Try this untested query.  It uses the eventstats command to copy the "mem" value from the events that have it to all other events from the same host.  Then the percentage calculation should work.

index=*index* sourcetype=tss:action host=*host*
category=monitoring_wp OR category=monitoring_as
measure="memory health status" OR measure=mem OR measure="available bytes"
| eval "Mem Health" = case(measure == "memory health status", value)
| eval memory = case(measure == "mem", round(value/1024/1024/1024,2))
| eval "Avail Memory" = case(measure == "available bytes",round(value/1024/1024/1024,2))
| eventstats max(memory) as memory by host
| eval "Percentage" = 'Avail Memory'/memory
| bin span=5m _time
| timechart span=5m avg("Avail Memory") as "Available Memory (Gb)" avg(memory) as "Heap Mem(Gb) Avg" avg("PercentageUsed") as "Percentage"