topic Help with join in searches in Splunk Search

Help with join in searches

mailravi — Sat, 17 Sep 2022 00:12:22 GMT

Need help with Left join

There are two queries as below

Query1

index=abc sourcetype=123 | table a.b.requestGUID EmplId

Query 2

index=adef sourcetype=456 | table c.requestGUID VacationStartDate

In query 1 the request guid is under an object b within an object a (hence a.b.requestGUID)

In query 2 the request guid is under a different object c (hence c.requestGUID)

what is the syntax to join query 1 & 2 on guid under two objects and see if an employee has a vacation coming up ( basically get Guid, emplID vacatioStartDate in one shot.)

These two queries only have guid as common field but they are under different objects.

Thanks, for the help

Re: Help with join in searches

gcusello — Sat, 17 Sep 2022 04:29:44 GMT

Hi @mailravi,

don't think to the join command because it's a very slow command, use stats.

You have to correlate the two searches identifying the commn ield, something like this:

(index=abc sourcetype=123) OR (index=adef sourcetype=456) | eval requestGUID=coalesce(a.b.requestGUID,c.requestGUID) | stats values(EmplId) AS EmplId values(VacationStartDate) AS VacationStartDate BY requestGUID

Ciao.

Giuseppe

Re: Help with join in searches

mailravi — Sat, 17 Sep 2022 16:39:36 GMT

Thank you sir for the detail. Will try stats and update ( out for the weekend, no system access currently:) )

Re: Help with join in searches

gcusello — Sun, 18 Sep 2022 05:36:16 GMT

Hi @mailravi,

ok, let me know.

Ciao

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: Help with join in searches

mailravi — Mon, 19 Sep 2022 17:59:42 GMT

I tried eval and stats, it is not returning data when combined using eval and stats even though the individual searches shows the emplId and Avacation plans.

Not sure what syntax error I am making.

Is it the coalesce statement joining the two result sets?

Which is the exact place where Guid from one search is compared in the other in the combined query?

Thanks for all the help!

Re: Help with join in searches

gcusello — Tue, 20 Sep 2022 06:43:21 GMT

Hi @mailravi,

please share your search to check the syntax.

Anyway, coalesce the approach is to have one common field to group values from a.b and c.

The group command is stats, but you can group only having a common field.

Ciao.

Giuseppe

Re: Help with join in searches

yuanliu — Tue, 20 Sep 2022 06:56:46 GMT

As @gcusello said, you most often do not need to think in terms of JOIN. If you must have an analogy, you can think of stats' "by" as the "join". The use of coalesce is equivalent to something like

| eval requestGUID = if(isnull('a.b.requestGUID'), c.requestGUID, a.b.requestGUID)

This is just to make sure that the same field name can be used in both source types to be used for groupby.

Now, to your results. First, check all spelling, especially because a.b., c., etc. are not real field paths.

Then, follow the usual troubleshooting steps. For example, observe eval without stats. What does it give you in requestGUID?