Splunk Search

stats: count number of occurences for each value in a values() aggregate

Explorer

Another question on counting the number of events per values() value in stats command.
Using sistats this is seems to be done automatically. For example having events with srcip, destip, user (and a couple of more) fields.

   sistats values(src_ip) as src_ip, values(user) as user by dest_ip 

gives me for example

    dest_ip       src_ip            user   
    1.2.3.4       8.9.10.11;3        root;1
                  10.11.12.13;2      marcel;4

but using stats only shows:

    dest_ip       src_ip            user
    1.2.3.4       8.9.10.11          root
                  10.11.12.13        marcel

So splunk already can already count the number of occurrences per value but does not show it for plain stats

I started using sistats to build a summary index, but using the implicitly added symmaryindex command does not seem to allow me to specify sourcetype and source values for example. Therefor I want to revert to stat and explicitly use the collect command to build a summary index. But then I loose the counts per value in the values() results as shown above.

I tried to solve that by adding eventstats:

    | eventstats count(user) as usercnt by user,  dest_ip
    | eventstats count(src_ip) as srcipcnt by src_ip,  dest_ip
    | eval user=user . ";" . usercnt, src_ip=src_ip . ";" . srcipcnt  
    | stats values(src_ip) as src_ip, values(user) as user by dest_ip 

This works fine for small input datasets, but it quickly runs out of memory on large base search results (the actual query has additional by fields and aggregates), while the sistats version runs fine.

Any thoughts suggestions ideas?

@splunk development team: maybe you can add some optional functionality to the values() function to add such count information?

Thanks
-Marcel

0 Karma