Splunk Search

dbquery - create a field using a field value

matansocher
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

woodcock
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...