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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...