Splunk Search

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

mnarmada
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

to4kawa
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

mnarmada
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

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

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

mnarmada
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

p_gurav
Champion
0 Karma

mnarmada
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...