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:
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
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"