Splunk Search

Graph the same time period but from 1 week previous

dbcase
Motivator

Hi,

I have a query that looks like this

 <chart depends="$tableurlerror$">
        <title>URL Errors by Host Details for - $tableurlerror$ for $field1.earliest$ to $field1.latest$</title>
        <search>
          <query>source="/etc/httpd/logs/*" index=main | rex "HTTP.\d.\d.\s+(?&lt;status&gt;\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |timechart count by host</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>

It graphs all the >=40* and >=50* errors that occurred on a given host over the last 6 hours

What I'd like to do is graph the same criteria, except it should be the same 6 hour period but from 1 week ago.

0 Karma
1 Solution

sundareshr
Legend

Try this

source="/etc/httpd/logs/*" index=main [| search index=* earliest=$field1.earliest$ | head 1 | addinfo | eval earliest=relative_time(info_min_time, "-1w@w") | return earliest ] | eval when=if(_time<relative_time(now(), "$field1.earliest$"), "Previous", "Current") | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |bin _time | eval t=_time."#".host | chart count by t when | rex field=t "(?<_time>[^#]+)#(?<host>.*)" | fields - t

View solution in original post

0 Karma

dbcase
Motivator

I ended up using the the timewrap app

0 Karma

sundareshr
Legend

Try this

source="/etc/httpd/logs/*" index=main [| search index=* earliest=$field1.earliest$ | head 1 | addinfo | eval earliest=relative_time(info_min_time, "-1w@w") | return earliest ] | eval when=if(_time<relative_time(now(), "$field1.earliest$"), "Previous", "Current") | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |bin _time | eval t=_time."#".host | chart count by t when | rex field=t "(?<_time>[^#]+)#(?<host>.*)" | fields - t
0 Karma

vasanthmss
Motivator

Try this ,

<chart depends="$tableurlerror$">
         <title>URL Errors by Host Details for - $tableurlerror$ for $field1.earliest$ to $field1.latest$</title>
         <search>
           <query>source="/etc/httpd/logs/*" index=main [|stats c | addinfo | eval earliest=info_min_time-604800 | eval latest=info_max_time-604800 | return earliest, latest] | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |timechart count by host</query>
           <earliest>$field1.earliest$</earliest>
           <latest>$field1.latest$</latest>
         </search>

your query,

source="/etc/httpd/logs/*" index=main | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |timechart count by host

new query,

source="/etc/httpd/logs/*" index=main [|stats c | addinfo | eval earliest=info_min_time-604800 | eval latest=info_max_time-604800 | return earliest, latest] | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*|where host="$tableurlerror$"  |timechart count by host

added new sub query to calculate earliest and latest (moving cursor to last 7 days),

 |stats c | addinfo | eval earliest=info_min_time-604800 | eval latest=info_max_time-604800 | return earliest, latest

Hope this will helps you !!!

V

dbcase
Motivator

Hi Vasanthmss,

Many thanks for the reply! I think I understand your query but its not quite doing what I hoped. What I'm hoping to do is:

Line graph series 1:
The last 6 hours of values from today (8/29/2016) (example, 100, 200, 5, 10)

Line graph series 2:
The same 6 hours but from last Monday (8/22/2016) (example 50,30,200,300)

Does that help?

0 Karma

vasanthmss
Motivator

Above answer is for your question, and your comment referrers something else , any how this will helps you,

Search,

index=main source="/etc/httpd/logs/*"  host="$tableurlerror$" [|stats c | addinfo | eval earliest=info_min_time| eval latest=info_max_time | return earliest latest]  | rex "HTTP.\d.\d.\s+(?<status>\d+)" |search status=40* OR status=50*  | eval when="Current"  |bin _time  |stats count as total by host,_time,when | streamstats c 
|append[ search  index=main source="/etc/httpd/logs/*"  host="$tableurlerror$"  [|stats c | addinfo | eval earliest=info_min_time-604800 | eval latest=info_max_time-604800 | return earliest latest]  | rex "HTTP.\d.\d.\s+(?<status>\d+)" | eval when="Past" |bin _time   |stats count as total by host,_time,when | streamstats c ] | eval time=if(when="Current",_time,null) | sort 0 c | filldown time | eval _time=time | timechart sum(eval(if(when="Current",total ,0))) as totals1, sum(eval(if(when="Past",total ,0))) as totals2  by host | rename totals1* as Current*, totals2* as Past*
  • take care of special chars - preferred to add this search by editing the dashboard panel - edit search
  • Past week will not give you the date and time ( 08/29 as present that will be listed in x and 08/22 will not be shown in the mouse over

Hope this will helps you. If so accept the answer.

V
0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can get rid of subsearch in the first query as you're just using the timerange picker values as it is anyways.

0 Karma

vasanthmss
Motivator

@dbcase: are you sure the other query is the one you want???

V
0 Karma

dbcase
Motivator

Both of them worked so Ideally I would like to accept both answers!

0 Karma

Cuyose
Builder

This solution really should be advertised here. This is something that many people are wanting to do, me included, and the only other solutions were very cludgy and did not work with the timepicker very well.

0 Karma

dbcase
Motivator

WOW! What a query!!!!! And.... it works great!!!! Thank you vasanthmss!!!

0 Karma

sundareshr
Legend

I assume field1 is a timepicker. What should be the comparison if user picks Last 7 days or something else, that's not in hours?

0 Karma

dbcase
Motivator

Hi Sundareshr,

You are correct! (always) If the user selects the last 7 days then the graph should reflect that and the 7 days before. Does that make sense?

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 ...