Hello All, I have been scouring the community and other boards but for the life of me cannot create a SPL query to get the results I need. Below is what I am trying to accomplish, any direction and help would be greatly appreciated. I have 2 sourcetypes and 1 lookup table. SourceA has fields (ID_a, name, date, trimmed_name, env, etc..) SourceB has fields (ID, title_id, title, description solution, etc...) Lookup has fields (trimmed_name, department, etc...) SourceA gets updated weekly, therefore in the existing query I have earliest=-7d to exclude previous data Characteristics of SourceA: name is the only unique field, ID_a will be duplicated across name depending on date field, env is duplicated across the dataset, trimmed_name is a field created from trimming name SourceA ID_a and SourceB ID are the common field between the two datasets Characteristics of SourceB: there are duplicates of ID, title_id, title, description, solution. When deduping title_id, values of title, description, solution, etc... are dedup'd and become unique. This is why I called SourceB a knowledge base. We can dedup SourceB down to the fields above to get a finite list. SourceB will have multiple values of ID. But we only need to return fields\values where the dedup'd SourceA ID_a exists. This means from 100 events in SourceA, dedup'd by ID_a, results in 2 ID_a. These 2 ID_a values is what we to find in Source B and return title_id, title, description solution values for. At a minimum, final results needed per source are: SourceA: name, date, env SourceB: title_id, title, description, solution (other fields can be omitted, assuming we can add specific fields back as needed) Lookup: department (other fields can be omitted, assuming we can add specific fields back as needed) SourceA: ID_a name trimmed_name date ABC ABC_n AB 01/01/2023 ABC ABC_n1 AB 01/02/2023 ABC ABC_n2 AB 01/03/2023 XYZ XYZ_n XY 02/01/2023 XYZ XYZ_n1 XY 02/02/2023 SourceB: ID title_id title description solution ABC 12345 ABC_t ABC_d ABC_s ABC 23456 ABC_t1 ABC_d1 ABC_s1 ABC 93648 ABC_t2 ABC_d2 ABC_s2 XYZ 23456 XYZ_t XYZ_d XYZ_s XYZ 38840 XYZ_t1 XYZ_d1 XYZ_s1 MNO 43245 MNO_t MNO_d MNO_s MNO 36485 MNO_t1 MNO_d1 MNO_s1 RST 84678 RST_t RST_d RST_s Lookup Table: trimmed_name department AB ABC_dep XY XYZ_dep MN MNO_dep Intended Result: sourceA.name sourceB.title_id sourceB.title sourceB.description sourceB.solution sourceA.date lookup.department ABC_n 12345 ABC_t ABC_d ABC_s 01/01/2023 ABC_dep ABC_n 23456 ABC_t1 ABC_d1 ABC_s1 01/01/2023 ABC_dep ABC_n 93648 ABC_t2 ABC_d2 ABC_s2 01/01/2023 ABC_dep ABC_n1 12345 ABC_t ABC_d ABC_s 01/02/2023 ABC_dep ABC_n1 23456 ABC_t1 ABC_d1 ABC_s1 01/02/2023 ABC_dep ABC_n1 93648 ABC_t2 ABC_d2 ABC_s2 01/02/2023 ABC_dep ABC_n2 12345 ABC_t ABC_d ABC_s 01/03/2023 ABC_dep ABC_n2 23456 ABC_t1 ABC_d1 ABC_s1 01/03/2023 ABC_dep ABC_n2 93648 ABC_t2 ABC_d2 ABC_s2 01/03/2023 ABC_dep XYZ_n 23456 XYZ_t XYZ_d XYZ_s 02/01/2023 XYZ_dep XYZ_n 38840 XYZ_t1 XYZ_d1 XYZ_s1 02/01/2023 XYZ_dep XYZ_n 23456 XYZ_t XYZ_d XYZ_s 02/02/2023 XYZ_dep XYZ_n 38840 XYZ_t1 XYZ_d1 XYZ_s1 02/02/2023 XYZ_dep Query, I have been playing with... From what I gather, I have events for the query but nothing displays when I try to get data from SourceB and SourceA at the same time. index="myindex" (sourcetype="SourceB" type=INFO) OR (sourcetype="SourceA" date<2023-02-14 env=envA)
| rename ID_a AS ID
| fields ID, name, title, description, solution, date, env
| stats count values(*) AS * values(date) values(title) values(env) BY name
| where count=1
... View more
