Splunk Search
Highlighted

How to arrange the common values of different fields in the same row and uncommon values after the common values

Path Finder

Hello,

I have a data from two different sourcetypes. In that data, I have two specific columns where in I have to check whether there are common values in both fields or not and if there are common values in bot the fields, I have to show then on the same row in their respective fields and uncommon fields next to the common fields. For the common files, the status should be yes else no.

The data is like below:

Field1 Field2
A B

C D
Z L
L A
B K
S C
D M

Expected Output:
Field1 Field2 Status
A A Yes
C C Yes
L L Yes
L Z No
B K No
S S Yes

D M NO

Please help me... I have used join, but it is giving blank values in the middle of the table

0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Champion
0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Path Finder

@p_gurav , This is working and helped me. Thanks for the help

0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Legend

@mnarmada gap is expected, what do you mean by uncommon fields next to the common fields?
Also do you expect the output to be something like the following or the one in your question?

Field1 Field2 Status
  A      A      Yes
  C      C      Yes
  Z      K      No
  L      L      Yes
  B      B      Yes
  S      S      Yes
  D      M      No

In case you want output like the one I have posted here, could you tell me how to correlate Z and K and D and M together as two unmatched rows?

In case you want the output as per your question could you please explain the logic for L Z No and B K No instead of Z K No and B B Yes?

Community would be able to assist you better if you can add more details on the logic for correlation also your use case. If you have partially implemented code that you have tried and which is not working for you, you can post the same as well. While posting the details please mask/anonymize any sensitive data.

0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Path Finder

@niketnilay The field1 and field2 are two columns extracted from two sources through regular expressions. These two fields contains some standard codes. One file generates from one system and another file from another system. So, what I have to do is whether the standard code available in both the files or not by date. Example, today I have received 20 values in field1 and 20 or more than 20 values in field2. I have to compare what are the matched values and unmatched values like you have posted. Uncommon in the sense if field2 is having more than 20 values it will not match with field1, in this case I have to show as unmatched.

Please let me know if still the requirement is not clear. I will try to keep it in best way with my tried code.

0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Path Finder

@niketnilay The field1 and field2 are two columns extracted from two sources through regular expressions. These two fields contains some standard codes. One file generates from one system and another file from another system. So, what I have to do is whether the standard code available in both the files or not by date. Example, today I have received 20 values in field1 and 20 or more than 20 values in field2. I have to compare what are the matched values and unmatched values like you have posted. Uncommon in the sense if field2 is having more than 20 values it will not match with field1, in this case I have to show as unmatched.

Please let me know if still the requirement is not clear. I will try to keep it in best way with my tried code.

0 Karma
Highlighted

Re: How to arrange the common values of different fields in the same row and uncommon values after the common values

Ultra Champion

sample:

| makeresults
| eval _raw="Field1,Field2
A,A
C,C
Z,K
L,L
B,B
S,S
D,M"
| multikv forceheader=1
| table Field1 Field2
| eventstats values(Field2) as Field2
| eval Status=nullif(match(Field2,Field1),NULL)

Hi, Is Yes No better?

recommend:

index=yours source=A OR source=B
| rex .....
| stats values(Field2) as Field2 by Field1
| eval Status=if(match(Field2,Field1),"Yes","No")
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.