Splunk Search

dbquery - create a field using a field value



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

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?


0 Karma

Esteemed Legend

In SPL after it is pulled in, like this:

Your Base Search Here
| streamstats count AS serial
| 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
Get Updates on the Splunk Community!

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...