Hello, After lookup command was executed, the row with the blank company were excluded, so the following eval company did not take into effect. | eval company = mvmap(company, if(isnull(company), "N/A", company)) If I put eval command before the lookup, it did not work because the company field was in the CSV file which took place after the lookup command.  The issue is: how do we fill NULL in the CSV file. Thank you ... View more

Hello, Thank you for looking into this. I put the fillnull after the lookup command. See below: If you create a new CSV file called company2.csv and replace Comp-A and Comp-Y with "empty" as follows: ip_address company location 192.168.1.1   Loc-A 192.168.1.1 Comp-Z Loc-Z 192.168.1.2 Comp-B Loc-B 192.168.1.2   Loc-Y 192.168.1.5 Comp-E Loc-E Run the following query using company2.csv and "fillnull": makeresults | eval _raw="ip_address vulnerability score 192.168.1.1 SQL Injection 9 192.168.1.1 OpenSSL 7 192.168.1.2 Cross Site-Scripting 8 192.168.1.2 DNS 5" | multikv forceheader=1 | lookup company2.csv ip_address as ip_address OUTPUTNEW ip_address, company, location | fillnull value="N/A" company | eval idx = mvrange(0, mvcount(company)) | eval company = mvmap(idx, json_object("company", mvindex(company, idx), "location", mvindex(location, idx))) | stats values(score) as score by ip_address company vulnerability | fromjson company The result using company2.csv is: ip_address company vulnerability score location 192.168.1.1 Comp-Z OpenSSL 7 Loc-A 192.168.1.1 Comp-Z SQL Injection 9 Loc-A 192.168.1.2 Comp-B Cross Site-Scripting 8 Loc-B 192.168.1.2 Comp-B DNS 5 Loc-B The result using company.csv (without removing Comp-A and Comp-Y) is: ip_address company vulnerability score location 192.168.1.1 Comp-A OpenSSL 7 Loc-A 192.168.1.1 Comp-A SQL Injection 9 Loc-A 192.168.1.1 Comp-Z OpenSSL 7 Loc-Z 192.168.1.1 Comp-Z SQL Injection 9 Loc-Z 192.168.1.2 Comp-B Cross Site-Scripting 8 Loc-B 192.168.1.2 Comp-B DNS 5 Loc-B 192.168.1.2 Comp-Y Cross Site-Scripting 8 Loc-Y 192.168.1.2 Comp-Y DNS 5 Loc-Y For side note (separate issue) in real data, if I ran "sort" at the end of your JSON query, there are some missing data (compared to no sort or using non JSON query). I am not sure what causing it as it worked just fine in emulated data.   Thanks ... View more

Hello, I tested your query and it worked on both emulated and real data. The query time is faster than mvindex suggested by ITWhisperer. I would accept both solutions if I could. You are correct. This post is using outputlookup while the other post was actually using "append", which "the new issue with duplicate companies" has not been solved  https://community.splunk.com/t5/Splunk-Search/How-to-perform-lookup-from-index-search-with-dbxquery/m-p/651566#M225250 ** NULL issue *** Also, If the company is empty,  your suggested query will not show the company For example:  Comp-A and Comp-Y are removed.  The output only 4 rows as shown on Table 2. I tried to use the following command, but it did not help.   Please help.  Thank you so much   | fillnull value="N/A" company | eval company = if(company="" or isnull(company), "N/A",company)   Table1 - company.csv    ip_address company location 192.168.1.1   Loc-A 192.168.1.1 Comp-Z Loc-Z 192.168.1.2 Comp-B Loc-B 192.168.1.2   Loc-Y 192.168.1.5 Comp-E Loc-C Table2 Result after the query: ip_address vulnerability score company location 192.168.1.1 OpenSSL 7 Comp-Z Loc-A 192.168.1.1 SQL Injection 9 Comp-Z Loc-A 192.168.1.2 Cross Site-Scripting 8 Comp-B Loc-B 192.168.1.2 DNS 5 Comp-B Loc-B ... View more

