CSV Lookup File Permissions

[Priya70]

Hi, I uploaded a lookup csv file into Splunk. I am trying to use it in some of the queries, but it's not working for some reasons. The permissions are set to "Private" because I am not able to change permissions to either "This App" or "Global" I am able to view the csv file using the inputlookup command in the app, but when I try to use it in the query, It doesn't work.  Do you guys think this is a permissions issue or something else? Do I need to ask an admin to change the permissions for me in order to make it work? I would really appreciate your help.

[Priya70]

The avsv file was created in the same app but sharing is Private. Cannot change the permissions

[yuanliu]

1. What is the name of uploaded file shown in "Lookup table files"?  What is the command you try to use it?  What is the error message?
2. Are you running lookup command in the same app as the upload shows?

[Priya70]

Lookup file Is called subnets.csv. I'm trying to use a join command to CIDRMATCH an hosts Ip to the subnets in the csv file. A sample IP works in a separate search on its own starting with inputlookup, but doesn't work within my query.

[yuanliu]

As I always say, the phrase "doesn't work" should be banished from this forum because it doesn't convey much information to volunteers who lack the context you have.

But even before that, it is important to lay out basic information such as using the lookup for CIDRMATCH.  Does this mean you have defined a lookup using said lookup? And you have set up CIDRMATCH with the field in question?  What other fields are in the lookup?  What is that definition called?  When I ask "What is the command you try to use it," I mean to illustrate the actual command.

Then, I asked about error messages.  This relates back to "doesn't work."  Could you explain what happens when the lookup command runs without using that phrase?

[Priya70]

Sorry, let me provide you with detailed information. I've changed the permissions to the CSV lookup file to global along with lookup definition. I also have CIDR(subnet) for match type. There is only one field in the csv file and that is just subnet. 

When I try the following query, the match is found. 
| inputlookup subnets.csv
| eval test_ip="30.37.192.80"
| eval match=if(cidrmatch(subnet, test_ip), "Matched", "NoMatch")

But when I try the following query with index and sourcetype, I get no results for the same IP:
index=web sourcetype="access_combined"
| eval test_ip="30.37.192.80"
| join type=left test_ip
[
| inputlookup subnets.csv
| eval match=if(cidrmatch(subnet, test_ip), "Matched", "NoMatch")]
| where match="Matched"
| table test_ip, match, subnet

I also tried the following and still, no result:
index=web sourcetype="access_combined"
| eval test_ip="30.37.192.80"
| lookup subnets.csv subnet OUTPUT subnet
| eval match=if(cidrmatch(subnet, test_ip), "Matched", "NoMatch")
| where match="Matched"
| table test_ip, match, subnet

Please help me with. I don't know what I am doing wrong, when the first query is matching the IP to the subnets, but the other two. I would really appreciate your help.

[yuanliu · Accepted solution]

Thank you for explaining the use case and what "doesn't work" means.  It all makes sense now: That you get no result from the illustrated searches has nothing to do with permission.  You just need a search that will.  Protip 1: Describe the logic between your source input and desired output without SPL.  So, the gist of the use case is to find the subnet in the lookup table if an IP address in the log matches it, then list the IP address and the matched subnet.  Is this correct? (Note: As you are filtering for only matched entries, there is no need to display that "Matched" a million times.)

Before showing a search that works, let me first comment that inputlookup is the wrong command for such needs.  In most use cases, lookup tables are for lookup command. (It pays to read up its documentation.)  Second, since your search involves access_combined (presumably of the Apache/NCSD lineage), you probably already extracted a field clientip, not test_ip. (If not, just use Splunk's built-in transformation access-extractions to get it.)

The following should do what you want:

index=web sourcetype="access_combined"
``` if clientip is not already extracted, add
| extract access_extraction
```
| lookup subnets.csv subnet AS clientip OUTPUT subnet
| where isnotnull(subnet)
| table clientip, subnet

(Just to confirm: the subnet field in subnets.csv contains standard CIDR notation, correct?)

Bonus tip: if you want to use some test address to experiment and test search commands, there is makeresults command.  For example,

| makeresults
| eval clientip="30.37.192.80"

Hope this helps.

[Priya70]

@yuanliu Thank so you much. It worked. I can't believe the the problem was that simple. 
I was doing the following:
| lookup subnets.csv subnet OUTPUT subnet

Just needed to add what you suggested:
| lookup subnets subnet.csv subnet as IPAddress OUTPUT subnet

Thanks again!!