Search an index for two fields and join data

[JandrevdM]

Good day,

I am trying to figure out how I can join two searches to see if there is a service now ticket open for someone leaving the company and if that person is still signing into some of our platforms.

This is to get the signin details into the platform - as users might have multiple email addresses I want them all.

index=collect_identities sourcetype=ldap:query [ search index=db_mimecast splunkAccountCode=* mcType=auditLog
  |fields user
  | dedup user
  | eval email=user, extensionAttribute10=user, extensionAttribute11=user
  | fields email extensionAttribute10 extensionAttribute11
  | format "(" "(" "OR" ")" "OR" ")" 
  ]
| dedup email
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| table email extensionAttribute10 extensionAttribute11 first last identity
| stats 
     values(email) AS email
     values(extensionAttribute10) AS extensionAttribute10
     values(extensionAttribute11) AS extensionAttribute11
     values(first) AS first
     values(last) AS last
     BY identity

This is to check all leavers in service now

index=db_service_now sourcetype="snow:incident" affect_dest="STL Leaver" 
| dedup description
| table _time affect_dest active description dv_state number

Unfortunately the Supporthub does not add the email in the description and only user names and surnames.
So I would need to search the first queries 'first' 'last' against the second query to find leavers.
this is what I tried but it does not work.

index=collect_identities sourcetype=ldap:query [ search index=db_mimecast splunkAccountCode=* mcType=auditLog
  |fields user
  | dedup user
  | eval email=user, extensionAttribute10=user, extensionAttribute11=user
  | fields email extensionAttribute10 extensionAttribute11
  | format "(" "(" "OR" ")" "OR" ")" 
  ]
    [search index=db_service_now sourcetype="snow:incident" affect_dest="STL Leaver"
    | dedup description
    | rex field=description "*(?<first>\S+) (?<last>\S+)*"
    | fields first last]
| dedup email
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| stats 
     values(email) AS email
     values(extensionAttribute10) AS extensionAttribute10
     values(extensionAttribute11) AS extensionAttribute11
     values(first) AS first
     values(last) AS last
     BY identity

Search one results

identity	email	extensionattribute10	extensionattribute11	first	last
nsurname	name.surname@domain.com	nsurnameT1@domain.com	name.surname@consultant.com	name	surname

Search two will get all my tickets that was created for people leaving my company and will return results like this

_time	affect_dest	active	description	dv_state	number
2024-10-31 09:46:55	STL Leaver	true	Leaver Request for Name Surname - 31/10/2024	active	INC01

So the only way of searching would by to search the second query's description field where first and last appear

Expectations

identity	email	extensionattribute10	extensionattribute11	first	last	_time	affect_dest	active	description	dv_state	number
nsurname	name.surname@domain.com	nsurnameT1@domain.com	name.surname@consultant.com	name	surname	2024-10-31 09:46:55	STL Leaver	true	Leaver Request for Name Surname - 31/10/2024	active	INC01
jdoe	john.doe@domain.com	jdoeT1@domain.com	jdoe@worker.com	john	doe	2024-11-11 12:46:55	STL Leaver	true	John Doe Offboarding on  - 31/12/2024	active	INC02

[yuanliu]

Compared with some of your previous questions on the same subject, this is much clearer.  In Re: Search an index for two fields with a join, I gave an example based on speculation that description was unimportant.  Now that you illustrate expected results, I no longer have to read your mind.  The illustrated results also implies that there can be a different format in description, and that fields first and last are all lower-case, while name in description uses the first-cap rule.  So, instead of using the second search as subsearch to limit the first search, simply append output from second search and do stats on events from both.

index=collect_identities sourcetype=ldap:query
    [ search index=db_mimecast splunkAccountCode=* mcType=auditLog
    |fields user
    | dedup user
    | eval email=user, extensionAttribute10=user, extensionAttribute11=user
    | fields email extensionAttribute10 extensionAttribute11
    | format "(" "(" "OR" ")" "OR" ")" 
    ]
| dedup email
| append
    [search index=db_service_now sourcetype="snow:incident" affect_dest="STL Leaver"
    | dedup description
    | rex field=description "Leaver Request for (?<first>\S+) (?<last>\S+) -"
    | rex field=description "(?<first>\S+) (?<last>\S+) Offboarding on -"
    | eval first = lower(first), last = lower(last)
    ]
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| fields identity	email	extensionattribute10	extensionattribute11	first	last	_time	affect_dest	active	description	dv_state	number
| stats 
     values(*) as * min(_time) as _time
     BY first last

Hope this helps.