Splunk Search

How to add field "A" from another index if B and C are equal across both indexes?

PawelKozy
Loves-to-Learn Lots

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.

 

Labels (1)
0 Karma

PawelKozy
Loves-to-Learn Lots

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. 

0 Karma

johnhuang
Motivator

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?

0 Karma

PawelKozy
Loves-to-Learn Lots

 

 

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

0 Karma

es5
Loves-to-Learn Lots

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.

0 Karma

johnhuang
Motivator

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)

 

0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...