topic Re: timechart with stats and eval in Splunk Search

timechart with stats and eval

subtrakt — Sat, 17 May 2014 20:14:30 GMT

Hi,
Here's my query -

... 500 | stats dc(WEB_IP) as TEST2 | eval TEST1=WEBURL." ".TEST2 | timechart count by TEST1

Seems simple but i am not having any luck getting the timechart to work.

The end result will be a chart that shows URLs [WEBURL] experiencing 500 errors and
in the chart legend [TEST1], the URL will be displayed and a count beside it that shows how many different IPs [dc(WEB_IP)] have experienced a 500.

Re: timechart with stats and eval

linu1988 — Sat, 17 May 2014 20:41:22 GMT

 ...500 | stats dc(WEB_IP) as TEST2 by _time | eval TEST1=WEBURL." ".TEST2 | timechart count by TEST1

Re: timechart with stats and eval

martin_mueller — Sat, 17 May 2014 22:53:15 GMT

There's no field WEBURL after the stats.

Re: timechart with stats and eval

somesoni2 — Sat, 17 May 2014 22:53:41 GMT

Try this

...500 | eventstats dc(WEB_IP) as TEST2  by WEBURL| eval TEST1=WEBURL." ".TEST2 |timechart count by TEST1

Updated 2:

try this

The subsearch ensures that only the top 10 WEBURL (based on count) are included in search.

 ...500 [search ...500 | top limit=10 WEBURL | table WEBURL | format] | eventstats dc(WEB_IP) as TEST2  by WEBURL | eval TEST1=WEBURL." ".TEST2 |timechart count by TEST1

Re: timechart with stats and eval

martin_mueller — Sat, 17 May 2014 23:01:18 GMT

Wouldn't the timechart count by TEST1 always yield a count of 1? There can only be one dc(WEB_IP) by WEBURL row for every TEST1 because it contains the WEBURL.

Bottom line, I'm not quite sure what @subtrakt is even looking for as the result.

Re: timechart with stats and eval

somesoni2 — Sat, 17 May 2014 23:21:59 GMT

Thats the issue with question with fewer information. Based on the information available, a chart showing URL with 500 error and with URL show the distinct no of IPs using that URL with 500 error was required. Not sure whether a timechart is necessary or not. Up to the @subtrakt to confirm. Request him to provide more information.

Re: timechart with stats and eval

subtrakt — Sat, 17 May 2014 23:30:08 GMT

That works beautifully thanks somesoni2! To add to this and reduce noise, does anyone know how to show only URLS with > 10 500s in the search range? ... | eventstats dc(WEB_IP) as TEST2 by WEBURL | where WEBURL > 10

Re: timechart with stats and eval

subtrakt — Sat, 17 May 2014 23:30:42 GMT

above is my first guess but eventstats doesn't count that way apparently.

Re: timechart with stats and eval

somesoni2 — Sat, 17 May 2014 23:40:58 GMT

Give a try to updated answer.

Re: timechart with stats and eval

subtrakt — Mon, 28 Sep 2020 16:39:05 GMT

Unfortunately, that came back with an empty chart. Now that i think about it, If i could sort the timechart by the highest dc(WEB_IP) count and set the timechart for useother=f that would be the best option. I put "timechart... | sort WEB_IP" but it didn't seem to reflect the low end of the spectrum when i did "| sort -WEB_IP" and the high end of the spectrum when i did "| sort WEB_IP"

Re: timechart with stats and eval

somesoni2 — Sun, 18 May 2014 00:28:45 GMT

See if new answer works.

Re: timechart with stats and eval

subtrakt — Mon, 28 Sep 2020 16:39:13 GMT

That works if i'm trying to get the most-hit-URL. But if a single IP is causing noise - that would not be the goal of the chart. I was hoping i could do something like this - "...| eventstats dc(WEB_IP) as TEST2 by WEBURL| eval TEST1=WEBURL." ".TEST2 | timechart count limit=10 by TEST1 | sort - TEST2" and that would show the URLs with highest dc(WEB_IP) count.

Re: timechart with stats and eval

somesoni2 — Sun, 18 May 2014 18:25:39 GMT

How about this

Re: timechart with stats and eval

subtrakt — Sun, 18 May 2014 20:18:56 GMT

Bravo! that is impressive! I'm going to have to do some research and learn what all this means.

Re: timechart with stats and eval

subtrakt — Sun, 18 May 2014 21:04:22 GMT

looks like on a 2 hour search the search is somehow dropping off data on the middle of the chart(1hour). i was thinking window=1 might have something to do with it.

Re: timechart with stats and eval

martin_mueller — Sun, 18 May 2014 22:13:35 GMT

window=1 current=f is telling the streamstats command to use the previous row for grabbing last(TEST2) as prevTEST2 - that's unrelated to the time range.

Re: timechart with stats and eval

subtrakt — Sun, 18 May 2014 23:52:07 GMT

Thanks Martin. I added some filters to decrease the search results and it seems to be working fine now.