Hello, If the company is empty,  your suggested query (stats value)  will not show the company For example:  Comp-A and Comp-Y are removed.  The output only 4 rows as shown on Table 2. I tried to use the following command, but it did not help.   Please help.  Thank you so much   | fillnull value="N/A" company | eval company = if(company="" or isnull(company), "N/A",company)   Table1 - company.csv    ip_address company location 192.168.1.1   Loc-A 192.168.1.1 Comp-Z Loc-Z 192.168.1.2 Comp-B Loc-B 192.168.1.2   Loc-Y 192.168.1.5 Comp-E Loc-C Table2 Result after the query:   ip_address vulnerability score company location 192.168.1.1 OpenSSL 7 Comp-Z Loc-A 192.168.1.1 SQL Injection 9 Comp-Z Loc-A 192.168.1.2 Cross Site-Scripting 8 Comp-B Loc-B 192.168.1.2 DNS 5 Comp-B Loc-B ... View more

Hello, Thank you for your help. I ran your query on both emulated data and real data and it worked fine. 1. I found duplicates rows and was able to remove duplicate rows with dedup. At first I thought values would remove duplicates.   Is there a way to remove duplicates without using dedup? 2. Can I use lookup and the same splitting methods you suggested if I am combining index and DBXquery (instead of CSV), or I have to use "Append/join" with the same method? Here's the other post - The solution provided was good only if IPs are not duplicate.  Thank you again!! https://community.splunk.com/t5/Splunk-Search/How-to-perform-lookup-from-index-search-with-dbxquery/m-p/651566#M225250 ... View more

Hello, Thank you for your help. I ran your query in the emulated data and it gave me 16 results, instead of 8 in the expected result.  This topic is Index vs CSV using lookup,  the other topic is index vs DBXquery using append.  Lookup vs append does not seem to behave the same.  Comp-A only should only have 2 vulnerabilities (1 OpenSSL - with score 7 & 1 SQL Injection - with score - 9) When I ran it in real data, it gave me merged ip_address.  Please suggest. Thank you vulnerability score company ip_address location OpenSSL 7 Comp-A 192.168.1.1 Loc-A OpenSSL 7 Comp-Z 192.168.1.1 Loc-Z OpenSSL 9 Comp-A 192.168.1.1 Loc-A OpenSSL 9 Comp-Z 192.168.1.1 Loc-Z SQL Injection 7 Comp-A 192.168.1.1 Loc-A SQL Injection 7 Comp-Z 192.168.1.1 Loc-Z SQL Injection 9 Comp-A 192.168.1.1 Loc-A SQL Injection 9 Comp-Z 192.168.1.1 Loc-Z Cross Site-Scripting 5 Comp-B 192.168.1.2 Loc-B Cross Site-Scripting 5 Comp-Y 192.168.1.2 Loc-Y Cross Site-Scripting 8 Comp-B 192.168.1.2 Loc-B Cross Site-Scripting 8 Comp-Y 192.168.1.2 Loc-Y DNS 5 Comp-B 192.168.1.2 Loc-B DNS 5 Comp-Y 192.168.1.2 Loc-Y DNS 8 Comp-B 192.168.1.2 Loc-B DNS 8 Comp-Y 192.168.1.2 Loc-Y ... View more

Hello, I responded to ITWhisperer.  Please take a look. I appreciate your help. Thank you ... View more

Hello, Sorry. You are correct. Thank you for your help. I was not aware that lookup from index to CSV will not return duplicate data on the index side. I was confused with the output from "append" I updated the company.csv showing that one IP can be used in multiple companies, so when lookup is used, it merged data in "company" and "location" field. 1) How do I expand merged data in the company and location fields into individual event without using mvexpand? 2) If I have to use mvexpand, what an efficient way to use it without hitting memory limit? Thank you so much for your help | index=vulnerability_index | table ip, vulnerability, score ip vulnerability score 192.168.1.1 SQL Injection 9 192.168.1.1 OpenSSL 7 192.168.1.2 Cross Site-Scripting 8 192.168.1.2 DNS 5 company.csv ip_address company location 192.168.1.1 Comp-A Loc-A 192.168.1.1 Comp-Z Loc-Z 192.168.1.2 Comp-B Loc-B 192.168.1.2 Comp-Y Loc-Y 192.168.1.5 Comp-E Loc-E | lookup company.csv ip_address as ip_address OUTPUTNEW ip_address, company, location ip_address vulnerability score company location 192.168.1.1 SQL Injection 9 Comp-A Comp-Z Loc-A Loc-Z 192.168.1.1 OpenSSL 7 Comp-A Comp-Z Loc-A Loc-Z 192.168.1.2 Cross Site-Scripting 8 Comp-B Comp-Y Loc-B Loc-Y 192.168.1.2 DNS 5 Comp-B Comp-Y Loc-B Loc-Y Expected result with query (without mvexpand): ip_address vulnerability score company location 192.168.1.1 SQL Injection 9 Comp-A Loc-A 192.168.1.1 SQL Injection 9 Comp-Z Loc-Z 192.168.1.1 OpenSSL 7 Comp-A Loc-A 192.168.1.1 OpenSSL 7 Comp-Z Loc-Z 192.168.1.2 Cross Site-Scripting 8 Comp-B Loc-B 192.168.1.2 Cross Site-Scripting 8 Comp-Y Loc-Y 192.168.1.2 DNS 5 Comp-B Loc-B 192.168.1.2 DNS 5 Comp-Y Loc-Y ... View more

