Good day, I am trying to get a dashboard up and going to easily find the difference between two users groups. I get my information pulled from AD into splunk and then if user1 has a group that user2 doesnt have then I can easily compare two users to see what is missing. Example users in the same department typically require the same access but one might have more privileges and that is what I want to see. So my search works fine, only problem is it only gives me the group difference and now I cant see who has that group in order to add it to the user that doesnt have the group. I want to add the user next to the group: example group user G-Google user1 G-Splunk user2 | set diff [ search index=db_assets sourcetype=assets_ad_users $user1$ | dedup displayName sAMAccountName memberOf | makemv delim="," memberOf | mvexpand memberOf | rex field=memberOf "CN=(?<Group>[^,]+)" | where Group!="" | table Group ] [ search index=db_assets sourcetype=assets_ad_users $user2$ | dedup displayName sAMAccountName memberOf | makemv delim="," memberOf | mvexpand memberOf | rex field=memberOf "CN=(?<Group>[^,]+)" | where Group!="" | table Group ]   ... View 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 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 ... View 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       ... View more

Good day,  I have a query to check my Entra logs to see what Conditional access policies gets hit. The returns results like this but I would like it to display only the policies that were success or Applied and not the ones that was not applied. CA CAName success failure failure CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined notApplied success failure CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined notApplied success success CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined What I want instead   success failure failure CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined success success CA-Signin-LocationBased CA-HybridJoined success failure CA-Signin-LocationBased CA-HybridJoined index=db_azure_entraid sourcetype="azure:monitor:aad" command="Sign-in activity" category=SignInLogs "properties.clientAppUsed"!=null NOT app="Windows Sign In" | spath "properties.appliedConditionalAccessPolicies{}.result" | search "properties.appliedConditionalAccessPolicies{}.result"=notApplied | rename "properties.appliedConditionalAccessPolicies{}.result" as CA | rename "properties.appliedConditionalAccessPolicies{}.displayName" as CAName | dedup CA | table CA CAName ... View more

Good day, I have a query to summarize data per week. Is there a way to display my tables in a better way as my dates for the path month would just be the dates in number format?  I would like to name the table Week 1, Week 2, Week 3 etc if possible. index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" OR app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base | eval app=if(url_domain="www.perplexity.ai", url_domain, app) | table user, app, _time | stats count by user app _time | chart count by app _time span=1w | sort app 0 ... View more