topic Re: Group data, but keeping data to be grouped. in Splunk Search

Group data, but keeping data to be grouped.

dreadangel — Thu, 28 Mar 2019 14:03:14 GMT

Hi all,

I got some data structured next:

url    user     event
-------------------------------------
Url1, user1, EventType1
Url1, user1, EventType2
Url2, user1, EventType3
Url2, user1, EventType1
Url1, user2, EventType1
Url2, user2, EventType2
Url3, user2, EventType3
...

My goal is to build a statistics table:

    [Url],  [Total events count], [Distinct users count], [Top $x users + count list]
    Url1                       2                       2                     user1 - 1
                                                                             user2 - 1
    -----------------------------------------------------------------------------------
    Url2,                      3,                      1,                    user1 - 1
                                                                             user2 - 1
    -----------------------------------------------------------------------------------
    Url3,                      1,                      1,                    user2 - 1
    -----------------------------------------------------------------------------------

After using stats command in pipeline grouped data isn't available anymore, so is it possible "to add" statistics to the result or should I use multiple subsearches - each acquiting its goal and after joining them?

Re: Group data, but keeping data to be grouped.

renjith_nair — Thu, 28 Mar 2019 14:53:00 GMT

@dreadangel ,

Try

"base search"|stats dc(event) as "Event Count" , dc(user) as Distinct_Users, values(user) as Users by Url

If you need further stats by keeping this data , use eventstats . For eg. |eventstats count as "Total_URL_Count"

Re: Group data, but keeping data to be grouped.

DMohn — Thu, 28 Mar 2019 15:12:21 GMT

It might be a bit ugly, but this query gives exactly the result you want...

<your_base_query> | stats count as "Total Events", dc(user) as "Distinct Users" by url | appendcols [<your_base_query> | stats count by url, user | eval user = user." - ".count | stats list(user) as "Users + count" by url]

In case you want a distinct event count an no total count, just swap the first stats count for a stats dc(events)

Re: Group data, but keeping data to be grouped.

dreadangel — Fri, 29 Mar 2019 10:44:00 GMT

Thank you for the tip...

Re: Group data, but keeping data to be grouped.

dreadangel — Fri, 29 Mar 2019 10:44:47 GMT

Thank you for the tip upon eventstats - this indeed solves