Splunk Search

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

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

Influencer

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

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

Esteemed Legend

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.

0 Karma

Esteemed Legend
0 Karma

Influencer

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

0 Karma

SplunkTrust
SplunkTrust

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

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

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!