topic Re: How to edit my search to chart a trend line over time? in Splunk Search

How to edit my search to chart a trend line over time?

koushiknandan — Mon, 27 Jun 2016 17:25:08 GMT

I am trying to use the below search and plot a graph for the TPS field.

So, if I draw a chart with the TPS values over a day (duration) with a span of 1 min/5 mins, it would show a line graph over a day (duration) with the TPS value plotting over time.

 host=X source=Y.log "data available" | stats min(_time) as EARLIEST
 | appendcols [
     search host=X source=Z.log 5.7_WOLFER | stats max(_time) as LATEST ] 
 | appendcols [
     search host=X source=Y | stats count(ITIM_ID) as count ] 
 | eval TPS=count/(LATEST-EARLIEST)

Tried to use timechart in the following way, but didn't work

 | timechart span=1m avg(eval(TPS=count/(LATEST-EARLIEST)))

https://answers.splunk.com/answers/390329/how-to-run-multiple-queries-at-once-with-calculati.html

Re: How to edit my search to chart a trend line over time?

woodcock — Mon, 27 Jun 2016 17:57:05 GMT

Like this:

  ... | timechart span=1m avg(eval(count/(LATEST-EARLIEST))) AS TPS

Or this:

 ... | eval TPS=count/(LATEST-EARLIEST) | timechart span=1m avg(TPS) AS TPS

Re: How to edit my search to chart a trend line over time?

koushiknandan — Tue, 28 Jun 2016 11:23:25 GMT

Didn't work.

Adding any of the timechart throws error "No results found".

Re: How to edit my search to chart a trend line over time?

woodcock — Tue, 28 Jun 2016 13:40:15 GMT

Show us the results of your first search.

Re: How to edit my search to chart a trend line over time?

koushiknandan — Tue, 28 Jun 2016 14:07:10 GMT

The query would show the following data in a table. I only want the TPS data to show as a trendline (chart).

 Start Time - 2016-04-07 13:41:59
 End Time - 2016-04-07 16:20:59
 Count (ITIM_ID) - 100
 TPS=(End Time-Start Time/Count(ITIM_ID)) - 56.76 *No. as per formulae*

Thanks,
Koushik

Re: How to edit my search to chart a trend line over time?

woodcock — Tue, 28 Jun 2016 14:16:16 GMT

We need to see the actual event data returned by your search, without reformatting/summarizing.

Re: How to edit my search to chart a trend line over time?

koushiknandan — Tue, 29 Sep 2020 10:05:03 GMT

PERFORM 2015/06/29 14:11:21 -A- data available: 'XXXXXXX21467025246209'
PERFORM 2015/06/29 14:11:21 -A- 5.7_WOLFER 'XXXXXXX21467025246209'
PERFORM 2015/06/29 14:11:21 -A- data available: 'XXXXXXX21467025246225'
PERFORM 2015/06/29 14:11:21 -A- 5.7_WOLFER 'XXXXXXX21467025246225'
PERFORM 2015/06/29 14:11:24 -A- data available: 'XXXXXXX21467025246265'
PERFORM 2015/06/29 14:11:24 -A- 5.7_WOLFER 'XXXXXXX21467025246225'
PERFORM 2015/06/29 14:11:25 -A- data available: 'XXXXXXX21467025246205'
PERFORM 2015/06/29 14:11:25 -A- 5.7_WOLFER 'XXXXXXX21467025246225'

In the above log, count of ITIM_ID is 4

EARLIEST = 2015/06/29 14:11:21
LATEST = 2015/06/29 14:11:25

Difference = 4 seconds

TPS = 4/4 = 1

When I am running the above query for an hour, I am getting TPS value of the hour.

But, is it possible to get the same data over a trendline, which would say if the TPS value is varying over time, or, remaining fixed? All ideas are welcome.

Many Thanks,
Koushik

Re: How to edit my search to chart a trend line over time?

koushiknandan — Mon, 11 Jul 2016 16:35:26 GMT

@woodcock, can you please help me out on this?

Re: How to edit my search to chart a trend line over time?

sundareshr — Mon, 11 Jul 2016 18:23:38 GMT

Try this (5 min interval)

...  | eval TPS=count/(LATEST-EARLIEST) | bucket bins=288 EARLIEST | stats count TPS by EARLIEST

Re: How to edit my search to chart a trend line over time?

woodcock — Mon, 11 Jul 2016 23:29:58 GMT

I used this to fake the events:

|noop|stats count AS raw|eval raw=
"PERFORM 2015/06/29 14:11:21 -A- data available: 'XXXXXXX21467025246209'::
PERFORM 2015/06/29 14:11:21 -A- 5.7_WOLFER 'XXXXXXX21467025246209'::
PERFORM 2015/06/29 14:11:21 -A- data available: 'XXXXXXX21467025246225'::
PERFORM 2015/06/29 14:11:21 -A- 5.7_WOLFER 'XXXXXXX21467025246225'::
PERFORM 2015/06/29 14:11:24 -A- data available: 'XXXXXXX21467025246265'::
PERFORM 2015/06/29 14:11:24 -A- 5.7_WOLFER 'XXXXXXX21467025246225'::
PERFORM 2015/06/29 14:11:25 -A- data available: 'XXXXXXX21467025246205'::
PERFORM 2015/06/29 14:11:25 -A- 5.7_WOLFER 'XXXXXXX21467025246225'"
| makemv delim="::" raw
| mvexpand raw
| rex field=raw "(?<sourcetype>\S+)\s+(?<time>\S+\s+\S+)\s+-A-\s+(?<ITIM_ID>.*?)\s+'"
| eval _time=strptime(time,"%Y/%m/%d %H:%M:%S")

Then I added this which does the work and worked for me:

| stats range(_time) AS spanSeconds count BY ITIM_ID
| eval TPS = count/spanSeconds