Getting multiple stats for a single search

[shengcow]

I am trying to get two different kinds of stats for the same search and I have been having problems. I want to get something like this:

... | stats latest(_time) as time
... | stats count by ip | where count > 10

But in one splunk search.

And I expect there to be multiple lines in stats like this:
time count ip
[time] null null
null 12 xx.xx.xx.xx

The time stamp should always be there but there may not be any IPs because of the where clause.
Is there any way to achieve this in one Splunk search? I will accept other format as long as it contains the information in stats.
Thank before.

[sideview]

There are some parts to your question that make it seem like a trick question.

index=_internal group=per_sourcetype_thruput | timechart count by series | untable _time series count

The part where you say

And I expect there to be multiple lines in stats like this:
time count ip
[time] null null
null 12 xx.xx.xx.xx

There is a row with a time value and neither count NOR ip value, and then there is another row with no time value, and an ip and a count.

So it's probably best if you give a little more detail, flesh out what you want the final output to look like, or get more specific about what you're ultimately trying to do.

Here is a search that will give you all of the possible combinations of ip value, and thirty second interval. it filters to only show the 30 second intervals that had more than 10 events with the given ip.

| bin _time span=30s | stats count by ip _time | where count>10

Here is another example where the filtering leaves ip's that have a total of 10 rows anywhere in the set, ie regardless of the time value.

| bin _time span=30s | stats count by ip _time | eventstats sum(count) as ipCount by ip | where ipCount>10

Here is another example where you get the ip's that appeared more than 10 times. Every row here also has a "time" field. the values on all rows are hte same, and they hold the time value of the single latest event in the set across all IP's.
| stats first(_time) as time count by ip | where count>10 | eventstats min(time) as time

Short answer: disambiguation needed!

UPDATE.

Throwing out one more search for you, based on some clarifications you made in other comments.
This search will give you the counts and IP's, for the IP's that had more than 10 occurrences in that timerange, and if there are no such IP's whatsoever, it will give you a single row with the latest time of the overall data. that row will have an ip value of "NULL", and again that row will vanish completely if there are any real IP values in the output.

| fillnull ip value="NULL" | stats first(_time) as time count by ip | where count>10 OR ip="NULL" | sort 0 + ip | streamstats count | where ip!="NULL" OR count="1"

[shengcow]

Thanks for the great answer. This search does give me all the expected information.

[Runals]

I'm having a hard time imagining what your events might be or ends you are trying to achieve =). Can you post some more concrete examples or types of logs you are looking at? If for example you are looking at firewall or IDS logs and you wanted the last time an IP did something where that something had to happen 10 times (ie show me the time stamp of all the IPs that have done a port scan with a min port scan of 10) you could do something like this.

... | stats latest(_time) as time count by ip | where count > 10 | convert ctime(time)

A potential challenge is all events in Splunk have timestamps even if there isn't one in the event. You could extract the timestamp out of the event as a field, convert it to epoch time, then get the latest value of desired. It also seems like you are doing a split by some additional field in your first stats command. If it comes to it you could run the search, get the latest time, do an append with the same search, and then somehow sort the results as/if needed.

[shengcow]

Thanks for the answer. The search you provided won't return the time stamp if no IP is found.

The scenario is quite complicated but a simple version is I am searching logs for some special IP addresses. The search is triggered when some external code calls it and should cover all the data since the last search by default. That's why I need the time stamp from the last search to see where to start for this search. It'll be great if you have any better architecture to achieve this.

[Runals]

It sounds like, perhaps, you need to get the latest timestamp because the external system is passing that back into the query the next time it runs as maybe earliest=(timestamp from the previous query). Seems like a ... strange ... arrangement of tools and procedure /shrug. I'm guessing the system is also passing the IPs in question as well. If the list of IPs is dynamic then why does the time of the last scan matter especially since there must be some trigger for the search to happen. Otherwise you could just create this as a scheduled search in Splunk or whatever system is querying Splunk externally (ie every 20 min). Do you need the time of the search or the time of the last time there was an event involving a particular IP? If you just need the time of the search you could do something like ... | eval current_time = now() | .. At any rate if this is just a matter of filling the field IP if there is no IP you could do

... | eval ip = if(isnotnull(ip), ip, "-") |  stats latest(_time) as time count by ip | where count > 10

but I don't think that will really solve the problem.

[shengcow]

It doesn't solve the problem, but thanks for the solution. I have to admit this is a very strange architecture. Splunk alerts and scheduled searches are definitely a better way. In my scenario, the Splunk server is heavily burdened and I only want to throw a search when needed(triggered), scheduled search and alerts do not have the flexibility in that sense.

[ramdaspr · Accepted solution]

.. | bucket _time span=30s | stats  count by ip,_time

you can modify the span as per your need, but this should give you the count per ip split when time is split into buckets of 30 seconds each.

[shengcow]

Thanks for the quick response. The search you provided does give me all the info I want, although the format is not what I expected. I will accept that if no better answer are provided.

[ramdaspr]

If the layout you need is Time, Count, IP all you need to do is add
table _time, count, ip
after the query