timechart with stats and eval

[subtrakt]

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.

[somesoni2 · Accepted solution]

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

[subtrakt]

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

[martin_mueller]

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.

[subtrakt]

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.

[subtrakt]

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

[somesoni2]

How about this

...| eventstats dc(WEB_IP) as TEST2 by WEBURL | sort -TEST2 | streamstats window=1 current=f last(TEST2) as prevTEST2 | eval sno=case(isnull(prevTEST2),1,prevTEST2=TEST2,0,1=1,1) | accum sno | where sno < 11 | eval TEST1=WEBURL." ".TEST2 |timechart count by TEST1

[subtrakt]

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.

[somesoni2]

See if new answer works.

[subtrakt]

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"

[somesoni2]

Give a try to updated answer.

[subtrakt]

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

[subtrakt]

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

[somesoni2]

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.

[martin_mueller]

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.

[linu1988]

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

[martin_mueller]

There's no field WEBURL after the stats.