topic Re: How do I convert rows to columns by time? in Splunk Search

How do I convert rows to columns by time?

jiaqya — Wed, 10 Oct 2018 06:39:41 GMT

I have universal forwarder data which I access using the below query, but the fields are coming in each row.

I want all these rows to be columns by time.

When i do chart, I lose the time and it shows only the last value by time instead of showing for each time.

How do I fix the query so that i have the last value for each time?

the data gets collected every 15 mins..

index=win_uf sourcetype="Perfmon:LogicalDisk" host="####" instance="_Total"
|eval RaisedDateTime=_time
|search counter="% Free Space" OR counter="Free Megabytes" OR counter="% Idle Time" OR counter="Avg. Disk sec/Transfer"
|table host counter _time Value
|bucket _time span=15m 
|chart last(Value) by host counter

this works, but shows a single line for all the time frames.

But, i would like to see the same line for each time entry..

Re: How do I convert rows to columns by time?

Vijeta — Wed, 10 Oct 2018 12:30:46 GMT

Try
Timechart last(Value) by host span = 15 min

Re: How do I convert rows to columns by time?

jiaqya — Wed, 10 Oct 2018 15:03:55 GMT

Thanks for your response , im missing the counter field which has the actual rows that should be columns.
such as FreeMB etc...

how to include the counter field as well..

Re: How do I convert rows to columns by time?

Vijeta — Wed, 10 Oct 2018 15:27:27 GMT

Either use

|stats last(Value) by _time host counter

Once the chart is plotted , you can use trellis layout from chart settings and display as per host or counter

Re: How do I convert rows to columns by time?

jiaqya — Wed, 10 Oct 2018 15:43:23 GMT

unfortunately , i need to show it by counter as its getting fed into a summary index , after which the visualization is configured..

the above one gives me the data, but the counter fields are still rows..

i need counter rows as columns and _time as column and host as column
the counter rows which are now columns should have the appropriate values for each column.

Re: How do I convert rows to columns by time?

jiaqya — Tue, 29 Sep 2020 21:35:50 GMT

i dont know if this is the right way to do it, but i got it working by using evals..

index=testindex sourcetype="Perfmon:LogicalDisk" instance="_Total"
|search counter="% Free Space" OR counter="Free Megabytes" OR counter="% Idle Time" OR counter="Avg. Disk sec/Transfer"
|table host Value counter _time
|eval TimeHost=_time+":"+host
|chart values(Value) by TimeHost counter
|eval ActualTime=mvindex(split(TimeHost,":"),0)
|eval fields=split(TimeHost,":")
|eval host=mvindex(fields,1)
|fields - fields TimeHost
|eval _time=ActualTime

i now have all the fields of counter with appropriate values + host + _time..