Splunk Search

[Need Help] how to reverse the time scale and corresponding count in x axis from timechart.

cheriemilk
Path Finder

Hi team,

I have below query 

index=*bizx_application AND sourcetype=perf_log_bizx AND AutoSaveForm OR SaveFormV2 OR SaveForm

| timechart count by SFDC useother=false limit=0

 

the timechart returned as below.

 
 

Screen Shot 2020-10-21 at 6.18.36 PM.png

Now I want to adjust the _time scale in x axis to display from latest to earliest which means put the latest _time and corresponding count in the left.

 

How should I modify my query to achieve this adjustment?

Labels (1)
0 Karma
1 Solution

ITWhisperer
Ultra Champion

@cheriemilk 

It seems to work with rename as well

<Base Query>
| bin span=2h _time
| stats count as number by _time SFDC
| rename _time as Time
| chart values(number)  by Time SFDC limit=0 useother=f
| reverse

Although you might still need to format the field if that's important to you

View solution in original post

0 Karma

cheriemilk
Path Finder

@ITWhisperer 

I found this way works:

<Base Query>

| bin span=2h _time

| stats count as number by _time SFDC

| eval Time=strftime(_time,"%Y/%m/%d %H:%M")

| chart values(number)  by Time SFDC limit=0 useother=f

| reverse

0 Karma

ITWhisperer
Ultra Champion

@cheriemilk 

It seems to work with rename as well

<Base Query>
| bin span=2h _time
| stats count as number by _time SFDC
| rename _time as Time
| chart values(number)  by Time SFDC limit=0 useother=f
| reverse

Although you might still need to format the field if that's important to you

View solution in original post

0 Karma

cheriemilk
Path Finder

@ITWhisperer  Thanks.

0 Karma

bowesmana
Champion

I don't think it's sensibly possible with timecharts. You can covert time to some string value, which is sorted datewise, but you will be limited on number of data points.

| timechart span=1h count
| reverse
| eval t=strftime(_time,"%F %T")
| table t count

but it's not really what you're after

 

0 Karma

cheriemilk
Path Finder

I tried with stats and chart . but the chart doesn't reverse as expected.

baseQuery

| fieldformat _time=strftime(_time,"%Y-%m-%d %H:%M:%S")

| bin span=2h _time

| stats count by _time SFDC

| chart values(count) by _time, SFDC

| reverse

0 Karma

ITWhisperer
Ultra Champion

It appears that chart will order time earliest to latest. The closest I have got is this

baseQuery
``` new time as number of second until next hour ```
| eval time=relative_time(relative_time(now(),"@h")+3600-_time,"@h") 
``` 2 hour bins using new time ```
| bin span=2h time
``` stats using new time ```
| stats count by time SFDC
``` reformat new time for display purposes ```
| fieldformat time=strftime(relative_time(now(),"@h")+3600-time,"%Y-%m-%d %H:%M") 
``` new time as x-axis, count as y-axis, SFDC series ```
| xyseries time, SFDC, count

The problem with this is that the values on the x-axis are not displayed nicely.

0 Karma

cheriemilk
Path Finder

Hi @ITWhisperer 

there's no direct way to reverse the time order in timechart, right?

 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!