Splunk Search

How to make rows value into columns.

pratapbhanu2047
Engager

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

niketn
Legend

[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

FrankVl
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

FrankVl
Ultra Champion

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

0 Karma

niketn
Legend

[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!!!"

jiaqya
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

pratapbhanu2047
Engager

@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

0 Karma

niketn
Legend

@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

pratapbhanu2047
Engager

@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

0 Karma

niketn
Legend

@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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...