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+(?<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>
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.
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
I ended up using the the timewrap app
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
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 !!!
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?
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*
Hope this will helps you. If so accept the answer.
You can get rid of subsearch in the first query as you're just using the timerange picker values as it is anyways.
@dbcase: are you sure the other query is the one you want???
Both of them worked so Ideally I would like to accept both answers!
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.
WOW! What a query!!!!! And.... it works great!!!! Thank you vasanthmss!!!
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?
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?