Splunk Search

How to use multiple join commands in single search?

Jagadeesh2022
Path Finder

Hi Friends,

My current query:

index = pg_idx_whse_prod_events host="*" sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS MarkSweep__collection-count.csv"
| stats latest(value) as "Marksweep collection Count" by host

|join type=left max=0 host
[search index = pg_idx_whse_prod_events host="*" sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS MarkSweep__total-time-ms.csv"
| stats latest(value) as "Marksweep total time ms" by host]

|join type=left max=0 host
[search index = pg_idx_whse_prod_events host="*" sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS Scavenge__collection-count.csv"
| stats latest(value) as "Scavenge collection count" by host]

|join type=left max=0 host
[search index = pg_idx_whse_prod_events host="*" sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS Scavenge__total-time-ms.csv"
| stats latest(value) as "Scavenge total time ms " by host]

I want to use Same index, same sourcetype, same field name but different source. I want each source field value corresponding to host.

Instead of use Join in the above command kindly suggest alternate SPL to achieve this result.

@gcusello

Labels (5)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

You are correct that join is not the way to go (and to call out the king of disjoin for help:-). Try something like

index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS*.csv"
``` host is a required field, no need to do host=* ```
| stats latest(value) as value by host source
| foreach "MarkSweep__collection-count" "MarkSweep__total-time-ms" "Scavenge__collection-count" "Scavenge__total-time-ms"
    [ eval <<FIELD>> = if(source == "/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS <<FIELD>>.csv", value, null())]
    ``` allocate field name based on source ```
| fields - value
| stats values(*) as * by host

The big idea here is to first retrieve all data that your original strategy encounter, then split them meaningfully with groupby in the first stats. After you get data segregated, iterate over source names with foreach in order to give value distinct field names.

Hope this helps.

View solution in original post

Tags (1)

yuanliu
SplunkTrust
SplunkTrust

You are correct that join is not the way to go (and to call out the king of disjoin for help:-). Try something like

index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS*.csv"
``` host is a required field, no need to do host=* ```
| stats latest(value) as value by host source
| foreach "MarkSweep__collection-count" "MarkSweep__total-time-ms" "Scavenge__collection-count" "Scavenge__total-time-ms"
    [ eval <<FIELD>> = if(source == "/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS <<FIELD>>.csv", value, null())]
    ``` allocate field name based on source ```
| fields - value
| stats values(*) as * by host

The big idea here is to first retrieve all data that your original strategy encounter, then split them meaningfully with groupby in the first stats. After you get data segregated, iterate over source names with foreach in order to give value distinct field names.

Hope this helps.

Tags (1)

Jagadeesh2022
Path Finder

@yuanliu,

Thank you so much. Its working perfect.

I have made it with multisearch command. Kindly review and confirm which one I need to use it?
Multisearch or Foreach ?

| multisearch
[search index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS MarkSweep__collection-count.csv" | rename value as value1]
[search index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS MarkSweep__total-time-ms.csv" | rename value as value2]
[search index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS Scavenge__collection-count.csv" | rename value as value3]
[search index = pg_idx_whse_prod_events sourcetype= PG_ST_PROBE_DATA source="/opt/redprairie/prod/prodwms/les/data/csv_probe_data/com.redprairie.mad/JVM-Garbage-Collectors__PS Scavenge__total-time-ms.csv" | rename value as value4 ]
| stats latest(value1) as "PS Marksweep Collection Count" latest(value2) as "PS Marksweep Total Time ms" latest(value3) as "PS Scavenge Collection Count" latest(value4) as "PS Scavenge Total Time ms" by host
|lookup PG_WHSE_PrIME_TS Server AS host
|table Site_Name host "PS Marksweep Collection Count" "PS Marksweep Total Time ms" "PS Scavenge Collection Count" "PS Scavenge Total Time ms" | rename host as Server

0 Karma

yuanliu
SplunkTrust
SplunkTrust

If multisearch allows you to rename "value" field, and you have enough search heads to handle multisearch, you shouldn't need foreach. (I assume that the search is returning what you wanted.)

0 Karma

Jagadeesh2022
Path Finder

Hi @yuanliu ,

Thanks for the reply. 

In our project we have only one search head. I'm not sure how to measure the capability of our SH to run multisearch command. 

Currently I'm getting exact results which I want. If I use multisearch command it impact our application performance? 

Thanks in advance. 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

My understanding is that index search is more efficient if all data are fetched in one batch.  I could be wrong, though.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...