https://community.splunk.com/t5/Splunk-Enterprise-Security/I-need-to-match-the-user-col-to-the-Expired-user-col-from-two/m-p/461698#M6688 <P>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. </P> <P>| datamodel Authentication "Authentication" search<BR /> | stats count by Authentication.user | rename Authentication.user as user <BR /> | appendcols<BR /> [| datamodel Identity_Management "Expired_Identities" search<BR /> | stats count by All_Identities.LoginID]<BR /> | foreach user <BR /> [eval match=if(user=All_Identities.LoginID, user, NULL)]<BR /> | table user All_Identities.LoginID count match</P> Wed, 30 Sep 2020 04:02:16 GMT MikeVenable 2020-09-30T04:02:16Z https://community.splunk.com/t5/Splunk-Enterprise-Security/I-need-to-match-the-user-col-to-the-Expired-user-col-from-two/m-p/461698#M6688 <P>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. </P> <P>| datamodel Authentication "Authentication" search<BR /> | stats count by Authentication.user | rename Authentication.user as user <BR /> | appendcols<BR /> [| datamodel Identity_Management "Expired_Identities" search<BR /> | stats count by All_Identities.LoginID]<BR /> | foreach user <BR /> [eval match=if(user=All_Identities.LoginID, user, NULL)]<BR /> | table user All_Identities.LoginID count match</P> Wed, 30 Sep 2020 04:02:16 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/I-need-to-match-the-user-col-to-the-Expired-user-col-from-two/m-p/461698#M6688 MikeVenable 2020-09-30T04:02:16Z https://community.splunk.com/t5/Splunk-Enterprise-Security/I-need-to-match-the-user-col-to-the-Expired-user-col-from-two/m-p/461699#M6689 <PRE><CODE>| 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 </CODE></PRE> <P>Hi, @MikeVenable<BR /> how about this?</P> Wed, 05 Feb 2020 12:10:00 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/I-need-to-match-the-user-col-to-the-Expired-user-col-from-two/m-p/461699#M6689 to4kawa 2020-02-05T12:10:00Z