topic Re: Extract with PairDelim KeyDelim in Splunk Search

Extract with PairDelim KeyDelim

aliquori — Thu, 14 May 2020 22:53:55 GMT

I have the following data in csv format:

date,year,quarter,statementType,dataCode,value
2020-03-31,2020,1,balanceSheet,ppeq,1047418000.0
2020-03-31,2020,1,balanceSheet,acctRec,0.0
2020-03-31,2020,1,incomeStatement,ebt,-20269000.0
2020-03-31,2020,1,incomeStatement,consolidatedIncome,-14061000.0
2020-03-31,2020,1,overview,bvps,12.4058406156063

I am trying to parse these so that dataCode values are the field names and Values remain the values.

Using INDEXED_EXTRACTIONS =csv in my props.conf results in ppeq, acctRec being dataCode values and the same for their actual values.

I have tried using | extract pairdelim="," kvdelim="," which associates correctly but also adds the date ex.
acctRec : 0.0 2020-03-31

I also looked at adding a transforms to parse out the fields using this \d+,\d+,\w+,\w+,(\w+)\,(\S+) but it does not appear that fields can be dynamically assigned and would all have to be specified. Any advice is greatly appreciated.

Re: Extract with PairDelim KeyDelim

ololdach — Fri, 15 May 2020 09:22:12 GMT

Hi,
I assume you want the result to look somewhat like this:
date, year, quarter, statementType,ppeq,acctRec,ebt,consolidatedIncome,bvps
2020-03-31,2020,1,balanceSheet,1047418000.0, 0.0, 0.0, 0.0, 0.0
2020-03-31,2020,1,IncomeStatement, 0.0, 0.0, -20269000.0, -14061000.0, 0.0
...

The trick is to use an eval to create the "new" fields:
| eval ppeq=if(dataCode="ppeq",value,0.0), acctRec=if(dataCode="acctRec",value,0.0), ebt=if(dataCode="ebt",value,0.0) ...

Hope it helps
Oliver

Re: Extract with PairDelim KeyDelim

vnravikumar — Fri, 15 May 2020 09:56:57 GMT

Check this

...yourquery..| table date,year,quarter,statementType,dataCode,value 
| eval {dataCode}=value 
| fillnull value=0.0 
| fields - dataCode,value

Re: Extract with PairDelim KeyDelim

aliquori — Fri, 15 May 2020 12:50:13 GMT

Thanks Oliver,

That definitely works on a small scale, is there any way to have splunk dynamically create the fields at index or search time? I have over 100 fields with new ones potentially coming in every day.

Re: Extract with PairDelim KeyDelim

aliquori — Fri, 15 May 2020 13:02:42 GMT

Thanks and that works but adds a lot of zero values making it difficult to chart. If I treat each line like a separate event I should be able to pull out each dataCode and Value and only show events where the data is present if that makes sense.

Re: Extract with PairDelim KeyDelim

aliquori — Wed, 30 Sep 2020 05:27:33 GMT

I found an alternative solution by extracting at index time:

Transforms.conf
[csv_pair]
REGEX = -\d+\,\d+\,\d+\,\w+\,(\w+)\,(\S+)
FORMAT = $1::$2
REPEAT_MATCH = true
WRITE_META = true

props.conf
[csv]
TRANSFORMS-csv = csv_pair