topic Re: How do you search results produced from a timechart...by command? in Splunk Search

How do you search results produced from a timechart...by command?

saltmills — Tue, 13 Mar 2012 00:25:25 GMT

I have a query similar to below:

... | timechart span=30s count by host

resulting in output that looks like:

_time hosta hostb 3/12/12 15:10:00 6 0 3/12/12 15:10:30 10 5

From this output I'm only interested in '0' values and would like to report host and time values for those instances. I'm unable to craft a query to get a handle on values in this timechart command. Is there some way to address the count and host values in a subsequent search?

Re: How do you search results produced from a timechart...by command?

Damien_Dallimor — Tue, 13 Mar 2012 02:45:44 GMT

Try something like :

... | bucket _time span="30s"| stats count by host,_time | where count=0

Re: How do you search results produced from a timechart...by command?

saltmills — Tue, 13 Mar 2012 04:07:13 GMT

Thanks for response.

I'm actually using bucket command earlier in the query. However, it will not report a _time value for which no matching events occur, and no count=0 match will ever appear in your final results. By having the timechart command I can at least get count=0 instances appearing in the results along with associated _time and host, but the question is how do I isolate them?

Re: How do you search results produced from a timechart...by command?

Damien_Dallimor — Wed, 14 Mar 2012 02:47:31 GMT

Ok , the untable command after timechart seems to produce the desired output

... | timechart span="30s" count by  host | untable _time host count | where count=0

Re: How do you search results produced from a timechart...by command?

saltmills — Wed, 14 Mar 2012 05:50:35 GMT

Nice. That does it! Thanks for help.

Re: How do you search results produced from a timechart...by command?

Damien_Dallimor — Wed, 14 Mar 2012 06:09:26 GMT

Great! Happy Splunking.

Re: How do you search results produced from a timechart...by command?

mpwelch — Sat, 13 Jun 2020 22:35:51 GMT

There is a subtle but very important distinction between Bin w/Span and STATS by metric _time .vs. timechart with Span by metric . The timechart shows the intervals even when there are no events whereas the Stats with Bin will not. So if you need to know when an event is missing timechart with untable is your friend.

Re: How do you search results produced from a timechart...by command?

to4kawa — Sun, 14 Jun 2020 00:09:11 GMT

I looked into it once.

It's in Japanese, but try using Google Translate.

日本語:

https://qiita.com/toshikawa/items/8fa44753bc8f2a4c9cb0

English translate:
https://translate.google.co.jp/translate?hl=ja&sl=ja&tl=en&u=https%3A%2F%2Fqiita.com%2Ftoshikawa%2Fitems%2F8fa44753bc8f2a4c9cb0

Personally, I think stats and bin are easier to do if you want to aggregate.

Re: How do you search results produced from a timechart...by command?

mpwelch — Sun, 14 Jun 2020 16:59:27 GMT

I think you missed the point. timechart span=1m creates _time for each minute whereas bin _time span=1m only has _time for events which exist. timechart can be useful to see for instance the minutes where expected events didn't occur but should have. Identifying minutes where count=0 is easily accomplished with timechart but with a by the untable is needed to allow where count=0. In any case, the suggestion to use untable then use the where statement with timechart/by solved my problem and why I gave Karma. How do you search results produced from a timechart with a by? Use untable!