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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...