Splunk Search

How to make rows value into columns.

I am trying to convert values from rows into columns. below is a example data

ServerName Counter Value
server1 %Processor time 85
server1 %
Disk Time 90
server1 Pages/sec 44
server2 %Processor time 75
server2 %
Disk Time 80
server2 Pages/sec Null

to Something like

ServerName %Processor time %Disk Time Pages/sec
server1 85 90 44
server2 75 80 Null

Could anyone, Please help me here.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

[UPDATED ANSWER]

updated field names.

<yourBaseSearch>
| chart last(Value) by host counter

alt text


@pratapbhanu2047, Just pipe the following chart command to your existing search which returns the tabular result as per your question:

 <yourCurrentSearch>
| chart last(Value) by host counter

Following is a run anywhere search based on sample data provided.

| makeresults
| fields - _time
| eval data="server1,%_Processor time,85;server1,%_Disk Time,90;server1,Pages/sec,44;server2,%_Processor time,75;server2,%_Disk Time,80;server2,Pages/sec,Null" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=","
| eval host=mvindex(data,0),counter=mvindex(data,1),Value=mvindex(data,2)
| fields - data
| table host counter value
| chart last(Value) by host counter
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Ultra Champion

You should be able to construct a REPORT action in props and transforms, to extract both counter name and value from an event and use the counter name as field name and value as field value. Should look something like below example):

props.conf

[yoursourcetype here]
REPORT-extract-counter-name-and-value = extract-counter-name-and-value

transforms.conf

[extract-counter-name-and-value]
REGEX = (?s)counter="([^\"]+)".*Value=([\d\.]+)
FORMAT = $1::$2
0 Karma

Ultra Champion

Edited my answer to include the (?s) modifier in the regex to make . match newlines.

0 Karma

SplunkTrust
SplunkTrust

[UPDATED ANSWER]

updated field names.

<yourBaseSearch>
| chart last(Value) by host counter

alt text


@pratapbhanu2047, Just pipe the following chart command to your existing search which returns the tabular result as per your question:

 <yourCurrentSearch>
| chart last(Value) by host counter

Following is a run anywhere search based on sample data provided.

| makeresults
| fields - _time
| eval data="server1,%_Processor time,85;server1,%_Disk Time,90;server1,Pages/sec,44;server2,%_Processor time,75;server2,%_Disk Time,80;server2,Pages/sec,Null" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim=","
| eval host=mvindex(data,0),counter=mvindex(data,1),Value=mvindex(data,2)
| fields - data
| table host counter value
| chart last(Value) by host counter
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Builder

Another question on this, the uf data collected runs every 15 mins, so if i have to see the same output based on each run time, how do i do that.. it does not work for me when i run
|chart last(Value) by host counter _time

0 Karma

@Niketnilay, thanks for answer.

however, what i was trying to do is building/finding a way to have "% Idle Time" as field name and field Value as value to "% Idle Time"

that is "% Idle Time" = "99.880239676329609"

Similarly i want apply this for all counter(% Idle Time, %Processor time, %Disk Time etc.) and have corresponding Values (field Value in below example event ) assigned from events.

example of an event.

05/28/2018 17:33:53.560 +1000
collection="Host LogicalDisk Stats"
object=LogicalDisk
counter="% Idle Time"
instance=D:
Value=99.880239676329609

0 Karma

SplunkTrust
SplunkTrust

@pratapbhanu2047 have you tried my answer?

<yourCurrentSearch>
 | chart last(Value) by ServerName Counter

My example is a run anywhere search based on your first table using the following query:

 | makeresults
 | fields - _time
 | eval data="server1,%_Processor time,85;server1,%_Disk Time,90;server1,Pages/sec,44;server2,%_Processor time,75;server2,%_Disk Time,80;server2,Pages/sec,Null" 
 | makemv data delim=";" 
 | mvexpand data 
 | makemv data delim=","
 | eval ServerName=mvindex(data,0),Counter=mvindex(data,1),Value=mvindex(data,2)
 | fields - data
 | table ServerName Counter Value

Then final pipe inverses the results using chart command as per your question:

 | chart last(Value) by ServerName Counter
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

@Niketnilay, yes i tried. However it did help a bit..but I am not able to resolve it.

I did found out that i can use eval with chart or stats

index=acn*tier* sourcetype="Perfmon:sqlserver:buffer_manager"
| eval Value=round(Value,2)
| eval My{counter}=Value
| stats avg("My*") by host
| fillnull

0 Karma

SplunkTrust
SplunkTrust

@pratapbhanu2047 can you post the code in comment using the code button 101010 or shortcut key CTRL+K so that special characters do not escape?

The chart command can control one field on x-axis and another one on y-axis. The first parameter after by clause is actually treated as over (or x-axis). Second on y-axis. I have replaced ServerName with host as per your query

index=<yourIndexName> sourcetype="Perfmon:sqlserver:buffer_manager"
| chart last(Value) by host counter

If you want to invert you would use

index=<yourIndexName> sourcetype="Perfmon:sqlserver:buffer_manager"
| chart last(Value) by counter host

Please try both and see if one of them matches result you are seeking!
PS: Just noticed counter should be lower case.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma