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.
[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
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
Edited my answer to include the (?s)
modifier in the regex to make .
match newlines.
[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
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
@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
@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
@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
@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.