topic Re: How to make rows value into columns. in Splunk Search

How to make rows value into columns.

pratapbhanu2047 — Tue, 29 Sep 2020 19:42:18 GMT

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.

Re: How to make rows value into columns.

niketn — Sun, 27 May 2018 11:56:00 GMT

[UPDATED ANSWER]

updated field names.

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

@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

Re: How to make rows value into columns.

pratapbhanu2047 — Tue, 29 Sep 2020 20:00:17 GMT

@niketn, 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

Re: How to make rows value into columns.

FrankVl — Mon, 28 May 2018 09:35:13 GMT

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

Re: How to make rows value into columns.

FrankVl — Mon, 28 May 2018 09:45:58 GMT

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

Re: How to make rows value into columns.

niketn — Mon, 28 May 2018 12:22:08 GMT

@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

Re: How to make rows value into columns.

pratapbhanu2047 — Tue, 29 Sep 2020 19:42:10 GMT

@niketn, 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

Re: How to make rows value into columns.

niketn — Mon, 28 May 2018 16:12:46 GMT

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

Re: How to make rows value into columns.

jiaqya — Wed, 10 Oct 2018 06:26:45 GMT

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