how can i do this without using the append subsearches

[CR]

I want to get metrics from multiple index/sourcetype combinations - have been using the append clause and subquery to do it but need to process a lot of events and hit the limitations of subqueries and although i get all the data from the primary query the appends get truncated.   Im sure there is an easy way of doing this and its what splunk is meant to do but cant work out how to cater for the different manipulation that needs to be done depending on the index and sourcetype. The follow is a relatively simple one but i have more complex queries which need to calculate rates from absolute values etc.  

So basically have 3 queries ( one that needs a join so i can do some calculations) keep _time host and the metric I want and then do the visualisation.

index=windows sourcetype=PerfmonMk:Memory host IN(host1,host2,host3)
| join type=outer host [ search index=windows sourcetype=WMI:ComputerSystem host IN(host1,host2,host3) earliest=-45d latest=now()
| stats last(TotalPhysicalMemory) as TPM by host
| eval TPM=TPM/1024/1024]
| eval winmem=((TPM-Available_MBytes)/TPM)*100
| fields _time host mem
|append [search index=linux sourcetype=vmstat host IN(host4,host5,host6)
| where isnotnull(memUsedPct)
| eval linmem=memUsedPct
| fields _time host mem]
|append [ search index=unix sourcetype="nmon-MEMNEW" host IN(host7,host8,host9)
| where isnotnull("Free%")
| eval aixmem=100-'Free%'
| fields _time host mem]
| eval host=upper(host)
| timechart limit=0 span=1h perc95(mem) as Memory_Utilisation by host

[gcusello]

Hi @CR,

following the indications of @PickleRick, you have to create a main search with the three searches correlated with the OR operator.

In addition the join command is a very slow command possibly to avoid,

So, please, try something like this:

(index=windows host IN(host1,host2,host3)) OR (index=windows sourcetype=WMI:ComputerSystem host IN(host1,host2,host3) earliest=-45d latest=now()) OR (index=linux sourcetype=vmstat host IN(host4,host5,host6)
 NOT memUsedPct=*) OR (index=unix sourcetype="nmon-MEMNEW" host IN(host7,host8,host9) NOT "Free%"=*)
| eval host=upper(host), mem=100-'Free%'
| rename memUsedPct AS mem
| stats max(mem) AS mem last(TotalPhysicalMemory) as TPM max(Available_MBytes) AS Available_MBytes earliest(_time) AS _time BY host
| eval mem=((TPM/1024/1024-Available_MBytes)/TPM)*100
| timechart limit=0 span=1h perc95(mem) as Memory_Utilisation by host

In few words, the approach should be:

• to find a common key in all your searches (host)
• to put in the main search all your searches correlated by the OR operator,
• rename o transform (using eval) all the fields  to havethe same field name,
• then put in the stats command all the fields you need for the following commands (mem, TPM, Available_MBytes, _time).

Obviously I cannot test this search but you could try my approach to debug it adding one by one your searches and modifying each one as I describe (renaming or transforming fields).

You could this approach to replace the join command, many append searches and the transform command.

Ciao.

Giuseppe

[isoutamo]

Here is an excellent presentation how and why you should avoid join (etc.) on queries. Maybe not exactly what you are looking, but definitely something to worth of read. https://conf.splunk.com/files/2020/slides/TRU1761C.pdf
r. Ismo

[CR]

thanks Giuseppe,

some good ideas in your solution - the challenge is that calculating the Windows Memory Used % means I need the freebytes from one sourcetype and the size of memory from another.  The sourcetype with size doesnt have frequent events so i cant do the calculation without a join. I also just tried using "|multiseach" but it doesnt allow joins. I thought of creating 3 base searches ( one for each windows (with the join) , LInux , Unix) but then I can work out how to reference all 3 base searches in one query. All roads seem to lead to a dead end. 

[gcusello]

Hi @CR,

you could also create an eventtype for each search and then use the three eventtypes for the main search.

In this way you have a more smart search.

Ciao.

Giuseppe

[PickleRick]

apoend is a streaming command so it shouldn't be that limiting. I'd rather expect the join to be too "heavy", especially since it's an outer join. But indeed you consume multiple subsearches.

You could do some multi-conditioned evals which would transform only subsets of your data (you still need to have common timerange for your search) but since you're searching for the same parameter, just expressed differently in different types of events, it's a perfect case for creating (or using a standard one) a datamodel and creating proper aliases/calculated fields to fit the data into this datamodel.

You can then do a single search from the datamodel.

Additionally, unles you're on Splunk Free, you can accelerate the datamodel which gives you a big boost in your searches.

[CR]

thanks -

the appends are limited to 50,000 events it would seem - so if i have 6 host with an event each minute i hit that limit in about 8 days which doesn't do what i need 

I thought some kind of conditional execution would be the option not sure on the syntax to do it.

If anyone can point me to a good example it would be appreciated.

I guess what i need is something that does this

search index IN (one, two, three)  sourcetype IN (a,b,c) host IN (host1, host2,host3)

 if sourcetype = a then do ( list of commands)

else

if sourcetype=b then do (list of commands)

else

if sourcetype=c then do (list of commands)

| timechart  ......