topic Re: Join issue in Splunk Search

Join issue

v11n — Wed, 22 Dec 2021 20:03:57 GMT

I want to join two source types ST1(has fields id,title) and ST2(no fields only _raw="xid https://www.example.com?q1=test1&q2=test2") . I have tried via join it is working but due to sub search row constraint, I am getting wrong result. I have tried without join(sourcetype="ST1" OR sourcetype="ST2" approach), I am getting incorrect result.

sourcetype="ST1" (id,title are fields here)

id=1 title=one

id=2 title=two

id=3 title=three

sourcetype="ST2"

_raw

1 "GET https://www.example.com?q1=one"

2 "GET https://www.example.com?q1=test&q2=test2"

3 "GET https://www.example.com?q3=thr"

I want to join these source types and get the below output(grab the url params alone in source type ST2). Can you please help me on this?

id	title	params
1	one	q1=one
2	two	q1=test&q2=test2
3	three	q3=thr

Re: Join issue

johnhuang — Wed, 22 Dec 2021 21:30:35 GMT

Use stats and combine the searches whenever possible since its much more efficient.

Here's a possible way to skinning it:

(sourcetype="ST1" OR sourcetype="ST2") | fields _time id title _raw | rex field=_raw "^(?<event_id>\d+)\s\"(?<event_type>[^\s]*)\s(?<base_url>[^\?]*)(\?(?<params>[^\"]*))?" | eval id=COALESCE(id, event_id) | stats values(event_id) AS event_id values(base_url) AS base_url values(params) AS params max(title) AS title values(event_type) AS event_type max(_time) AS _time by id | table _time id title event_type base_url params

Re: Join issue

bowesmana — Wed, 22 Dec 2021 21:43:18 GMT

You need to grab the fields from your ST2 data row with rex, e.g. try this example

Note that the rex statement must only grab the id and params from the ST2 sourcetypes, so this relies on _raw from ST1 NOT matching the rex pattern, so that id from the ST1 is not overwritten by an extraction.