Help with join in searches

[mailravi]

Hi

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

[gcusello]

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

[mailravi]

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

[gcusello]

Hi @mailravi,

ok, let me know.

Ciao 

Giuseppe

P.S.: Karma Points are appreciated 😉

[mailravi]

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!

[yuanliu]

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?

[gcusello]

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