https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346161#M102540 <P><CODE>*etc*</CODE> = removed text for anonymity </P> <P>I have a very complex search query that input the following table:<BR /> <STRONG>Network , Source_IP, count</STRONG> </P> <P>Search for this: </P> <PRE><CODE>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 </CODE></PRE> <P>I need to match <EM>Network</EM> with an inputlookup file column <EM>account</EM>. Join/append? Not sure. The inputlookup file looks like this: <BR /> <STRONG>Account, department, environment, primary, secondary</STRONG> </P> <P>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. </P> <P>Final Output Sample: <BR /> <STRONG>network, department, primary, secondary, source_IP, count</STRONG> <BR /> testuser , null, null, null, 10.10.10.10, 500 <BR /> testuser1, team2, director1, director2 , 100.10.10.10, 10 <BR /> testuser3, team4, director3, director4 , 100.10.10.10, 8 </P> Fri, 15 Dec 2017 04:56:39 GMT gingyish 2017-12-15T04:56:39Z https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346161#M102540 <P><CODE>*etc*</CODE> = removed text for anonymity </P> <P>I have a very complex search query that input the following table:<BR /> <STRONG>Network , Source_IP, count</STRONG> </P> <P>Search for this: </P> <PRE><CODE>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 </CODE></PRE> <P>I need to match <EM>Network</EM> with an inputlookup file column <EM>account</EM>. Join/append? Not sure. The inputlookup file looks like this: <BR /> <STRONG>Account, department, environment, primary, secondary</STRONG> </P> <P>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. </P> <P>Final Output Sample: <BR /> <STRONG>network, department, primary, secondary, source_IP, count</STRONG> <BR /> testuser , null, null, null, 10.10.10.10, 500 <BR /> testuser1, team2, director1, director2 , 100.10.10.10, 10 <BR /> testuser3, team4, director3, director4 , 100.10.10.10, 8 </P> Fri, 15 Dec 2017 04:56:39 GMT https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346161#M102540 gingyish 2017-12-15T04:56:39Z https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346162#M102541 <P>The best way is to use the lookup and fillnull.<BR /> ... | table network count | lookup mynetwork network | fillnull value=null department, primary, secondary</P> Fri, 15 Dec 2017 18:37:14 GMT https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346162#M102541 starcher 2017-12-15T18:37:14Z https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346163#M102542 <P>and optionally make your lookup a CIDRMATCH one on the network field. </P> Fri, 15 Dec 2017 18:37:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-combine-a-search-result-and-an-inputlookup-file/m-p/346163#M102542 starcher 2017-12-15T18:37:48Z