topic Re: Display fields with different values only between 2 row in Splunk Search

Display fields with different values only between 2 row

yuming1127 — Tue, 18 May 2021 01:40:31 GMT

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

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

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.

Re: Display fields with different values only between 2 row

ITWhisperer — Tue, 18 May 2021 06:48:44 GMT

Re: Display fields with different values only between 2 row

gcusello — Tue, 18 May 2021 06:51:26 GMT

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

Re: Display fields with different values only between 2 row

ITWhisperer — Tue, 18 May 2021 07:05:06 GMT

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.

Re: Display fields with different values only between 2 row

yuming1127 — Tue, 18 May 2021 07:53:59 GMT

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

Re: Display fields with different values only between 2 row

yuming1127 — Tue, 18 May 2021 07:56:48 GMT

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

Re: Display fields with different values only between 2 row

yuming1127 — Tue, 18 May 2021 08:08:51 GMT

@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

Re: Display fields with different values only between 2 row

marceloalejandr — Fri, 05 Nov 2021 14:03:39 GMT

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

Re: Display fields with different values only between 2 row

ITWhisperer — Fri, 05 Nov 2021 14:23:26 GMT

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

Re: Display fields with different values only between 2 row

marceloalejandr — Fri, 05 Nov 2021 16:35:26 GMT

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)

Re: Display fields with different values only between 2 row

ITWhisperer — Fri, 05 Nov 2021 16:21:00 GMT

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?

Re: Display fields with different values only between 2 row

marceloalejandr — Fri, 05 Nov 2021 16:42:28 GMT

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

Re: Display fields with different values only between 2 row

tread_splunk — Fri, 05 Nov 2021 18:07:45 GMT

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