Splunk Search

How do I convert rows to columns by time?

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

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

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

Influencer

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

0 Karma

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

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!