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!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...