How get data lookup from other remote peer?

[luthfiag]

Hi all,

Can Splunk get data lookup from remote peer server ?
The problem is, because we have many Splunk servers, so if the lookup is located to each servers, then when we want to manually update it, we have to update to all servers.
What we want is to maintance the Splunk lookup table in one server and all other servers get data from that server.

I try this query but it is not working
* | head 1 | eval fieldinput="abc" | lookup local=false lookup-table-a fieldinput AS fieldinput OUTPUTNEW fieldoutput AS fieldoutput

The error is
Error in 'lookup' command: The lookup table 'lookup-table-a' does not exist.

Well lookup-table-a is located in server a. I have the query in server b which the search peer to server a is enabled.

So, Can Splunk get data lookup from remote peer server ?

[rtrobock_splunk]

Has anyone tried this against a kvstore collection?  Seems to break in that case, when you have mv fields

[craigkleen]

Little late on this, but yes, I just tried this with a kvstore and it fails under 9.1.4.  If I export a subset of my kvstore data to a CSV, it's fine.   No mv fields in my kvstore data, either.

[fredclown]

This is possible now with straight SPL using the REST API. The below solution was originally inspired by SA-rest_get_lookup, but I've made some changes to fix some issues.

Create a macro called remotelookup (Settings -> Advanced search -> Search macros).

Destination app:
Wherever you want it

Name:
remotelookup(2)

Definition:

