Splunk Search

Display fields with different values only between 2 row

yuming1127
Path Finder

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

productcolorproduct_iddescription1description2description3description4
phonebluetag_1passpass fail 
phonebluetag_2failpasspassfail

 

Desired_outcome

1)

productcolorproduct_iddescription1description3
phonebluetag_1pass 
phonebluetag_2failpass

 

or

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

product_iddescription1description3
tag_1pass 
tag_2failPass

 

Appreciate your help.

Labels (5)
Tags (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

tread_splunk
Splunk Employee
Splunk Employee

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
0 Karma

marceloalejandr
Path Finder

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

0 Karma

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

marceloalejandr
Path Finder

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)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

marceloalejandr
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

yuming1127
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Path Finder

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

0 Karma

yuming1127
Path Finder

@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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...