How to update a lookup table with certain fields but leave rest of the columns with its custom values

[justinchen]

Hi There,

I have a lookup table that generate one lookup table yet it has some custom column that do not have values by default, and I add custom values to them.

The columns which I want to update by SPL are

host

index

firstTime

lastTime

totalCount

The columns which I want to update their values manually are:

accepted_delta

is_expected

Below is a screenshot of this table.

Currently we are using the following daily SPL to update this table, but I am really bad at SPL, the bug is that it will just overwrite everything that in this lookup table and all my custom values (columns of accepted_delta, is_expected) all gone.

| tstats count as totalCount earliest(_time) as firstTime latest(_time) as lastTime where index!="main" by index host
| fields host index firstTime lastTime totalCount accepted_delta is_expected

| fillnull value=1000000000 accepted_delta
| fillnull value="false" is_expected
| outputlookup  nonforwarderhost.csv

[gcusello]

Hi @justinchen,

probably in the shared search there's something missing because there's only one "]".

Anyway, you have to define the values in your lookup using fields or table command before outputlookup, listing all the fields you need.

to be sure that also the missing fields are present, you could use eval:

| tstats count as totalCount earliest(_time) as firstTime latest(_time) as lastTime where index!="main" by index host
| fillnull accepted_delta 
| fillnull is_expected
| table host index firstTime lastTime totalCount accepted_delta is_expected
| outputlookup  nonforwarderhost.csv

Ciao.

Giuseppe

[justinchen]

Thanks you @gcusello  that you pointed out my error in the SPL. I have edited that. Yet this still update the filelds with my custom values with column(accepted_delta and is_expected).

I will keep this question to you and myself. I am certain is doable but need some effort to find out how.

🙂

[justinchen · Accepted solution]

I have tuned the SPL with:

| tstats count as totalCount earliest(_time) as firstTime latest(_time) as lastTime where index!="main" by index host
| join overwrite=t type=outer
[inputlookup nonforwarderhost.csv
| fields index host accepted_delta is_expected]
| fields index host firstTime lastTime totalCount accepted_delta is_expected
| fillnull value=1000000000 accepted_delta
| fillnull value="FALSE" is_expected
| outputlookup nonforwarderhost.csv

And now this is working as expected. 😀