topic Re: how to make column from Multivalues field in Splunk Search

how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 10:43:37 GMT

Hi Splunker,

Please find below the data of 2 events below where i have to change the result in tabular form.so that i can see the muti valued field value as a field name in splunk when there are multple events

Query:-
|rex field=_raw "\<(?<Name>[^\>]+)\>(?<Value>[^\<]+)" max_match=50|table Name Value

My result in Splunk look like this these and are multivalued result in statistics bar.

**Name**                        **Value**
Customer                         Karen
SalesChannel                     Online
UniqueID                         a2wert
ProductCode                      zebra
ProductName                      Maestri
ProductCode                      zebra2
ProductName                      Maestri2
------------------------------------------------------
Customer                            Topez
SalesChannel                     Online
UniqueID                         a23ret
ProductCode                      Umbrella
ProductName                      Yousty
ProductCode                      Umbrella2
ProductName                      Yousty2
ProductCode                      Umbrella3
ProductName                      Yousty3

I want my result to look like this 

Customer      SalesChannel                UniqueID           ProductCode          ProductName                                     
Karen              Online                 a2wert             zebra                  Maestri
                                                             zebra2                 Maestri2                         
Topez              Online                 a23ret             Umbrella               Yousty 
                                                             Umbrella2              Yousty2                       
                                                             Umbrella 3             Yousty 3  
then the 3rd,4th enrty and so on...

Can you please help me on it.
Thanks in advance

Re: how to make column from Multivalues field

gcusello — Mon, 10 Jul 2017 10:52:21 GMT

Hi m7787579,
add at the end of your search

your_search
|rex field=_raw "\<(?<Name>[^\>]+)\>(?<Value>[^\<]+)" max_match=50
| stats values(SalesChannel) AS SalesChannel values(UniqueID) AS UniqueID values(ProductCode) AS ProductCode values(ProductName) AS ProductName by Customer

Beware: the values option put in alphabetical order the found values, so if you have more than one value in two columns, the second of the first column could not be aligned with the second of the second column.

Bye.
Giuseppe

Re: how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 11:02:49 GMT

Hi Giuseppe,

Thanks for your answer,

But here only Name and Values are field.

Rest of the field like Customer,SalesChannel,UniqueID,ProductCode,ProductName are just the values for Name and i want these values to become as a column name like Name and Values are column name.

Please let me know if you have any doubt

Re: how to make column from Multivalues field

gcusello — Mon, 10 Jul 2017 11:56:28 GMT

Hi
I didn't use it in this way but probably multikv command (http://docs.splunk.com/Documentation/Splunk/6.6.2/SearchReference/Multikv) could solve your problem.
Bye.
Giuseppe

Re: how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 13:09:11 GMT

Thanks Giuseppe,

But it is still not helping me to get my result.

Re: how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 13:18:12 GMT

Can anyone please help me on it,i am still facing the issue and i need it to submit it as soon as possible

Re: how to make column from Multivalues field

sbbadri — Mon, 10 Jul 2017 13:46:44 GMT

try this,

your base search |table Name Value | transpose

Re: how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 13:56:56 GMT

Nopz it is not working 😞

Re: how to make column from Multivalues field

DalJeanis — Mon, 10 Jul 2017 15:16:16 GMT

There has to be a less clumsy way to do this, but this should work -

your search
| rex field=_raw "\<(?<Name>[^\>]+)\>(?<Value>[^\<]+)" max_match=50
| streamstats count as recno
| eval NameValue=mvzip(Name,Value,"=")
| table recno NameValue
| mvexpand NameValue
| rex field=NameValue "(?<Name>[^=]+)=(?<Value>.*)"
| eval {Name}=Value
| fields - Name Value NameValue
| stats list(*) as * by recno
| fields - recno

Re: how to make column from Multivalues field

sbbadri — Tue, 29 Sep 2020 14:49:21 GMT

....... |rex field=_raw "(?&lteventData&gt\S+\s+\S+)" max_match=50 | eval test = mvjoin(eventData,"#") | rex field=test max_match=50 "Customer\s+(?P&ltCustomerValue&gt\S+)#" | rex field=test max_match=50 "SalesChannel\s+(?P&ltSalesChannelValue&gt\S+)#" | rex field=test max_match=50 "UniqueID\s+(?P&ltUniqueIDValue&gt\S+)#" | rex field=test max_match=50 "ProductCode\s+(?P&ltProductCodeValue&gt\S+)#" | rex field=test max_match=50 "ProductName\s+(?P&ltProductNameValue&gt\S+)#" | stats Values(SalesChannelValue) as SalesChannel, values(UniqueIDValue) as UniqueID, values(ProductCodeValue) as ProductCode, Values(ProductNameValue) as ProductName by CustomerValue

Note: Change the initial regex. Instead of Name and Value give as eventData

Re: how to make column from Multivalues field

woodcock — Mon, 10 Jul 2017 16:16:20 GMT

Like this:

| makeresults
| eval raw="Customer=Karen,SalesChannel=Online,UniqueID=a2wert,ProductCode=zebra,ProductName=Maestri,ProductCode=zebra2,ProductName=Maestri2::Customer=Topez,SalesChannel=Online,UniqueID=a23ret,ProductCode=Umbrella,ProductName=Yousty,ProductCode=Umbrella2,ProductName=Yousty2,ProductCode=Umbrella3,ProductName=Yousty3" 
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex max_match=100 "(?<name>[^,=]+)=(?<value>[^,]+)"

| rename COMMENT AS "Everything above is faking your data; everyting below is your solution"

| streamstats count AS _serial
| eval KVP=mvzip(name, value, "=")
| table KVP _serial
| mvexpand KVP
| rex field=KVP "^(?<KVP_key>[^=]+)=(?<KVP_value>[^=]+)$"
| eval {KVP_key}=KVP_value
| fields - KVP*
| stats values(*) AS * BY _serial

Re: how to make column from Multivalues field

m7787579 — Mon, 10 Jul 2017 17:32:24 GMT

Hi DalJeanis,

Thanks for your answer it was exactly my requirement.

One more question i have to ask how can i calculate these that combination count.
I mean if you o back to my qustion asked above i have pasted one XML there in the last after Product Name i would like to see these combination count.

I mean how many time these fields in same combination occured in my data.
I want to identify the count of different combinatition.

Re: how to make column from Multivalues field

DalJeanis — Tue, 11 Jul 2017 03:55:47 GMT

Depends on what you mean. If you want to see how many of each Name Value combination occur in the entire search, then right after this...

| table recno NameValue
| mvexpand NameValue

...you can do this instead...

| stats count as Count by NameValue
| rex field=NameValue "(?<Name>[^=]+)=(?<Value>.*)"
| table Name Value Count

On the other hand, if you just want to know how many values the multivalue field has, then use mvcount right after stats...

| eval mycount = mvcount(ProductName)

Re: how to make column from Multivalues field

woodcock — Sat, 09 Nov 2019 23:08:46 GMT

Also, you can use multikv for this and it should be much simpler/smaller and scale better:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Multikv