How to combine the results ?

[skavuri11]

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

[isoutamo]

Hi

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

[skavuri11]

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