*etc*
= removed text for anonymity
I have a very complex search query that input the following table:
Network , Source_IP, count
Search for this:
sourcetype="etc" index=*etc* EventCode=*etc* field46="*" | rex field=field46 "(?\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\:)(?\d+)\s+(?.*)" | dedup ip network | stats values(ip) as Source_IP dc(ip) as count by network | sort count desc | table network count | head 100
I need to match Network with an inputlookup file column account. Join/append? Not sure. The inputlookup file looks like this:
Account, department, environment, primary, secondary
The final output needs to show the initial results with added columns = account, primary and secondary data from the inputlookup file. For those with no match, then it just need to show NULL in those 3 new columns.
Final Output Sample:
network, department, primary, secondary, source_IP, count
testuser , null, null, null, 10.10.10.10, 500
testuser1, team2, director1, director2 , 100.10.10.10, 10
testuser3, team4, director3, director4 , 100.10.10.10, 8
The best way is to use the lookup and fillnull.
... | table network count | lookup mynetwork network | fillnull value=null department, primary, secondary
and optionally make your lookup a CIDRMATCH one on the network field.