Splunk Search

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

koushiknandan
New Member

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

sundareshr
Legend

Try this (5 min interval)

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

woodcock
Esteemed Legend

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
0 Karma

koushiknandan
New Member

Didn't work.

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

0 Karma

woodcock
Esteemed Legend

Show us the results of your first search.

0 Karma

koushiknandan
New Member

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

koushiknandan
New Member

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

0 Karma

koushiknandan
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...