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
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
Thank you sir for the detail. Will try stats and update ( out for the weekend, no system access currently:) )
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!
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?
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