How to JOIN the same table?

[pakerwe]

Hi, i've this table

R      VIP            state
R1     1.1.1.1        Master
R2     1.1.1.1        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           1.1.1.1         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           1.1.1.1         Master          Master

How to avoid this happen? so the join will happen only if some value on the field is not the same

[woodcock]

This fakes your data:

| makeresults 
| eval raw="R1     1.1.1.1        Master::R2     1.1.1.1        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

[jplumsdaine22]

That is some crazy feature there with the rename command. Is there any documentation on that?

[DalJeanis]

seriously weird. And it has identical results to removing the "rename COMMENT AS " and the end quote, so what's the point?

[woodcock]

It is the previous code, which does work. It is a comment, that is all. The longer the search, the more important it is to comment your code.

[woodcock]

https://answers.splunk.com/answers/48865/add-a-comment-to-a-search.html

[jplumsdaine22]

oh derp I thought the eval statement was getting executed. lol never mind

[niketn]

@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 R_A, R_B 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="1.1.1.1" 
| eval state="master" 
  | append 
  [ | makeresults 
    | eval R="R2" 
    | eval VIP="1.1.1.1" 
    | eval state="backup"]
| table R VIP state

[woodcock]

You must not use values because it resorts the order alphabetically and we must retain the alignment/correlation of the values between the R and state fields; this is why I deliberately used list. Also, I modified the answer to eliminate the necessity of multi-valued fields entirely.

[niketn]

@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.

[woodcock]

I disagree. I am sure that the A values are to be correlated and also the B values. That is half the point.