Join issue

[v11n]

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

[bowesmana]

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

| makeresults
| eval x=split("id=1 title=one,id=2 title=two,id=3 title=three", ",")
| mvexpand x
| eval _raw=x
| extract
| table id title _raw
| append [
  | makeresults
  | eval _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\""
  | multikv noheader=t 
  | table _raw
]
| rex field=_raw "^(?<id>\d+).*\?(?<params>.*)"
| stats values(*) as * by id
| table id title params

 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.

[johnhuang]

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