Add "A" field from another index if "B" and ""C" are equal across indexes
I have search that returns events with fields
Index=1
"B"
"C"
"D"
index=2
"A"
"B"
"C"
On a search in index 1, I would like to kind of 'lookup' dynamicaly field from index=2. I know "B" "C" will most likelly be equal, so I would like to use them to find event, and pull value of field "A". Same activity is similarly recorded within two indexes but one has "A"
Within 5min timeframe of an event (search & map?)
if I find event where fields B and C are equal accross both indexes, (if)
then pull value from index=2 field "A" and map it to field "D" on index=1 in that event.
else do a lookup from lookuptable (if false | lookup table x as B OUTPUTNEW z as D)
I hope it's clear, please let me know in case of any quesions.
so far..
(index=1 sourcetype=type1 E=condition) OR (index=2 Q=condition) | fields A, B1, B2 C1, C2 D, E, Q | eval B=coalesce(B1, B2)
| rename A as D | stats values(*) as * by B | table A B C
It returns common value of A on comparision of B1 and B2.
If possible could you provide the real index names and fieldnames? It's much easier to understand and write a working query than using hypotheticals.
The optimal solutions depends on how many records is returned from index 2 where B = C -- could you give me some idea?
Index=windows event_id=XXX user IN (account1, account2, account3)
Index=mfa service=ntlm AND loger IN (account1, account2, account3)
Index=mfa contains fields like :
service
logger
hostsrc
app
IP
etc..
Index=windows contains fields like:
eventid
address
user
DC name
etc..
So far my search was looking only on the index=windows
logger and user will probably have the same value accross indexes
IP and address will probably have the same value accross indexes
I want to compare if values of those fields match, if yes.. take hostsrc field with corresponding values.
If there is no event that contains those two values, take hostsrc from lookuptable using IP field on index=windows (dont bother about mfa index)
index=mfa which contains hostsrc that I want to add in my table.
I hope this makes it more clear
I am not able to test my search so you may have to mess around with the where statement. But, if I'm understanding the question correctly all you want to do is display the events in a table together?
index=mfa OR index=windows
| stats list(_raw) AS events BY logger user address IP hostsrc | where logger==user AND IP==address | table user IP hostsrc
That's what I quickly threw together and came up with. Not sure if it does what you need, but after some tweaking I think it'd give you unique values tabled despite the index.
Does user and IP have to match on both indexes in order to consider a match? Adjust the transaction command if you're matching only one of the fields.
((index=windows event_id=<event_id>) OR (index=mfa service=ntlm)) (account1 OR account2 OR account3)
| eval src_user=LOWER(COALESCE(logger , user))
| search src_user IN (account1, account2, account3)
| eval src_ip=COALESCE(IP, address)
| transaction src_ip src_user maxspan=10m
| table _time src_user src_ip hostsrc
| inputlookup <lookup_name> IP OUTPUT hostsrc AS hostsrc_2
| eval hostsrc=COALESCE(hostsrc , hostsrc_2)