Display fields with different values only between 2 row

[yuming1127]

Hi,

I cannot found any similar thread on this issue, my aim is to display fields with different values between 2 row, so my problem will be...

my search...

statistics view from my search

product	color	product_id	description1	description2	description3	description4
phone	blue	tag_1	pass	pass		fail
phone	blue	tag_2	fail	pass	pass	fail

Desired_outcome

1)

product	color	product_id	description1	description3
phone	blue	tag_1	pass
phone	blue	tag_2	fail	pass

or

2)  if option 1 not achievable,  maybe this work as well.

product_id	description1	description3
tag_1	pass
tag_2	fail	Pass

Appreciate your help.

[tread_splunk]

Adapting the solution provided by @ITWhisperer to provide Desired Outcome 1.

| makeresults 
| eval _raw="product	color	product_id	description1	description2	description3	description4
phone	blue	tag_1	pass	pass	 	fail
phone	blue	tag_2	fail	pass	pass	fail" 
| multikv forceheader=1 
| fields - _* linecount 
| eval pKey=product.":".color.":".product_id 
| fields - product color product_id 
| transpose 0 
| eval same=if('row 1'=='row 2',"same","different") 
| where same="different" 
| fields - same 
| transpose 0 header_field=column 
| rex field=pKey "^(?<product>.*):(?<color>.*):(?<product_id>.*)" 
| fields - column pKey

[marceloalejandr]

@ITWhisperer 

We have a similar question to this thread but in our case we're trying to compare 2 lookup files for user-IDs and if there's a match do a count of all the accounts that have a .admin in the name.   

We're able to do a basic compare using "where isnull(userPrincipalName)" but not sure what's needed to compare if the user exist, then do a count .admin usernames.

Here's what we have so far: 
| inputlookup UserID-File.csv | lookup UserID2-File userPrincipalName OUTPUTNEW userPrincipalName as userPrincipalName | where isnull(userPrincipalName)

Any thoughts?  Thanks in advanced.  

[ITWhisperer]

| regex userPrincipalName="\.admin"
| stats count

[marceloalejandr]

Thank you so much for your reply.  

Since this is comparing 2 lookup files, the common field between the 2 files is the userPrincipalName.  However we need to extract the sAMAccountName (which was exported from Active Directory) that contains a userID that have a .sa or .svc at the end of the name. 
Any thoughts on how to achieve this?

To further clarify the 1st lookup file in the SPL is the file from AD and 2nd file is not.  We've notice the 1st file referenced is where the results return from if it meets the search criteria.  

| inputlookup ADUserID-File.csv | lookup ExDbUserID2-File userPrincipalName OUTPUTNEW userPrincipalName as userPrincipalName | where isnull(userPrincipalName)

[ITWhisperer]

I am not sure I understand. Is this is a third source that you want to lookup and compare or something that is already in one of the two sources you have used already?

[marceloalejandr]

---

@ITWhisperer wrote:

I am not sure I understand. Is this is a third source that you want to lookup and compare or something that is already in one of the two sources you have used already?

---

Sorry for the confusion.  These are same 2 sources.   

[gcusello]

Hi @yuming1127,

one little question: the pair product/color is unique in your results or not?

if yes, you can use stats to have your need.

your_search
| stats dc(product_id) AS dc_product_id values(product_id) AS product_id dc(description1) AS dc_description1 values(description1) AS description1	dc(description3) AS dc_description3 values(description3) AS description3 BY product color
| where dc_product_id>1 OR dc_description1>1 OR dc_description1>3
| table product color description1 description3

Ciao.

Giuseppe

[yuming1127]

great effort but I don't get desired outcome,
description1-description4 is just example, which the values are randomize, my real data have like 70 + description.
btw thanks for the reply

[ITWhisperer]

Sorry @gcusello, but this doesn't quite work if some of the values are null as they don't get included in the dc count. However, you could use fullnull to get around this. (Also, there is a typo in the where clause, it should be dc_description3>1.

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="product	color	product_id	description1	description2	description3	description4
phone	blue	tag_1	pass	pass	 	fail 
phone	blue	tag_2	fail	pass	pass	fail"
| multikv forceheader=1
| fields - _* linecount
| transpose 0
| eval same=if('row 1'=='row 2',"same","different")
| where same="different"
| fields - same
| transpose 0 header_field=column
| fields - column

[yuming1127]

Great one,
any idea to exclude product and color from the matching? or re-append product and color after the matching.

[yuming1127]

@ITWhisperer 

It's ok, i'm okay to edit the query
| eval same=if('row 1'=='row 2' AND 'row 1' != "phone" AND 'row 1' != "blue","same","different")

able to get expected outcome (1),

Thanks alot