topic Re: how to compare 2 different rows using count in Splunk Search

How to compare 2 different rows using count?

villnooB — Thu, 29 Dec 2022 04:17:58 GMT

Hi guys,

Can you please help me , I am trying to create a query in which it shows if a user is in a different location in the sameday it will only prioritize one of it. please see below

| convert timeformat="%F %H:%M" ctime(zone) as ctime | stats count by user fullname country ctime location | rename fullname as "Name", ctime as DateStamp, location as "Location", user as "NetworkID", country as "Country" | fields - count | sort 0 NetworkID

This is what i am getting if I'm using the query above

NetworkID	Name	Country	DateStamp	Location
userA	A Sample	Spain	12-26-2022	Office
userA	A Sample	Spain	12-27-2022	Office
userA	A Sample	Spain	12-27-2022	Home

and this is what I am trying to get that If it's in the same day it will only Select the office

NetworkID	Name	Country	DateStamp	Location
userA	A Sample	Spain	12-26-2022	Office
userA	A Sample	Spain	12-27-2022	Office

Thank you in advance

Re: how to compare 2 different rows using count

SanjayReddy — Wed, 28 Dec 2022 05:06:55 GMT

Hi @villnooB

can you try following qurey, highlight lines are are newly added

| convert timeformat="%F %H:%M" ctime(zone) as ctime
| stats count by user fullname country ctime location
| eval Filter=if(location="Office","Yes","NO")
| search Filter="Yes"
| rename fullname as "Name", ctime as DateStamp, location as "Location", user as "NetworkID", country as "Country"
| fields - count
| sort 0 NetworkID
|

Re: how to compare 2 different rows using count

ITWhisperer — Wed, 28 Dec 2022 07:55:38 GMT

| convert timeformat="%F %H:%M" ctime(zone) as ctime | stats count values(location) as location by user fullname country ctime | rename fullname as "Name", ctime as DateStamp, location as "Location", user as "NetworkID", country as "Country" | fields - count | eval Location=if(isnotnull(mvfind(Location, "Office")), "Office", "Home") | sort 0 NetworkID

Re: how to compare 2 different rows using count

Manasa_401 — Wed, 28 Dec 2022 08:05:59 GMT

Hello @villnooB

After rename, add the below logic

| eventstats count as multiple by DateStamp Name
| eval valid=if(multiple>1 AND Location="Home","NO","YES")
| search valid="YES"
|fields - count multiple valid

If this helps, karma would be appreciated.

Thanks,

Manasa

Re: how to compare 2 different rows using count

villnooB — Wed, 28 Dec 2022 12:10:20 GMT

@ITWhisperer

Thank you very much

Re: how to compare 2 different rows using count

villnooB — Wed, 28 Dec 2022 12:11:12 GMT

@ITWhisperer , @Manasa_401 , @SanjayReddy

Thank you very much for the help