Hello, Thanks for your help. I was able to run your JSON query just fine. It turned out that one ip_address can be used in multiple company, so the merged data now is in "vulnerability" , "company", and "location" field 1. Is it possible to use lookup from index to Dbx query?        When I used lookup from index to CSV, it only returns merged data on the CSV side, not on the index side 2. Why don't we use JOIN? Before parse: company         location        SubnetID-IP Comp-A Loc- A   [{"subnet_id":"101","ip_address":"192.168.1.1"}, {"subnet_id":"121","ip_address":"192.168.1.21"}, {"subnet_id":"131","ip_address":"192.168.1.31"}] Comp-Z Loc- Z  [{"subnet_id":"101","ip_address":"192.168.1.1"}, {"subnet_id":"121","ip_address":"192.168.1.21"}, {"subnet_id":"131","ip_address":"192.168.1.31"}] Comp-B Loc-B  [{"subnet_id":"102","ip_address":"192.168.1.2"}, {"subnet_id":"122","ip_address":"192.168.1.22"}, {"subnet_id":"123","ip_address":"192.168.1.23"}] Comp-Y Loc-Y  [{"subnet_id":"102","ip_address":"192.168.1.2"}, {"subnet_id":"122","ip_address":"192.168.1.22"}, {"subnet_id":"123","ip_address":"192.168.1.23"}] Comp-E Loc-E [{"subnet_id":"105","ip_address":"192.168.1.5"}, {"subnet_id":"152","ip_address":"192.168.1.52"}] After Parse:   ip_address company location 192.168.1.1 Comp-A Loc-A 192.168.1.1 Comp-Z Loc-Z 192.168.1.2 Comp-B Loc-B 192.168.1.2 Comp-Y Loc-Y 192.168.1.5 Comp-E Loc-E After append:  both vulnerability and company are merged ip_address vulnerability score company location 192.168.1.1 OpenSSL SQL Injection 7 Comp-A Comp-Z Loc-A Loc-Z 192.168.1.2 Cross Site-Scripting DNS 8 Comp-B Comp-Y Loc-B Loc-Y   Expected result: ip_address vulnerability score company location 192.168.1.1 OpenSSL 7 Comp-A Loc-A 192.168.1.1 SQL Injection 9 Comp-Z Loc-A 192.168.1.2 Cross Site-Scripting 8 Comp-B Loc-B 192.168.1.2 DNS 5 Comp-Y Loc-B ... View more

Hello, Thanks for your help.  Your suggestion worked fine on the emulated data, it still needs to filter out empty vulnerability (  | search vulnerability=*) In real data, your query worked with the following findings: 1) Very slow 2) Location field also has "merged" data 3) There are additional fields that need to be merged using ("<:>"), then expand - which may cause more slowness I will accept your solution in here Do you have any other alternative solutions? Can you perhaps give a simple example using external lookup - inside python code? Thank you so much. ... View more

Hello, Thank you for your help. I tried both of your queries, and they worked exactly shown in your result. However, the result combined all the vulnerabilities in one row (OpenSSL and SQL Injection), and all the scores in one row (7 and 9), and after I expanded into a separate row, the score did not match the vulnerability because it got mixed up when it was merged (OpenSSL should have score 7, not both 7 and 9) .  Please suggest. Thank you | search vulnerability=*       'remove blank space | mvexpand vulnerability | mvexpand score ip_address Company Location vulnerability score 192.168.1.1 Comp-A Loc-A OpenSSL 7 192.168.1.1 Comp-A Loc-A SQL Injection 9 192.168.1.1 Comp-A Loc-A OpenSSL 7 192.168.1.1 Comp-A Loc-A SQL Injection 9 192.168.1.2 Comp-B Loc-B Cross Site-Scripting 5   192.168.1.2 Comp-B Loc-B DNS 8 192.168.1.2 Comp-B Loc-B Cross Site-Scripting 5   192.168.1.2 Comp-B Loc-B DNS 8 ... View more

