Let me reconstruct this step by step. No matter what technique you choose, matching logic is the key question. 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" The above is from OP. Is description always composed of some sort of classifier ("March: Low") followed by a dash then title? I know that you mentioned a version of your implementation as "not a proper solution". Can you elaborate why? Because you have never described matching logic in detail (including what kind of characters/strings have to throw off any specific implementation), this is the simplest I can think of. So I will assume that it IS the proper solution - maybe it was your implementation that had a problem: | eval match_key = coalesce(title,
mvindex(split(description, " - "), 1, -1)) However, in another reply, you said "falcon_spotlight.remediation.entities{}.title" will contain "Update Nvidea Geforce" And description might contain the same as the title but have more information, "March - Low Update Nvidea Geforce" This example quite contradicts the example in the OP. What is the real logic? Is title always contained in description? Your third illustrated SPL seems to suggest so. This more generic logic can be expressed in the following: | eval match_key = if(description LIKE "%" . title . "%", title, null()) The second strategy is doable but much more expensive in practice. So, I will focus on the simpler strategy. The above two snippets are just concepts expressed in SPL, not a solution. To really construct something usable, let me circle back to @PickleRick's comment about join: only use as a last resort. Next, let's try to find a usable solution. I will first illustrate the most commonly recommended method, i.e., use one index search to include all necessary events. You haven't told us how the desired result should look like. So, I can only speculate based on the third SPL you illustrated: You want select stats grouped by the short title. Here is the first method: (index="db_crowdstrike" sourcetype="crowdstrike:spotlight:vulnerability:json"
"falcon_spotlight.status"!="closed" "falcon_spotlight.suppression_info.is_suppressed"="false"
``` of interest: falcon_spotlight.remediation.entities{}.title, falcon_spotlight.host_info.hostname ```
) OR (
index=db_service_now sourcetype="snow:incident" status!="Resolved" status!="Closed" affect_dest="CrowdStrike Vulnerability Management"
``` of interest: number description priority status assignment_group_name assignment_user_name ```
)
| rename falcon_spotlight.host_info.hostname as host
| eval title = coalesce(trim('falcon_spotlight.remediation.entities{}.title'),
mvindex(split(description, " - "), 1, -1))
| mvexpand title
| stats
dc(host) AS host_count
values(host) 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
dc(number) AS snow_count
BY title
| eval has_ticket=if(snow_count>0,"Yes","No")
| table cs_title host_count has_ticket snow_count snow_numbers snow_status snow_priority snow_group snow_assignee
| sort -has_ticket -host_count Here, I removed a lot of calculations that I believe are not necessary, such as makemv (flattened JSON array elements falcon_spotlight.remediation.entities{}.* are already multivalue; they are NOT newline delimited), mvsort (any values output is already sorted lexically), etc. The only expensive calculation in the above is mvexpand. You may get inconsistent data if there are too many events. A second method is as @livehybrid suggested, using append. It only marginally reduces the mvexpand problem; it can introduce a different memory problem although I believe db_service_now events are a lot sparser so it is probably not immaterial. (index="db_crowdstrike" sourcetype="crowdstrike:spotlight:vulnerability:json"
"falcon_spotlight.status"!="closed" "falcon_spotlight.suppression_info.is_suppressed"="false"
``` of interest: falcon_spotlight.remediation.entities{}.title, falcon_spotlight.host_info.hostname ```
| rename falcon_spotlight.host_info.hostname as host
| eval title = trim('falcon_spotlight.remediation.entities{}.title')
| mvexpand title
| append
[ search
index=db_service_now sourcetype="snow:incident" status!="Resolved" status!="Closed" affect_dest="CrowdStrike Vulnerability Management"
``` of interest: number description priority status assignment_group_name assignment_user_name ```
| dedup number
| eval title = mvindex(split(description, " - "), 1, -1)
]
| stats
dc(host) AS host_count
values(host) 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
dc(number) AS snow_count
BY title
| eval has_ticket=if(snow_count>0,"Yes","No")
| table cs_title host_count has_ticket snow_count snow_numbers snow_status snow_priority snow_group snow_assignee
| sort -has_ticket -host_count Note in both approaches, you want to create that match key in respective datasets first. Of course, if service_now's "description" is free text and does not follow a predefined pattern as to how they match title in crowdstrike, you will need to use the second matching strategy. However, the calculation will be even more expensive than just eventstats suggested by @bowesmana . Let us know if you are this unfortunate.
... View more
