how to make column from Multivalues field

[m7787579]

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

[woodcock]

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

[woodcock]

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

[DalJeanis · Accepted solution]

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 

[m7787579]

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.

[DalJeanis]

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)

[sbbadri]

try this,

your base search |table Name Value | transpose

[m7787579]

Nopz it is not working 😞

[sbbadri]

....... |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

[m7787579]

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

[gcusello]

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

[m7787579]

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

[gcusello]

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

[m7787579]

Thanks Giuseppe,

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