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 pl...
See more...
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