rest splunk_server=$server$ /services/search/jobs/export search="| inputlookup $lookup$ | foreach * [eval <<FIELD>> = replace(replace(replace(replace(<<FIELD>>, \"\\n\", \"@@NewLine@@\"), \"\\r\", \"@@CarriageReturn@@\"), \"\\\"\", \"@@DoubleQuote@@\"), \"NULL\", \"@@NULL@@\")] | fillnull value=NULL | rename _* AS tmp_*" output_mode=csv
| fields value
| makemv tokenizer="([^\n]+)" value
| eval header=mvindex(value,0), value=mvindex(value,1,mvcount(value))
| makemv tokenizer="(\"[^\"]+\"|[^,]+)" header
| mvexpand value
| makemv tokenizer="(\"[^\"]+\"|[^,]+)" value
| eval tuple=mvzip(header,value,"#####")
| fields tuple
| eval primarykey=md5(tostring(tuple))
| mvexpand tuple
| rex field=tuple "^(?P<field>.*)#{5}(?P<value>.*)$"
| eval field=trim(field,"\""), value=if(value=="NULL","",trim(value,"\""))
| fields primarykey field value
| eval {field}=value
| fields - name, field, value
| stats values(*) as * by primarykey
| fields - primarykey
| rename tmp_* AS _*
| fieldformat _time=if(isint(_time),strftime(_time, "%s"),_time)
| foreach * [
    eval <<FIELD>> = replace(replace(replace(replace(<<FIELD>>, "@@NewLine@@", "
"), "@@CarriageReturn@@", ""), "@@DoubleQuote@@", "\""), "@@NULL@@", "NULL")
]

Arguments:
server,lookup

Validation Expression:
$server$!="" AND $lookup$!=""

Validation Error Message:
You must provide a server and a lookup.

You can then call it this way.

| `remotelookup("server name", "lookup.csv")`

If you want to sync a local lookup to match the lookup on another server you can do this in a report and set it to run on a schedule.

| `remotelookup("server name", "lookup.csv")`
| outputlookup lookup.csv

One thing to note is that the server where the macro exists needs to have the remote server as a search peer so that it can access that server's REST API (Settings -> Distributed search -> Search peers).

Update:
2023-06-06: Changed method for create an mv field from a string to work with Splunk 9.x

[rodrigomanrique]

Your proposal looks interesting, but it doesn't work. I have Splunk 9.0.1 and I get an error.

[fredclown]

Try this REST endpoint (/services/search/jobs/export). The "v2" is the problem.

[rodrigomanrique]

Splunk documentation states that the services/search/jobs/export method is no longer supported as of version 9.0.0.

[fredclown]

That REST endpoint only supports POST right now now and the "| rest" command only supports GET. So, you cannot use that endpoint in the Splunk UI yet. Hopefully, they will add the GET method to it at some point.

[rodrigomanrique]

So, how is your method going to work if you are also using REST.

[fredclown]

I'm not using the v2 endpoint. Yes it is deprecated, but typically that takes a while to be completely removed as they try to support backwards functionality for a while. Like I said hopefully at some point they either add the GET method for the v2 endpoint or they add an option in the "| rest" command to support POST.

[rodrigomanrique]

Let's see, let's do something. Run only the first part of your query:

rest splunk_server=$server$ /services/search/jobs/export search="| inputlookup $lookup$

Does it work for you? It should work for you, right?

[fredclown]

Simply running this will not work.

rest splunk_server=$server$ /services/search/jobs/export search="| inputlookup $lookup$

It is not correctly formatted. It is missing the pipe at the beginning, the quotes are imbalanced, and the $server$ and $lookup$ tokens are not set. A simplified version of the first line that would be returned from the macro would be the below SPL. The $server$ and $lookup$ tokens are replaced by the macro call. Also, the macro SPL does not have the preceding pipe on purpose. It is expected that the pipe will be supplied before the macro call "| `remotelookup(server,lookup)`". Also, if you do not supply the  "output_mode=csv" in the "| rest" command you will not see anything. This is required.

| rest splunk_server=local /services/search/jobs/export search="| inputlookup geo_attr_us_states.csv" output_mode=csv

[rodrigomanrique]

I did exactly what you put in your final query and I get error:

| rest splunk_server=socsiemfe /services/search/jobs/export search="| inputlookup license.csv" output_mode=csv

[socsiemfe] Unable to determine response format from HTTP Header

[fredclown]

That's not an error. It's a warning and yes, you will see that. That is unavoidable, but it does not affect the results. Also your server "socsiemfe" needs to be set as a search peer on the search head you are searching from ... if you've not done that already. Otherwise, it will not be able to call the REST api for the "socsiemfe" search head. It can be set up in (Settings -> Distributed search -> Search peers).

[rodrigomanrique]

I have SH socsiemfe as a search peer. But what is not clear to me is that your original query is expected to work, if when I run the first line it returns nothing.

When you build a query, you test it as you go along. First part 1 should work, then part 2, then part 3. If one part of the query does not return data, why then are we going to create a macro if part 1 does not return anything.

Do you understand my doubt?

[fredclown]

It works very well on the multiple search heads I've put it on. Also, this is originally not my macro. I've simply made some tweaks to it to account for some bugs in the original. Originally, this comes from a splunk.com blog post and the source has been put on github.

[rodrigomanrique]

I get this error after executing it as the blog says:

| rest splunk_server=socsiemfe /services/search/jobs/export search="| inputlookup licencia.csv" output_mode=csv | fields value

[socsiemfe] Unable to determine response format from HTTP Header

[fredclown]

It's not an error. It is a warning (it has the yellow triangle icon). You will see that ... I see that on my servers. That is unavoidable and is a Splunk issue, but it does not affect the results of the search.

[fredclown]

Have you set up the remote server as a search peer? If so is it showing as healthy in the search peers UI? Is the remote server a full Splunk install (not a UF)?

This does work with 9.x, although I did have to make a tweak yesterday but it should work now. The code above has been updated. However, the tweak I made should not have corrected the issue you are having. In fact when I tried it on 9.x without the tweak I didn't get an error. So, the fix is probably not related to your issue.

[fredclown]

What is the error?

[rodrigomanrique]

I get this error:

Unexpected status for to fetch REST endpoint uri=https://127.0.0.1:8089/services/search/v2/jobs/export?count=0&search=%7C%20inputlookup%20licencia.csv from server=https://127.0.0.1:8089 - Method Not Allowed