Hello Friends,
I am collecting an event log data from an Internal App on Windows to an Index called "pr"
I have a Primary search query, which it's query looking at this index:
index=pr EventCode=22222| table id,source_id,last_valid,last_update_time
the results are raws of data, and each raw contains one of the interesting fields is called "id" which are generic ID numbers:
Message=xxxxxxxxxxxxxxx id=64817, source_id=0, last_valid=1, last_update_time=01/01/2020
Now I have another index called "dbdata" (where I use DB-Connect App to import tables data into Splunk thru Input once a day) and it holds IDs details, I use this subsearch query:
index=dbdata sourcetype=Database_Data source=IDsData | table InsID,InsName,InsCode
the result of the query is such a table:
InsID InsName InsCode
64817 xxxx xxxx1
65304 bbbb bbbb1
65303 dddd dddd1
65072 aaaa aaaa1
I am using a Real-Time Alert which triggered on Primary Search and sends an email once logged with All Fileds values
Now I need to extract the 3 fields (InsID,InsName,InsCode) from the SubSearch (DB) when matching id and have it on the same Table on Primary Search, so my Alert can include both ID and the ID Details
Expected Result Table:
id source_id InsName InsCode source_id last_valid last_update_time
64817 0 xxxx xxxx1
65304 1 bbbb bbbb1
65303 0 dddd dddd1
65072 1 aaaa aaaa1
Today I am using the "map" command on the query, but it is not working well (it is not working on Real-Time Alerts, and it have skips in scheduled searches - always -and we missed alerts on this way )
Current used query:
index=pr EventCode=22222 | fields *
| map search="search index=dbdata earliest=-1d sourcetype=Database_Data source=IDsData InsID = $id$ | eval id=$id$ | eval source_id=$source_id$ | ev last_valid=last_valid | eval last_update_time=$last_update_time$ "
| table id,source_id,last_valid,last_update_time,InsName,InsCode
So I need to find another way in the query where I can use join/append and get aggregated data after matching the id and then use RT Alert
kindly advise
Thanks
Abed
UPDATED:
(index=pr EventCode=22222) OR (index=dbdata earliest=-1d sourcetype=Database_Data source=IDsData)
| eval id=coalesce(id,InsID)
| fields id,source_id,last_valid,last_update_time,InsName,InsCode
| stats values(*) as * dc(index) as flag by id
| where flag > 1
| table id,source_id,last_valid,last_update_time,InsName,InsCode
Hi, @skylabsit
maybe works. I don't use subsearch.
UPDATED:
(index=pr EventCode=22222) OR (index=dbdata earliest=-1d sourcetype=Database_Data source=IDsData)
| eval id=coalesce(id,InsID)
| fields id,source_id,last_valid,last_update_time,InsName,InsCode
| stats values(*) as * dc(index) as flag by id
| where flag > 1
| table id,source_id,last_valid,last_update_time,InsName,InsCode
Hi, @skylabsit
maybe works. I don't use subsearch.
Hi @to4kawa
the query you supply it results in a table with all required fields, but all data from index=dbdata are in the table (with empty values), where I am interested in only the IDs found in Primary search index=pr
Thank u
HI @skylabsit
my answer is updated. please confirm.
Hi, sorry for delayed response.
as checked last week, the last update done by you @to4kawa seems doing the job well.
although it's resulted in the Events Tab ALL the Events, but in Stats tab only the matched values
I would appreciate if you just put here a little explain of what coalesce, dc, flag use did here in the 2 searches..
Thank you!
dc()
is count of index.
I use coalesce()
to create common fields.
cf. coalesce