How do I convert the following data into a pivot table?

[tamakg]

With the following search

index=msperf sourcetype="perfmon_processor_xml" 
| xpath outfield=Architecture "//COMMAND/RESULTS/CIM/INSTANCE/PROPERTY" 
| mvexpand Architecture 
| rex field=Architecture "^[^=\n]*=\"(?P[^\"]+)[^=\n]*=\"(?P[^\"]+)[^<\n]*<\w+>(?P[^<]+)"
| table PropertyName  PropertyValue
| where PropertyName in ( "Description", "DeviceID", "Name", "NumberOfCores", "NumberOfLogicalProcessors") 
| dedup PropertyValue 
| sort PropertyName PropertyValue

I've got the following result:

PropertyName     PropertyValue
Description          Intel64 Family 6 Model 45 Stepping 7
DeviceID             CPU0
DeviceID             CPU1
Name                 Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz
NumberOfCores    6

and I would like to convert to the following format:

DeviceID    Description             Name                        NumberOfCores
CPU0        Intel64 Family 6 Model 45 Stepping 7    Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz    6
CPU1        Intel64 Family 6 Model 45 Stepping 7    Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz    6

Help please?

[kamlesh_vaghela · Accepted solution]

@tamakg

Have you tried transpose command.

https://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Transpose

Can you please try following search?

index=msperf sourcetype="perfmon_processor_xml" 
 | xpath outfield=Architecture "//COMMAND/RESULTS/CIM/INSTANCE/PROPERTY" 
 | mvexpand Architecture 
 | rex field=Architecture "^[^=\n]*=\"(?P[^\"]+)[^=\n]*=\"(?P[^\"]+)[^<\n]*<\w+>(?P[^<]+)"
 | table PropertyName  PropertyValue
 | where PropertyName in ( "Description", "DeviceID", "Name", "NumberOfCores", "NumberOfLogicalProcessors") 
 | dedup PropertyValue 
 | sort PropertyName PropertyValue
| transpose header_field=PropertyName column_name=PropertyValue 
| mvexpand DeviceID | fields - PropertyValue

My Sample Search:

| makeresults 
| eval PropertyName = "Description,DeviceID,DeviceID,Name,NumberOfCores,NumberOfLogicalProcessors", PropertyName=split(PropertyName,","),
    PropertyValue = "Intel64 Family 6 Model 45 Stepping 7,CPU0,CPU1,Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz,6,12",PropertyValue=split(PropertyValue,","), temp=mvzip(PropertyName,PropertyValue) 
| stats count by _time,temp 
| eval PropertyName=mvindex(split(temp,","),0),PropertyValue=mvindex(split(temp,","),1) 
| stats values(PropertyValue) as PropertyValue by PropertyName 
| transpose header_field=PropertyName column_name=PropertyValue 
| mvexpand DeviceID | fields - PropertyValue

Thanks