add new fieldname to empty and also filled csv lookup

[mfleitma]

Hi,

I have a variety of CSV lookup tables and have to add a field to each of these tables. The CSV files are used by scheduled searches, so I need their contents AND the field names.

table1.csv:

index,sourcetype

index1,st1

table2.csv:

sourcetype,source

st1,source1

table3.csv:

field1,field2

- no rows

For this, I use the following spl:

| inputlookup table1.csv | table index,sourcetype,comment1 | outputlookup table1.csv

| inputlookup table2.csv | table sourcetype,source,comment2 | outputlookup table2.csv

| inputlookup table3.csv | table field1,field2,comment3 | outputlookup table3.csv

For table1 and table2, this works. But for table3, I have the problem that outputlookup creates an empty table and the field names are missing. Is there a search that can extend empty and filled lookups?

Thank you.

[PrewinThomas]

@mfleitma 

You can try to use appendpipe to maintain the header

| inputlookup table3.csv
| appendpipe [ stats count | eval field1="", field2="", comment3="" ]
| fields field1, field2, comment3
| outputlookup table3.csv

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[mfleitma]

Thank you for your suggestion.

Tried this, but I get a new row in table3.csv

field1,field2,comment3
"","",""

This is not acceptable for the later process. Empty rows are ok.

Changed your spl to

| inputlookup table3.csv
| appendpipe [ stats count | eval field1=NULL, field2=NULL, comment3=NULL ]
| fields field1, field2, comment3
| outputlookup table3.csv

But this is creating an empty lookup table3.csv again.

[yuanliu]

---

Tried this, but I get a new row in table3.csv

That is exactly what appendpipe is supposed to do.  If this is not acceptable, do not use appendpipe.  Have you tried fillnull @gcusello and I have suggested?

[PrewinThomas · Accepted solution]

@mfleitma 
I thought you want to keep empty row as well. Can you try this then for only to have headers

| inputlookup table3.csv
| appendpipe [ stats count | eval field1="", field2="", comment3="" | where 1=0 ]
| fields field1, field2, comment3
| outputlookup table3.csv

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[mfleitma]

@PrewinThomas 
This is helpfull. It is not the solution I prefer at the beginning, but the result is acceptable for the later process of my scheduled searches.
For the documentation:
the appendpipe is not working in case there is ONLY the line with the fieldnames in the csv:

A_Row1: field1,field2

In case there is a LF in it like:
B_Row1: field1,field2

B_Row2:

it is running well. I get in the updated csv now:
C_Row1: field1,field2,comment3

C_Row2: ,,

I would feel better, if B_Row2 would not be necessary, but it I can handle this.

Many thx for your help.

[gcusello]

Hi @mfleitma ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @mfleitma ,

is it acceptable to replace the empty fild with another char e.g. "-"=

in this case,  you can use the fillnull command.

Ciao.

Giuseppe

[yuanliu]

Make sure to apply fillnull before table

| inputlookup table3.csv
| fillnull field1,field2,comment3 value=-
| table field1,field2,comment3
| outputlookup table3.csv

[mfleitma]

Hello yuanliu,

fillnull is only working, if there is an empty row existing in addition to the fieldname. In case table2.csv has only the filednames in it, fillnull is not working.

I tried many things with fillnull, eval as well to add a row, append the new fieldname, but at the end I didn't manage to delete my added row to get only the original status of the lookup ONLY added by a next fieldname.