Splunk Search

timechart with stats and eval

subtrakt
Contributor

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.

Tags (3)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Contributor

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

subtrakt
Contributor

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.

0 Karma

subtrakt
Contributor

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

subtrakt
Contributor

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

See if new answer works.

0 Karma

subtrakt
Contributor

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"

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give a try to updated answer.

0 Karma

subtrakt
Contributor

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

0 Karma

subtrakt
Contributor

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

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

martin_mueller
SplunkTrust
SplunkTrust

There's no field WEBURL after the stats.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...