Splunk Search

dbquery - create a field using a field value

Contributor

Hi,

I am using sql query with dbquery to get data of an item from 2 different tables.
In the first table I have the Item table that has the most data on it.
the second table:
alt text

I would like to join the tables and to get the CUSTOM_FIELD_NAME field as a field and the STRINGVALUE field as the value.
for example, I have an item with ItemID=30, and in the second table I am having 3 record with the ItemID=30:
Customer Name=ENG GEN
Area=Backup
Location=Haifa

and I need the table after the join between the 2 tables to look like that:

ItemID Item Name Customer Name Area Location
30 aaa ENG GEN Backup Haifa
40 bbb IT Backup Haifa
50 ccc IT Windows Tel Aviv

How do I join the tables with the ability to have the fields like that?

Thanks

0 Karma

Esteemed Legend

In SPL after it is pulled in, like this:

Your Base Search Here
| streamstats count AS serial
| eval KVP=mvzip(CUSTOM_FIELD_NAME,  STRINGVALUE, "=")
| mvexpand KVP
| rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
| eval {KVP_key}=KVP_value
| fields - KVP* 
| rename COMMENT AS "If you need to reconstitute original events, then add in the next line (or similar)"
| rename COMMENT AS "| stats values(_time) AS _time values(*) AS * BY serial"
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!