Splunk Search

Creating a REST endpoint to allow csv lookup files to be uploaded/updated?

Path Finder

I would like to create a REST endpoint that will allow me to to automate the uploading and updating of a csv lookup file daily.
Is it possible to create such an endpoint and what would be the process to achieve this?

There is a similar question to this, but it has no accepted answer.

https://answers.splunk.com/answers/369789/how-to-create-or-update-kv-store-via-rest-endpoint.html

Thank you in advance.

0 Karma

Path Finder

Hey somesoni2,

For some reason Iwhen I replied to your comment it would not appear after I posted it.

So for the the first error thats exactly what I did, Ill post for others to see

Was:
curl -k -u admin:splunk https://10.10.10.65:8089/servicesNS/admin/search/data/lookup-table-files -d eai:data="C:/Program Files/Splunk/var/run/splunk/lookup_tmp/TestLookup.csv" -d name=TestLookup.csv 
Now:
curl -k -u admin:splunk https://10.10.10.65:8089/servicesNS/nobody/search/data/lookup-table-files/ -d eai:data="C:/Program Files/Splunk/var/run/splunk/lookup_tmp/TestLookup.csv" -d name=TestLookup.csv

As for the 'Data could not be written:' error, I am running the command line as the Administrator. I also chaged the TestLookup.csv file properties>>security permissions of all the Group and user names to allow full control. Is this what you meant?

I then restarted Splunk and re-ran the following command. I still got the 'Data could not be written' error

curl -k -u admin:splunk https://10.10.10.65:8089/servicesNS/nobody/search/data/lookup-table-files/ -d eai:data="C:/Program Files/Splunk/var/run/splunk/lookup_tmp/TestLookup.csv" -d name=TestLookup.csv

Ultra Champion

An alternative approach is to bring the data in just like any other data source instead of uploading as a lookup. In other words, if you use a monitor input you can have splunk index the data each time it is updated. Assuming you set the sourcetype appropriately, it will then be trivial to write the same search using indexed data instead of a lookup. Make sense?

Path Finder

Hey SloshBurch,

Thanks for the reply. I'm pretty sure I understand what you mean by monitoring the input. Would this not present this data as events? and how would you correlate it with other event fields?

0 Karma

Ultra Champion

Yea, you got it! The rows of the csv would appear as individual events. So instead of using a lookup, you would do:

( original base search ) OR ( search of the csv file )
 | stats values(*) AS * by unifying_field

So your base search pulls all the events from the two different data sets and then you can use the fields after by in stats to do the join/correlation.

If that's unclear then toss the search here and I can get more specific.

0 Karma

Path Finder

Hey SloshBurh,

This could be an option alright. Heres a basic example of a search, how would you achieve this with your suggested approach?

managername, department and username are all fields within the csv

index="abc" sourcetype="123" Device=* AppType=* OS=*  UserID |  lookup  userIDlookup.csv user_id as UserID |search managername ="John doe"  | search departmet = "A"  | stats  count as Logins by username   | sort  -Logins  
0 Karma

Ultra Champion

Try something like this:

( index="abc" sourcetype="123" Device=* AppType=* OS=* UserID )
OR
( index="def" sourcetype="userIDlookup" source="userIDlookup.csv" managername ="John doe" departmet = "A" username=* )
| stats values(*) AS *, count as Logins by username
| sort -Logins

I may be getting the by part of the stats command wrong simply because I'm overlooking what you're trying to match on. But the general idea is that once the data is indexed, you can add it's events to the search and use the common field name (in this case username) to join the events together. The values(*) AS * provides you with the complete list of fields.

If this gets you most of the way, you can use the fields command to clean up and remove the fields you don't care about in the final output.

Thoughts?

0 Karma

Path Finder

Thanks for the fast reply SloshBurch, I will try this out and let you know how I get on. It may take me some time as I will have to figure out how to input the csv as a data input instead of a lookup.

Thanks again

0 Karma

Ultra Champion

That should be easy so if you struggle let us know. Essentially, you'll want to have a forwarder collect that using a [monitor://path/to/csv.csv ]. You can play with the sourcetype definition by bringing in a sample through the UI and defining the sourcetype there. If you run into issues, post another question on that topic and tag me in.

0 Karma

Ultra Champion

Is REST and/or lookup specifically a requirement or are you open to other ways getting a CSV data set into Splunk?

0 Karma

Path Finder

Hi SloshBurch,

Yes the Lookup is a specific requirement. I would like to automate the updating or replacing of a lookup file on a daily basis, If there are alternatives to using REST, I would certainly try them out.

We use the lookup to add more meaning to our event data by correlating a user ID with a lookup file. Users are continuously being added to our events and so we need to keep our lookup up-to-date with these new users.

0 Karma

SplunkTrust
SplunkTrust

Path Finder

Hey somesoni2,

I have been trying to run the example you have provided and I keep getting the same error. I then ran the GET example to see if it would run but I got the same output.

Any thoughts?

C:\Users\Aaron>curl -k -u admin:pass https://localhost:8089/servicesNS/admin/search/data/lookup-table-file
s
curl: (56) Received HTTP code 403 from proxy after CONNECT
0 Karma

Path Finder

I could not find anything in the Splunk answers that referenced the 403 code that refers to this issue

