topic Re: Join across multiple sources display all sources with values present or not in Splunk Search

Join across multiple sources display all sources with values present or not

msrama5 — Wed, 30 Sep 2020 05:07:44 GMT

Hello, I have query which joins across 4 sources and correlationid may or may not exists across all sources, I want to print the id and exists or not exists on each source, wrote the query below and actual results only shows one source where value is present and does not show other 3 sources, any ideas how can this query be modified to show expected results ?
Query -
sourcetype=API_HUB OR sourcetype=API_RISK OR sourcetype=API_SECURE OR sourcetype=API_PAYMENT
| eval CorrelationId = coalesce(CorrelationId,"Not exists")
| table sourcetype,CorrelationId | dedup CorrelationId,sourcetype | sort(sourcetype,CorrelationId)

Actual results
sourcetype CorrelationId
API_RISK 123456-34344-55555

Expected results
sourcetype CorrelationId
API_RISK 123456-34344-55555
API_PAYMENT Not exists
API_SECURE Not exists
API_HUB Not exists

Re: Join across multiple sources display all sources with values present or not

renjith_nair — Thu, 23 Apr 2020 03:44:49 GMT

@msrama5 ,
Do you have events for other sourcetypes?

Try below search for the same time window.

sourcetype=API_HUB OR sourcetype=API_RISK OR sourcetype=API_SECURE OR sourcetype=API_PAYMENT|stats count by sourcetype

Re: Join across multiple sources display all sources with values present or not

msrama5 — Wed, 30 Sep 2020 05:04:53 GMT

I wrote the query using map for find correlation id in first query and substitute one by one in second query to return results for each id as below, this query is not working, wanting to get corrid and substitute in second query as is, any ideas what is wrong with query below ? no results are returned for query below
sourcetype=OPENAPI_HUB | stats count by CorrelationId |
map search="search "$CorrelationId$" sourcetype=OPENAPI_HUB OR sourcetype=OPEN_ASSESSMENT OR sourcetype=OPEN_TEST OR sourcetype=OPENAPI_DEV | eval CorrelationId = coalesce(CorrelationId,"Not exists") | stats count by CorrelationId,sourcetype | sort(CorrelationId,sourcetype)"

Re: Join across multiple sources display all sources with values present or not

to4kawa — Thu, 23 Apr 2020 10:07:19 GMT

sourcetype=API_HUB OR sourcetype=API_RISK OR sourcetype=API_SECURE OR sourcetype=API_PAYMENT
| stats values(CorrelationId) as CorrelationId by sourcetype
|append [|makeresults 
|eval sourcetype=split("API_HUB,API_RISK,API_SECURE,API_PAYMENT" ,",")
| mvexpand sourcetype
| fields sourcetype]
| stats values(CorrelationId) as CorrelationId by sourcetype
| fillnull value="Not exists" CorrelationId

Splunk can't search nothing.

Re: Join across multiple sources display all sources with values present or not

msrama5 — Wed, 30 Sep 2020 05:05:17 GMT

Getting close answer like one below after couple changes, need 2 more changes
1)Want to break down results correlation id by correlation id and display all 4 sourcetypes for each corr-id
2)display null values for source types where corr-id don't exists
Actual -
1129213a-6d34-46e3-a7d7-2e2fcc552045 API_HUB 1
1129213a-6d34-46e3-a7d7-2e2fcc552045 API_RISK 1
116f755f-2c67-4604-b98a-90ed0bb43f74 API_RISK 1
11f7a987-e7a8-4ba3-9445-8ad92f4ecc05 API_PAY 1
11f7a987-e7a8-4ba3-9445-8ad92f4ecc05 API_RISK 1

Expected-
1129213a-6d34-46e3-a7d7-2e2fcc552045 API_HUB 1
1129213a-6d34-46e3-a7d7-2e2fcc552045 API_RISK 1
NULL API_PAY

NULL API_PAY1

116f755f-2c67-4604-b98a-90ed0bb43f74 API_RISK 1
NULL API_RISK 1
NULL API_PAY

NULL API_PAY1

Modified new query is below, can this be modified to get expected results above ?
sourcetype=API_HUB OR sourcetype=API_RISK OR sourcetype=API_PAY OR sourcetype=API_PAY2
| stats values(CorrelationId) as CorrelationId by sourcetype
|append [|makeresults
|eval sourcetype=split("API_HUB,API_RISK,API_PAY ,API_PAY2" ,",")
| mvexpand sourcetype
| fields sourcetype]
| stats count by CorrelationId,sourcetype
| fillnull value="Not exists" CorrelationId | sort (CorrelationId)

Re: Join across multiple sources display all sources with values present or not

to4kawa — Fri, 24 Apr 2020 00:36:49 GMT

sorry. I don't know your log. and your query is changed from question. so I can't make it.

maybe, this result will be wrong.