I want to combine 3 different source type in single table. The column names are different in all the 3 source type to join them.
Source type = a
ID name location
Source type = b
Source type = c
Kindly help to get output as follows:
PID name location country contact_number
There are more than 50 column I want in table.
Thank & Regards
if PID is equal to ID (I hope it is, or else you either didn't give us enough information to solve the problem, or the problem is unsolveable because they're not actually related records... 🙂 )
(sourcetype=a AND index=foo) OR (sourcetype=b AND index=bar) OR (sourcetype=c)
| eval master_id = case(sourcetype=a, ID, sourcetype=b, PID, sourcetype=c, id)
| stats values(*) as * by master_id
You can use, like I did, an eval/case statement to collapse all possible field names that are your ID into one field you can stats on.
Or you could use the coalesce function.
Or a couple of eval/if statements.
And the `stats values(*) as * by ...` is really just shortcut to "doing all the fields". You could replace it with values(field1) as newnameforField1, values(field2) as ...
Or list(field1) as newnameforField1, ...
or any other pile of possibilities.
The idea is just pile them all together, create a new ID that is the same field name for all, then let stats "group" then by that new id field.