Splunk Search
Highlighted

How to JOIN the same table?

New Member

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

Tags (3)
0 Karma
Highlighted

Re: How to JOIN the same table?

Esteemed Legend

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
Highlighted

Re: How to JOIN the same table?

Legend

@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="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



| eval message="Happy Splunking!!!"


Highlighted

Re: How to JOIN the same table?

Esteemed Legend

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.

0 Karma
Highlighted

Re: How to JOIN the same table?

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: How to JOIN the same table?

Esteemed Legend

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

0 Karma
Highlighted

Re: How to JOIN the same table?

Influencer

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

Highlighted

Re: How to JOIN the same table?

Esteemed Legend
0 Karma
Highlighted

Re: How to JOIN the same table?

Influencer

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

0 Karma
Highlighted

Re: How to JOIN the same table?

SplunkTrust
SplunkTrust

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

0 Karma