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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...