topic Re: Stats output into timechart in Splunk Search

Stats output into timechart

thisissplunk — Tue, 28 Oct 2014 18:58:48 GMT

Hello,

I've seen similar posts but they do not answer this question. What I'm trying to do is take the Statistics number received from a stats command and chart it out with timechart.

My search before the timechart:

index=network sourcetype=snort msg="Trojan*" | stats count first(_time) by host, src_ip, dest_ip, msg

This returns 10,000 rows (statistics number) instead of 80,000 events. How do I use that data and chart the results over a timechart? There should be 10,000 total events on the timechart, not 80,000. I can't figure it out.

In other words, the results (rows) of the stats command are in reality new events to me. I deduped 80,000 down to 10,000 and now I want to see one line on a timechart represent how many new "events" there are over time.

Re: Stats output into timechart

jtrucks — Tue, 28 Oct 2014 19:09:07 GMT

You can't do multiple fields after a by clause for a timechart.

Also, your stats loses all time related values, so you can't ... | timechart since there is no longer time data.

If you are looking to aggregate over a timeframe, say, per hour, then you could try something like

index=network sourcetype=snort msg="Trojan*" | stats count by date_hour, host, src_ip, dest_ip, msg

This gives you a chart with the hours along the bottom.

If you need a true timechart effect, then try something more like this:

index=network sourcetype=snort msg="Trojan*" | stats count by _time, host, src_ip, dest_ip, msg

Your output will be different than when not counting by unique timestamp of the index event.

Re: Stats output into timechart

thisissplunk — Tue, 28 Oct 2014 19:32:46 GMT

It doesn't work. The problem seems to be that timechart cannot accept more than one value after the BY clause. This is what led me to crafting a stats statement first.

Re: Stats output into timechart

martin_mueller — Tue, 28 Oct 2014 19:35:29 GMT

Here's what you actually use:

index=network sourcetype=snort msg="Trojan*" | eval combined = host."_".src_ip."_".dest_ip."_".msg | timechart count by combined

That way you get your grouping by all four values and timechart is happy.

Re: Stats output into timechart

acharlieh — Tue, 28 Oct 2014 19:42:16 GMT

Also you may be interested in the limit and useother parameters on timechart. 10,000 different series of data is a lot to display on a graph, so you wind up with the top 10 series by default, and the other 9990 series are summed together in a field called "OTHER".

Ultimately what is it that you want to show in your chart?

Re: Stats output into timechart

martin_mueller — Tue, 28 Oct 2014 19:44:44 GMT

Oh yeah, 10000 serieseses isn't going to be fun. Alternative search:

 index=network sourcetype=snort msg="Trojan*" | bucket _time | stats count by _time, host, src_ip, dest_ip, msg

That'll create a huge bunch of rows rather than 10000 fields.

Re: Stats output into timechart

jtrucks — Tue, 28 Oct 2014 19:45:48 GMT

I removed the original answer and replaced it.

Re: Stats output into timechart

thisissplunk — Tue, 28 Oct 2014 19:47:38 GMT

What I'm trying to show is the amount of rows per hour or so. "100 rows hit at 1 pm, 93 hit at 2 pm" etc.

Re: Stats output into timechart

martin_mueller — Tue, 28 Oct 2014 19:48:57 GMT

Are you possibly looking for a | timechart span=1h dc(combined) as "Number of rows hit"?

Re: Stats output into timechart

acharlieh — Mon, 28 Sep 2020 18:01:16 GMT

So a different note here, ( Reference ) date_hour is only present if the timestamp exists and is extracted from the raw event itself. It's also in the timezone of the log and not the timezone of you the splunk user (_time is the UTC timestamp, and is by default displayed in your timezone)... which could be important if you are combining logs from different timezones or across days. You could also use |eval _time=relative_time(_time,"@h"), or |bin _time span=1h or |eval hour=strftime(_time, "%H") for getting a field by hour based on the _time field.

Re: Stats output into timechart

thisissplunk — Tue, 28 Oct 2014 19:58:18 GMT

I'll try my best to explain, but maybe timechart is overkill. I want to simply chop up the RESULTS from the stats command by hour/day. I want to count how many unique rows I see in the stats output fall into each hour, by day. In other words, I want one line on the timechart to represent the AMOUNT of rows seen per hour/day of the STATS output (the rows). There should be a total of 10,000 events on the timechart, not 80,000, because 10,000 was returned by the stats command.

Imagine a line in front of you. At any hour, it should tell you how many times there was a unique rows from the stat command in that time frame.

The combined option you showed above is producing a line for each and every unique row form the stats command. This isn't what I want.

Re: Stats output into timechart

martin_mueller — Tue, 28 Oct 2014 20:05:16 GMT

That sounds a lot like the | bucket _time | stats ... by _time ... approach.

Re: Stats output into timechart

acharlieh — Tue, 28 Oct 2014 20:11:58 GMT

This is slightly off topic, but something that may find interesting is the sparkline: you can have a would give you a mini graph on each event to show how that event trends over your search window.

index=network sourcetype=snort msg="Trojan*" | stats count sparkline by host, src_ip, dest_ip, msg

But also did you try martin's suggestion of:

index=network sourcetype=snort msg="Trojan*" | eval combined = host."_".src_ip."_".dest_ip."_".msg | timechart span=1h dc(combined) as "Number of rows hit"

That should be a single line giving the number of distinct combinations per hour.

Re: Stats output into timechart

thisissplunk — Tue, 28 Oct 2014 20:34:25 GMT

Thank you all for the pointers. After a short walk I realized that in essence, I was using the stats command to dedup the results. After this realization I exchanged stats with dedup and used that in timechart. This was successful.

This is what I ended up using for reference:

index=network sourcetype=snort name="Trojan*" | dedup host src_ip dest_ip msg | timechart span=1d count

This was a case of always using stats for multiple purposes which complicated the timechart. The "combined =" solution below was on par with what I was trying to do.