Hi, i've this table
R VIP state R1 18.104.22.168 Master R2 22.214.171.124 Backup
I want to join the table so the result will be as follows
R_A R_B VIP state_A state_B R1 R2 126.96.36.199 Master Backup
If i'm using VIP as the ID for join, R1 can join with him self and also R2, this is the result
R_A R_B VIP state_A state_B R1 R1 188.8.131.52 Master Master
How to avoid this happen? so the join will happen only if some value on the field is not the same
This fakes your data:
| makeresults | eval raw="R1 184.108.40.206 Master::R2 220.127.116.11 Backup" | makemv delim="::" raw | mvexpand raw | rex field=raw "(?<R>\S+)\s+(?<VIP>\S+)\s+(?<state>.+)" | table R VIP state
This is your solution:
| stats list(*) AS * first(R) AS R_A last(R) AS R_B first(state) AS state_A last(state) AS state_B BY VIP | rename COMMENT AS "eval R_A=mvindex(R,0), R_B=mvindex(R,1), state_A=mvindex(state,0), state_B=mvindex(state,1)" | table R_A R_B VIP state_A state_B
@woodcock... After posting my answer I realized you had already answered this with a better query than what I was suggesting (so I deleted mine). However, I felt that values() should be used instead of list() to get only unique values of R and state, only to cover scenario where data might contain multiple occurrences. However, @pakerwe can confirm the same. Also we would need to know whether there are only two possible values like RA, RB or can there be more like R_C for R3 possible?
Also on an irrelevant note, following is another way to mock the data:
| makeresults | eval R="R1" | eval VIP="18.104.22.168" | eval state="master" | append [ | makeresults | eval R="R2" | eval VIP="22.214.171.124" | eval state="backup"] | table R VIP state
You must not use
values because it resorts the order alphabetically and we must retain the alignment/correlation of the values between the
state fields; this is why I deliberately used
list. Also, I modified the answer to eliminate the necessity of multi-valued fields entirely.
@woodcock...In this case all values are required to be presented in the single row, which means correlation is actually only required between VIP and remaining two fields and not within R and state. However, again @pakerwe should confirm the data and requirement.