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

[Josh1890]

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

[gcusello]

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

[gcusello]

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

[Josh1890]

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 

[Josh1890]

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

[Josh1890]

Updated the post

[ITWhisperer]

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

[Josh1890]

Updated the post, please take a look

[ITWhisperer · Accepted solution]

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

[Josh1890]

I think this works, thanks

[gcusello]

Hi @Josh1890 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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