Splunk Search

Search a Datamodel using field values from a lookup csv file

mztopp
Explorer

I have a lookup: test.csv that has a list of 10 IP's (src_ip). I want to be able to search a datamodel that  looks for traffic from those 10 IPs in the CSV from the lookup and displays info on the IPs even if it doesn't match. Currently I have tried:

 | tstats count from datamodel=DM where
[| inputlookup test.csv | rename src_ip to DM.src_ip
| fields DM.src_ip]
by DM.src_ip 

| rename DM.src_ip AS src_ip

| iplocation src_ip

| fillnull value="NULL"

| table src_ip, Country

The issue is that if the IP from the lookup isnt found in the DataModel, it doesn't include that entire line, so instead of 10 IPs with 10 countries, I get maybe 5-6 IPs and their respective countries. I want the DM to always include all 10 IPs from the lookup in the table. I understand that I can just use the lookup to get countries, but I specifically want to have the datamodel available for other data while always including all 10 IPs in the table.

Labels (3)
0 Karma
1 Solution

PradReddy
Path Finder

If current DM doesn't bring all src_ip related information from subsearch then you can add all src_ip's using an additional inputlookup and append it to DM results

| tstats count from datamodel=DM where
[| inputlookup test.csv
| rename src_ip to DM.src_ip
| fields DM.src_ip]
by DM.src_ip
| rename DM.src_ip AS src_ip
| inputlookup test.csv append=t
| stats values(*) as * by src_ip

| iplocation src_ip
| fillnull value="NULL"
| table src_ip, Country

View solution in original post

0 Karma

PradReddy
Path Finder

If current DM doesn't bring all src_ip related information from subsearch then you can add all src_ip's using an additional inputlookup and append it to DM results

| tstats count from datamodel=DM where
[| inputlookup test.csv
| rename src_ip to DM.src_ip
| fields DM.src_ip]
by DM.src_ip
| rename DM.src_ip AS src_ip
| inputlookup test.csv append=t
| stats values(*) as * by src_ip

| iplocation src_ip
| fillnull value="NULL"
| table src_ip, Country

0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...