Search an index for two fields with a join

[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 snow

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

Unfortunately the Shub does not add the email in the description and only user names and surnames.

So I would need to search the first querys '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" ")" 
]
| dedup email
| eval identity=replace(identity, "Adm0", "")
| eval identity=replace(identity, "Adm", "")
| eval identity=lower(identity)
| table email extensionAttribute10 extensionAttribute11 first last identity
| search "*first*" "*last*" [ search index=db_service_now sourcetype="snow:incident" affect_dest="STL Leaver" 
  | dedup description
  | table _time affect_dest active description dv_state number
]
| stats values(email) AS email values(extensionAttribute10) AS extensionAttribute10 values(extensionAttribute11) AS extensionAttribute11 values(first) AS first values(last) AS last BY identity

[ITWhisperer]

It is not always easy to decipher what your search is trying to do without some sample representative anonymised events and expected results to see what your searches are doing. Please can you provide some events, preferably using the code block </> button to insert them into your reply.

[JandrevdM]

Sorry for the confusion. So search one gets the result like this.

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

[yuanliu]

You didn't illustrate what is the expected results look like.  Based on the last stats in your OP, you only want to filter for first, last of people with a leave ticket, not to add any information about the ticket.  Is this correct?

In that case, just extract first and last in the second search and use it as subsearch, like this.

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 "Leaver Request for (?<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

Note the extraction of first and last depends on the precise format in description; additionally, it assumes that first and last contains no white space.

[JandrevdM]

Thanks - this is defnitely helping a lot. I would love to join the tables in the results. And what I also noticed is that the description isn't always exactly "Leaver Request for" that is why I added affect_dest="STL Leaver" which checks just for leaver tickets

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