Splunk Search

How do I convert rows to columns by time?

jiaqya
Builder

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

0 Karma
1 Solution

jiaqya
Builder

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

View solution in original post

0 Karma

jiaqya
Builder

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

0 Karma

Vijeta
Influencer

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

0 Karma

jiaqya
Builder

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

0 Karma

Vijeta
Influencer

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

0 Karma

jiaqya
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...