How to compare 2 different rows using count?

[villnooB]

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

[villnooB]

@ITWhisperer , @Manasa_401 , @SanjayReddy 

Thank you very much for the help

[Manasa_401]

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

[ITWhisperer · Accepted solution]

| 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

[villnooB]

@ITWhisperer 

Thank you very much

[SanjayReddy]

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
|