How to a merge value and a field name from two multi value fields?

[hugohctint]

Hello, I have a Field with Oracle SQL_BIND and a second field with the SQL_TEXT, the SQL_BIND contains the values while the SQL_TEXT contains de fields name

They look like:

SQL_BIND=" #1(7):3077144 #2(1):0 #3(3):100 #4(5):10113 #5(4):2529 #6(1):0 #7(5):40001 #8(5):57195 #9(5):80263 #10(5):10113 #11(1):0 #12(4):4752 #13(1):3 #14(1):5 #15(1):1 #16(15):3/31/2018... etc
SQL_TEXT="INSERT INTO psm.camio_in (numero_sistema, numero_romaneo, cod_operacion, cliente, procedencia, subprocedencia, corredor, cargador, entregador, remitente, cod_acopio_client, cod_transportista,... etc..

I need to get KV the pairs:

numero_sistema=3077144
numero_romaneo=0
cod_operacion=100
cliente=10113

.... etc

I had tried to find the right way to do this but I was not able:
Any ideas?

[woodcock · Accepted solution]

Like this:

| makeresults
| eval SQL_BIND="#1(7):3077144 #2(1):0 #3(3):100 #4(5):10113 #5(4):2529 #6(1):0 #7(5):40001 #8(5):57195 #9(5):80263 #10(5):10113 #11(1):0 #12(4):4752", SQL_TEXT="INSERT INTO psm.camio_in (numero_sistema, numero_romaneo, cod_operacion, cliente, procedencia, subprocedencia, corredor, cargador, entregador, remitente, cod_acopio_client, cod_transportista)"

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex max_match=0 field=SQL_BIND "#\d+\(\d+\):(?<MV_SQL_BIND>\S+)"
| eval MV_SQL_TEXT=SQL_TEXT
| rex field=MV_SQL_TEXT mode=sed "s/^[^\(]+\(// s/\).*$//"
| eval MV_SQL_TEXT=split(MV_SQL_TEXT, ", ")
| eval _raw=mvzip(MV_SQL_TEXT, MV_SQL_BIND, "=")
| fields - MV_SQL_TEXT MV_SQL_BIND
| kv

[woodcock]

This answer preserves the original number of events and all context (except for _raw, which should not be important, but you can add an eval orig_raw = _raw if you need to).

[hugohctint]

This approach is very good, thank you. What is the best way to incorporate this new individual fields to the event/index?

[woodcock]

I do not understand the question; please rephrase and expand it.

[hugohctint]

What I am trying to say is how to incorporate all these new variables into the index or sourcetype as permanent and also add the transformation to the data coming in.
Is that more clear?

[woodcock]

Ah, I see. Unfortunately, I do not see how this can be done. The principle problem is that there is no automatic way to do mvzip, which is a critical step in the solution set.

[skyelowryvancit]

this had my hopes up while I used it to fit my use case. Came back and saw the rest of the thread.  Slowly dying inside.

[to4kawa]

Why don't you create transforms.conf? @skyelowryvancit 

[somesoni2]

Give this a try

| gentimes start=-1 | eval _raw="SQL_BIND=\" #1(7):3077144 #2(1):0 #3(3):100 #4(5):10113 #5(4):2529 #6(1):0 #7(5):40001 #8(5):57195 #9(5):80263 #10(5):10113 #11(1):0 #12(4):4752... etc\"
 SQL_TEXT=\"INSERT INTO psm.camio_in (numero_sistema, numero_romaneo, cod_operacion, cliente, procedencia, subprocedencia, corredor, cargador, entregador, remitente, cod_acopio_client, cod_transportista,... etc..)\"" | table _raw 
| rename COMMENT as "Above lines are to generate sample data with 12 fields"
| rex "SQL_BIND\=\"(?<SQL_BIND>[^\"]+)" | rex "SQL_TEXT\=\"[^\(]+\((?<SQL_TEXT>[^\)]+)" 
| rename COMMENT as "Above line extract the relevant portion for SQL_BIND and SQL_TEXT from raw data"
| makemv SQL_TEXT delim=", " | rex max_match=0 field=SQL_BIND "#\d+\((?<SQL_BIND>[^\)]+)" 
| rename COMMENT as "Above line converts both to multivalued field"
| eval temp=mvzip(SQL_TEXT,SQL_BIND,"#") | mvexpand temp | rex field=temp "(?<SQL_TEXT>[^#]+)#(?<SQL_BIND>.+)" | table SQL_TEXT SQL_BIND