Hello, Sorry I wasn't clear, DBXquery has field "SubnetID-IP" , not ip_address Company         Location        SubnetID-IP Comp-A Loc- A   [{"subnet_id":"101","ip_address":"192.168.1.1"},{"subnet_id":"121","ip_address":"192.168.1.21"},{"subnet_id":"131","ip_address":"192.168.1.31"}] Comp-B Loc-B  [{"subnet_id":"102","ip_address":"192.168.1.2"},{"subnet_id":"122","ip_address":"192.168.1.22"},{"subnet_id":"123","ip_address":"192.168.1.23"}] Comp-E Loc-E [{"subnet_id":"105","ip_address":"192.168.1.5"},{"subnet_id":"152","ip_address":"192.168.1.52"}] SubnetID-IP needs to be parsed using | spath input=SubnetID-IP path={} | mvexpand {} | spath input={} | table Company, Location, ip_address The output after parsing is below:     company         location        ip_address Comp-A Loc- A  192.168.1.1 Comp-B Loc-B 192.168.1.2 Comp-E Loc-E 192.168.1.5 How do I combine dbquery and spath and index?  Thanks ... View more

Hello,  I ran your query and it seems like it just appended the row without matching the IP.  This question is related to the other post that you helped me "split pattern into multiple rows" https://community.splunk.com/t5/Splunk-Search/Split-pattern-into-multiple-rows/m-p/650660 My goal is to replace the table.csv, which was obtained from | dbxquery connection=visibility query="select Company, Location, SubnetID-IP from tableCompany" then parsed the IP and SubnetID using spath, and performed mvexpand to split into single event  Instead of running dbxquery and put the data into table.csv to perform a IP lookup from index, is it possible to run a single query that combine index search and dbxquery to match/lookup an IP address? Thanks ... View more

Your first suggestion worked. Thank you so much | spath input=Subnet-IP path={} | mvexpand {} | spath input={}   I ran your second suggestion and received this "Error in 'makeresults' command: This command must be the first command of a search." What is the purpose of the following commands? Note that my search is using | inputlookup test.csv. It looks like makeresults didn't work with inputlookup. Thank you | makeresults | eval Company = "Comp-A", Location = "Loc-A", Subnet-IP = "[{\"subnet_id\":\"101\",\"ip_address\":\"192.168.1.1\"},{\"subnet_id\":\"102\",\"ip_address\":\"192.168.1.2\"},{\"subnet_id\":\"103\",\"ip_address\":\"192.168.1.3\"},{\"subnet_id\":\"104\",\"ip_address\":\"fd12:3456:789a:1::1\"}]"   ... View more

Does the CSV (Table B) need to have same exact field name as the Table A when using your search? It worked after I changed the field name (Name and Role) on Table B in CSV file to match the field names on Table A. Is it possible to do it without matching the field names (Name and Role) on both Table A and Table B? Please confirm this.  Thanks ... View more

Hello, What is the reason you used "stat"?    Does the CSV (Table B) need to have same exact field name as the Table A when using your search? When I ran your command, it merged Name with multiple roles into 1 row I need both tables are combined using 2 column name and role as  a key because the table has no unique ID, so it's kinda like a lookup, but if I use lookup, it won't catch value that's not on Table A (Siemens and Switch) Thanks ... View more

How do I do it if Table B is in CSV file?  Thanks ... View more

Hey Yeahnah, I think I figured it out using lookup below: index=table1  | lookup table2.csv  ip1 as ip OUTPUTNEW ip | lookup table2.csv  ip2 as ip OUTPUTNEW ip | search ip1=* or ip2=* | table ip, ip1, ip2, location, name Although I am still not sure why it worked by doing lookup twice  🙂      and not sure why search work with OR instead of AND (filter out both blank ip1 and ip2) And I was unable to get it to work using inputlookup Thanks for your help! ... View more