Splunk Search

How to join 2 searches from 2 different sourcetypes

ookamidono
Explorer

I am encountering problems joining 2 querries that are getting values from 2 different sourcetypes.

I would like to get the CPU load and maximum (s well as a trend line) of all my hosts, filtered by HostGroup. But the HostGroups are only linked to hosts in sourcetype=entity, and the value of CPU loads are located in sourcetype=metrics.

I'll link the 2 queries and an explaination of the results, as for the combination I tried to make of the 2 queries.

 

 

index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend by hostName
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)]

 

 

This query returns the average and maximum CPU load per host, which is the result I'm trying to get to, but sorted by HostGroup.

And the only way for me to filter hosts by HostGroup is to use this query :

 

 

index=my_index sourcetype="entity" hostGroup.name="*"
| spath
| stats values(discoveredName) as hostName by hostGroup.name

 

 


So I tried combining the two queries using the mvexpand command :

 

 

index=my_index sourcetype="entity" hostGroup.name=$hostGroup_token$
| spath
| stats values(discoveredName) as hostName
| mvexpand hostName
| join

[ search index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)]

 

 

 

The problem is that this particular query returns only 1 value that is the average  and maximum value of CPU load and max of all my hosts. 

Any idea on how to join the 2 queries so that it returns the CPU load and max filtered by HostGroup ?

Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend

Try something like this

index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| join hostName

[ search index=my_index sourcetype="entity" hostGroup.name=$hostGroup_token$
| spath
| stats values(discoveredName) as hostName by hostGroup.name
| mvexpand hostName]
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend by hostGroup.name
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)

View solution in original post

ITWhisperer
Legend

Try something like this

index=my_index sourcetype="metrics" timeseriesId="host.cpu.user"
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S")
| join hostName

[ search index=my_index sourcetype="entity" hostGroup.name=$hostGroup_token$
| spath
| stats values(discoveredName) as hostName by hostGroup.name
| mvexpand hostName]
| stats avg(value) as AvgCPU, max(value) as MaxCPU, values(unit) as Unit, sparkline(avg(value)) as Trend by hostGroup.name
| eval AvgCPU = round(AvgCPU,2), MaxCPU = round(MaxCPU, 2)

View solution in original post

ookamidono
Explorer

It doesn't seem to work, Splunk finds no results for this query.

0 Karma

ookamidono
Explorer

My bad, forgot to rename some values. Thanks a lot for the help !

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.