Good day, It's been a while.  I am trying to join two indexes together to see if a ticket has been logged based on the first search. Search 1:  Is used to gather all the results. Here we will be using title, as our main source. (But we dont want to through away all the other colums) index="db_crowdstrike" sourcetype="crowdstrike:spotlight:vulnerability:json" "falcon_spotlight.status"!="closed" "falcon_spotlight.suppression_info.is_suppressed"="false" | makemv delim="\n" "falcon_spotlight.remediation.entities{}.title" | mvexpand "falcon_spotlight.remediation.entities{}.title" | stats values("falcon_spotlight.host_info.hostname") as hosts dc("falcon_spotlight.host_info.hostname") as host_count by "falcon_spotlight.remediation.entities{}.title" | eval hosts=mvsort(hosts) | table "falcon_spotlight.remediation.entities{}.title" host_count Search 2: Is used to gather all the tickets logged to our ITSM.  index=db_service_now sourcetype="snow:incident" status!="Resolved" status!="Closed" affect_dest="CrowdStrike Vulnerability Management" | dedup number description | table _time active "number" "affect_dest" "description" "dv_description" "dv_impact" "priority" "status" "assignment_group_name" "assignment_user_name" "close_code" "close_notes" "closed_at" "short_description"   in search 1 the title will be something like "Update Nvidea GeForce" in search two description will be something like "March: Low - Update Nvidea Geforce" index="db_crowdstrike" sourcetype="crowdstrike:spotlight:vulnerability:json" "falcon_spotlight.status"!="closed" "falcon_spotlight.suppression_info.is_suppressed"="false" | makemv delim="\n" "falcon_spotlight.remediation.entities{}.title" | mvexpand "falcon_spotlight.remediation.entities{}.title" | eval cs_title=trim('falcon_spotlight.remediation.entities{}.title') | where len(cs_title)>0 | stats values('falcon_spotlight.host_info.hostname') AS hosts dc('falcon_spotlight.host_info.hostname') AS host_count BY cs_title | eval hosts=mvsort(hosts), lc_title=lower(cs_title), key=1 | join type=left max=0 key [ search index=db_service_now sourcetype="snow:incident" status!="Resolved" status!="Closed" affect_dest="CrowdStrike Vulnerability Management" | dedup number | eval desc=lower(coalesce(dv_description, description, short_description)) | eval key=1 | fields key number status priority assignment_group_name assignment_user_name desc ] | eval matched = if(like(desc, "%" . lc_title . "%"), 1, 0) | eval number = if(matched=1, number, null()) | eval status = if(matched=1, status, null()) | eval priority = if(matched=1, priority, null()) | eval assignment_group_name = if(matched=1, assignment_group_name, null()) | eval assignment_user_name = if(matched=1, assignment_user_name, null()) | stats max(host_count) AS host_count values(hosts) AS hosts values(number) AS snow_numbers values(status) AS snow_status values(priority) AS snow_priority values(assignment_group_name) AS snow_group values(assignment_user_name) AS snow_assignee sum(matched) AS snow_count BY cs_title | eval has_ticket=if(snow_count>0,"Yes","No") | dedup snow_numbers | table cs_title host_count has_ticket snow_count snow_numbers snow_status snow_priority snow_group snow_assignee | sort -has_ticket -host_count How can I combine my searches to search the second one where it contains the first title field? This is what I tried but it is not completely what I am looking for Any assistance would be greatly appreciated! ... View more

Good day, I want to join two indexes to show all the email addresses that the user have that signed in.  This queries my mimecast signin logs  index=db_mimecast splunkAccountCode=* mcType=auditLog | dedup user | table _time, user | sort _time desc Lets say it returns a user@domain.com that singed in. I want to then join this to show all the info from  index=collect_identities sourcetype=ldap:query | 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 I tried inner join but I do not have anything that match since my results come back as this for my second query identity email extensionAttribute10 extensionAttribute11 first last USurname user@domain.com userT1@domain.com user@domain.com user@domain.com user@another.com user@domain.com user Surname   ... View more

