I need to match the user col to the Expired user col from two different datasets

[MikeVenable]

I need an SPL that will take input from Authentication dataset in the Authentication datamodel, at the same time taking the expired_identities dataset from the Identity_Management datamodel. I want only the matches then i need the event time from when the authentication happened and when the identity was expired then eval. Below is what I have so far, it appends both data sources together and foreach is supposed to look for matches, it does, but I think it is only comparing the side by side column not searching the entire column for each entry in users. Thanks any help would be greatly appreciated.

| datamodel Authentication "Authentication" search
| stats count by Authentication.user | rename Authentication.user as user
| appendcols
[| datamodel Identity_Management "Expired_Identities" search
| stats count by All_Identities.LoginID]
| foreach user
[eval match=if(user=All_Identities.LoginID, user, NULL)]
| table user All_Identities.LoginID count match

[to4kawa · Accepted solution]

| datamodel Authentication "Authentication" search 
| stats count by Authentication.user 
| rename Authentication.* as *
| append 
    [| datamodel Identity_Management "Expired_Identities" search 
    | stats count by All_Identities.LoginID
    | rename All_Identities.* as *] 
| eval user=coalesce(user, LoginID)
| stats sum(count) as count by user

Hi, @MikeVenable
how about this?