topic Re: How to compare same fields from different events to find past occurrence in Splunk Search

How to compare same fields from different events to find past occurrence

Josh1890 — Thu, 25 Apr 2024 10:00:05 GMT

Editing to make it better:
Let's say I have login events with 2 important fields: past_deviceid, new_deviceid
I want to check if the new_deviceid was assigned to a different user in the past, for that I need to compare the value of the field to the past_deviceid field of past events and I'm kinda stuck here

In login events where the user uses their usual device, there'll be only 1 field called past_deviceid, we get the new_deviceid field only when there's a login with a new device

In the end I want to have a table that shows the new_deviceid by all the users that hold/held it where there's more than 1 user

Example:

events with only 1 device:

User: Josh
old_Device: iPhone12348

---------------------------
User: John
old_Device: samsung165

----------------------------
case where there's a new device:

User: Jane
old_Device: iPhone17778
new_Device: samsung165

I want to have the following table, I guess the stats command fits here:

DeviceID

User

samsung165

Jane

John

Re: How to compare same fields from different events to find past occurrence

ITWhisperer — Thu, 25 Apr 2024 09:26:01 GMT

Please can you share some anonymised representative events demonstrating your issue?

Re: How to compare same fields from different events to find past occurrence

gcusello — Thu, 25 Apr 2024 09:48:58 GMT

Hi @Josh1890 ,

as @ITWhisperer said, some sample coud help to better understand your requirement.

Anyway, if I correctly understood, you want to know if the new_id was assigned in the past to some different users; in other words, if there are more users with assigned the same new_id, is this correct?

It isn't so cluear for me the reation between new_id and past_id.

Anyway, in this case, you could try to run something like this:

<your_search> | stats dc(user) AS user_count values(user) AS user BY new_id | where user_count>1

Ciao.

Giuseppe

Re: How to compare same fields from different events to find past occurrence

Josh1890 — Thu, 25 Apr 2024 09:49:56 GMT

Updated the post, please take a look

Re: How to compare same fields from different events to find past occurrence

Josh1890 — Thu, 25 Apr 2024 09:50:23 GMT

Updated the post

Re: How to compare same fields from different events to find past occurrence

Josh1890 — Thu, 25 Apr 2024 09:53:54 GMT

And to answer your question, I want to see if the value of new_deviceid exists in other users old_deviceid field, meaning it was assigned to them in the past

Re: How to compare same fields from different events to find past occurrence

gcusello — Thu, 25 Apr 2024 10:11:23 GMT

Hi @Josh1890 ,

please try this:

<your_search> | stats dc(User) AS user_count values(User) AS user BY DeviceID | where user_count>1

Ciao.

Giuseppe

Re: How to compare same fields from different events to find past occurrence

Josh1890 — Thu, 25 Apr 2024 10:50:28 GMT

Hey Giuseppe, the solution doesn't work since it doesn't include users who have the value of new_device inside their old_device field

Check the example in the post

Re: How to compare same fields from different events to find past occurrence

ITWhisperer — Thu, 25 Apr 2024 11:22:55 GMT

| eval devices=mvappend(old_device,new_device) | stats values(user) as users by devices

Re: How to compare same fields from different events to find past occurrence

Josh1890 — Sat, 27 Apr 2024 10:19:42 GMT

I think this works, thanks

Re: How to compare same fields from different events to find past occurrence

gcusello — Sat, 27 Apr 2024 10:20:31 GMT

Hi @Josh1890 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