0 Karma

Ultra Champion

403 is forbidden/unauthorized so it might be that you have the wrong username and password in the curl command. Make sure you're not actually using admin:pass but instead putting your credentials there. If still same issue, then try the global scope by replacing the user and app name: https://localhost:8089/servicesNS/-/-/data/lookup-table-file

0 Karma

Path Finder

Hi Guys,

I was having some access issues but I was finally able to run the POST command. I am getting a response which is good, but I am unable to write to the file. I first tried it with the command that you linked me too and I got an 'object does not exist' error. Then I added the a blank csv to the apps lookups folder "C:\Program Files\Splunk\etc\apps\search\lookups" on the splunk instance and tried it again and that is when I got the 'Data cannot be written error'.

0 Karma

Path Finder
C:\Users\Aaron>curl -k -u admin:pass https://10.10.10.10:8089/servicesNS/admin/search/data/lookup-table-
files/lookup.csv -d eai:data=C:/Aaron/Splunk/RESTlookupTest/TestLookup.csv
<?xml version="1.0" encoding="UTF-8"?>
<response>
  <messages>
    <msg type="ERROR">
 In handler 'lookup-table-files': An object with name=lookup.csv does not exist</msg>
  </messages>
</response>
0 Karma

Path Finder
  C:\Users\Aaron>curl -k -u admin:pass https://10.10.10.10:8089/servicesNS/admin/search/data/lookup-table-
    files/TestLookup.csv -d eai:data=C:/Aaron/Splunk/RESTlookupTest/TestLookup.csv
    <?xml version="1.0" encoding="UTF-8"?>
    <response>
      <messages>
        <msg type="ERROR">
     In handler 'lookup-table-files': Data could not be written: /admin/search/lookups/TestLookup.csv: C:/Aaron/
    Splunk/RESTlookupTest/TestLookup.csv</msg>
      </messages>
    </response>
0 Karma

SplunkTrust
SplunkTrust

Can you actually try this endpoint? I believe this is the endpoint to create a new lookup (other one was for updating the data for existing lookup).

http://docs.splunk.com/Documentation/Splunk/6.2.6/RESTREF/RESTknowledgeExamples#data.2Flookup-table-...

So basically run this

C:\Users\Aaron>curl -k -u admin:pass https://10.10.10.10:8089/servicesNS/admin/search/data/lookup-table-
     files -d eai:data=C:/Aaron/Splunk/RESTlookupTest/TestLookup.csv -d name=TestLookup.csv
0 Karma

Path Finder

Hey somesoni2,

I was able to make some progress with this new command, thank you. When I first ran it and I got the following error

C:\Users\Aaron>curl -k -u admin:pass https://10.10.10.10:8089/servicesNS/admin/search/data/lookup-table-
files -d eai:data="C:/Aaron/Splunk/RESTlookupTest/TestLookup.csv" -d name=TestLookup.csv
<?xml version="1.0" encoding="UTF-8"?>
<response>
  <messages>
    <msg type="ERROR">
 In handler 'lookup-table-files': Source file is outside of staging area 'C:\Program Files\Splunk\var\run\splu
nk\lookup_tmp': C:\Aaron\Splunk\RESTlookupTest\TestLookup.csv</msg>
  </messages>
</response>

http: //docs.splunk.com/Documentation/Splunk/6.1.3/RESTAPI/RESTknowledge#POST_data.2Flookup-table-files

I found the page above with the create endpoint and noticed that the folder from which they upload from did not exist on my instance. I created the lookup_tmp folder on my instance and put the TestLookup.csv file into it.

Now when I run the following command it creates the TestLookup file in the "C:\Program Files\Splunk\etc\users\admin\search\lookups" folder but it does not contain any of the data.

C:\Users\Aaron>curl -k -u admin:pass https://10.10.10.10:8089/servicesNS/admin/search/data/lookup-table-
files -d eai:data="C:/Program Files/Splunk/var/run/splunk/lookup_tmp/TestLookup.csv" -d name=TestLookup.csv -x
https://http.proxy.fmr.com:8000
<?xml version="1.0" encoding="UTF-8"?>
<response>
  <messages>
    <msg type="ERROR">
 In handler 'lookup-table-files': Data could not be written: /admin/search/lookups/TestLookup.csv: C:/Program F
iles/Splunk/var/run/splunk/lookup_tmp/TestLookup.csv</msg>
  </messages>
</response>

I can also see the file in Splunk by running this search

| rest/services/data/lookup-table-files | search eai:acl.app=search

Could you advise on how to copy the csv with its data?

Also,Is there reason why the TestLookup.csv is created in "C:\Program Files\Splunk\etc\users\admin\search\lookups"
rather than "C:\Program Files\Splunk\etc\apps\search\lookups\geoattrcountries.csv" where the other csv's of the search app exist?

Thanks

0 Karma

Path Finder

I have amended the command so that I am creating the TestLookup.csv i the search app directory "C:\Program Files\Splunk\etc\apps\search\lookups\TestLookup.csv"

The only problem I am having now is the

   <msg type="ERROR">
  In handler 'lookup-table-files': Data could not be written:

The csv is created in the correct directory but it has none of the data from the original csv.

0 Karma