topic Re: How to combine the results ? in Splunk Search

How to combine the results ?

skavuri11 — Mon, 10 Aug 2020 16:21:34 GMT

I am new to Splunk. I have the logs in the following format for our servers.

Host, CPU, %USAGE
Host, Memory, %Usage
Host, Load Average, % USAGE
Host, Swapping, %Usage

I need to create a query to display the results in the following format.

HOST, CPU Avg Usage, Memory Avg Usage, Load Avg Usage, Swapping Avg Usage

My query below is printing the same value for each of fields. Ex: it prints the same cpu value for all the rows. Any suggestions on the query?

    index = index1 sourcetype=.... source=...
      | eval cpu_usage = [search index = ... sourcetype=... source=*  | search metric_name=CPU_Utilization | stats avg(Usage) as "CPU_Usage" by host_name | return $CPU_Usage ]
      | eval memory_usage = [search index = ... sourcetype=... source=*  | search metric_name=Memory_Utilization | stats avg(Usage) as "Memory_Usage" by host_name | return $Memory_Usage ]
      | eval load_usage = [search index = ... sourcetype=... source=*  | search metric_name=Load_Utilization | stats avg(Usage) as "Load_Usage" by host_name | return $Load_Usage ]
      | eval swapping_usage = [search index = ... sourcetype=... source=*  | search metric_name=Swapping_Utilization | stats avg(Usage) as "Swapping_Usage" by host_name | return $Swapping_Usage ]
      | stats values(cpu_usage) as "CPU Utilization", values(memory_usage) as "Memory  Utilization", values(load_usage) as "Load  Utilization", values(swapping_usage) as "Swapping  Utilization"  by host_name

Re: How to combine the results ?

isoutamo — Mon, 10 Aug 2020 18:43:58 GMT

please try the next:

index=_internal | head 1 | eval _raw = "Host, metric_name, usage f1, CPU, 10 f1, mem, 11 f1, mem, 12 f1, swap, 0 f1, load, 10 f1, load, 5 f1, CPU, 1" | multikv forceheader=1 | makemv metric_name | rename COMMENTS as "Previous prepare sample data" | eval {metric_name} = usage | stats avg(CPU) as aCPU avg(load) as aLoad avg(mem) as aMem avg(swap) as aSwap by Host

Here I suppose that you have field names: Host, metric_name, usage in your events.

Last two rows do the logic:

create new field name value of metric_name (CPU, men, swap or load) and assign usage% to it
stats just count averages by Host

r. Ismo

Re: How to combine the results ?

skavuri11 — Mon, 10 Aug 2020 20:11:18 GMT

Thank you @isoutamo . My query with your suggestions works now.