Good day, Is there a way to join all my rows into one? My simple query    index=collect_identities sourcetype=ldap:query user | dedup email | table email extensionAttribute10 extensionAttribute11 first last identity     Shows results as, as I have more than one email email extensionAttribute10 extensionAttribute11 first last identity user@domain.com   user@consultant.com User Surname USurname userT1@domain.com user@domain.com user@domain.com User Surname USurname userT0@domain.com user@domain.com user@domain.com User Surname USurname I want to add a primary key that searches for "user@domain.com" and display all their email addresses that they have in one row.  Example email extensionAttribute10 extensionAttribute11 first last identity email2 email3 user@domain.com user@domain.com user@consultant.com  User Surname USurname userT1@domain.com userT0@domain.com ... View more

Good day, I have done a join on two indexes before to add more information to one event. example get department for a user from network events. But now I want to add two indexes to give me more data.  Example index one will display: host 1 10.0.0.2 host 2 10.0.0.3 And index two will display:  host 3 10.0.0.4 host 1 10.0.0.2 What I want is: host 1 10.0.0.2 host 2 10.0.0.3 host 3 10.0.0.4 index=db_azure_activity sourcetype=azure:monitor:activity change_type="virtual machine" | rename "identity.authorization.evidence.roleAssignmentScope" as subscription | dedup object | where command!="MICROSOFT.COMPUTE/VIRTUALMACHINES/DELETE" | table change_type object resource_group subscription command _time | sort object asc index=* sourcetype=o365:management:activity | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceName" as ResourceName | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.CloudProvider" as CloudProvider | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceType" as ResourceTypes | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.EventType" as EventType | where ResourceTypes="Microsoft.Compute/virtualMachines" OR ResourceTypes="microsoft.compute/virtualmachines" | eval object=mvdedup(split(ResourceName," ")) | eval Provider=mvdedup(split(CloudProvider," ")) | eval Type=mvdedup(split(ResourceTypes," ")) | dedup object | where EventType!="Microsoft.Security/assessments/Delete" | table object, Provider, Type * | sort object asc ... View more

Good day, I am trying to find the latest event for my virtual machines to determine if they are still active or decommissioned. The object is the hostname and the command is where I can see if a device was deleted or just started. I will then afterwards add the command!="*DELETE" index=db_azure_activity sourcetype=azure:monitor:activity change_type="virtual machine" | rename "identity.authorization.evidence.roleAssignmentScope" as subscription | stats max(_time) as time by command object subscription change_type resource_group | convert ctime(time) ```| dedup object``` | table change_type object resource_group subscription command time | sort object asc ... View more

Good day, I have a query that I would like to add more information onto. The query pulls all users that accessed a AI site and gives my data for weekdays as a 1 or 0 if the site was accessed. The query 1 gets a user from index db_it_network and I would like to add the department of each user by querying theindex=collect_identities sourcetype=ldap:query The users are displayed in the collect identities index as 'email' and their department in the bunit field    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 | where date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" OR date_wday="thursday" OR date_wday="friday" | eval app=if(url_domain="www.perplexity.ai", url_domain, app) | table user, app, date_wday | stats count by user app date_wday | chart count by user app | sort app 0      Note: the |stats | chart is necessary to distinct so that one user return results for one app per day ... View more

Hi All, I have two queries which searches for users that use an app. The apps are not in the same fields which was why I had to split the queries. But now I want to join the queries to get the results Query 1 index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" | table user, url_domain, date_month | stats count by user url_domain date_month  | chart count by url_domain date_month  | sort url_domain 0 Query 2 index=db_it_network sourcetype=pan*  app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base | table user, app, date_month | stats count by user app date_month  | chart count by app date_month  | sort app 0 results example that I want App August July claude-base 123 120 google-gemini 124 42 openai 153 123 bing-ai-base 212 232 www.perplexity.com 14 12 ... View more

Hi, I am trying to get a list off all users that hit our AI rule and see if this increase or decrease over the timespan of 90 days. I want to see the application they use and see the last three months display as columns with a count of amount of users. Example below Applications June(Month1) July(Month2) August(Month3) chatGPT 213 233 512   index=db_it_network sourcetype=pan* rule=g_artificial-intelligence-access | table user, app, date_month ```| dedup user, app, date_month``` | stats count by date_month, app | sort date_month, app 0 | rename count as "Number of Users" | table date_month, app, "Number of Users" ... View more