topic Re: Graph the same time period but from 1 week previous in Splunk Search

Graph the same time period but from 1 week previous

dbcase — Mon, 29 Aug 2016 15:03:58 GMT

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.

Re: Graph the same time period but from 1 week previous

sundareshr — Mon, 29 Aug 2016 15:27:24 GMT

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?

Re: Graph the same time period but from 1 week previous

dbcase — Mon, 29 Aug 2016 15:30:00 GMT

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?

Re: Graph the same time period but from 1 week previous

vasanthmss — Mon, 29 Aug 2016 16:00:23 GMT

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

Re: Graph the same time period but from 1 week previous

dbcase — Mon, 29 Aug 2016 16:10:36 GMT

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?

Re: Graph the same time period but from 1 week previous

sundareshr — Mon, 29 Aug 2016 16:35:30 GMT

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

Re: Graph the same time period but from 1 week previous

vasanthmss — Mon, 29 Aug 2016 20:12:55 GMT

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.

Re: Graph the same time period but from 1 week previous

dbcase — Mon, 29 Aug 2016 20:49:48 GMT

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

Re: Graph the same time period but from 1 week previous

somesoni2 — Mon, 29 Aug 2016 20:57:31 GMT

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

Re: Graph the same time period but from 1 week previous

vasanthmss — Tue, 30 Aug 2016 15:52:34 GMT

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

Re: Graph the same time period but from 1 week previous

dbcase — Tue, 30 Aug 2016 15:59:38 GMT

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

Re: Graph the same time period but from 1 week previous

Cuyose — Mon, 19 Sep 2016 19:32:02 GMT

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.

Re: Graph the same time period but from 1 week previous

dbcase — Mon, 19 Sep 2016 19:39:01 GMT

I ended up using the the timewrap app