Splunk Search

How to match and obtain a fields values from subsearch based on parent search filed value and aggregate on same table

skylabsit
Engager

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

0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

to4kawa
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

skylabsit
Engager

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

0 Karma

to4kawa
SplunkTrust
SplunkTrust

HI @skylabsit
my answer is updated. please confirm.

0 Karma

skylabsit
Engager

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!

0 Karma

to4kawa
SplunkTrust
SplunkTrust

dc() is count of index.
I use coalesce() to create common fields.

cf. coalesce

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!