topic How to a merge value and a field name from two multi value fields? in Splunk Search

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

hugohctint — Tue, 29 Sep 2020 19:19:19 GMT

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?

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

somesoni2 — Mon, 30 Apr 2018 21:12:58 GMT

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

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

woodcock — Tue, 01 May 2018 16:21:38 GMT

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

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

woodcock — Tue, 01 May 2018 16:26:46 GMT

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).

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

hugohctint — Tue, 01 May 2018 17:23:17 GMT

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

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

woodcock — Tue, 01 May 2018 18:19:22 GMT

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

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

hugohctint — Tue, 01 May 2018 18:27:37 GMT

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?

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

woodcock — Tue, 01 May 2018 18:42:49 GMT

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.

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

skyelowryvancit — Wed, 19 Aug 2020 19:30:34 GMT

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.

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

to4kawa — Tue, 18 Aug 2020 02:36:45 GMT

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