Count number of visits group events by day

[user93]

Hi,

So, I want to count the number of visitors to a site, but because of the logging mechanism, I get many events per visit. 

I want to define a visit as 1 user per day.

<basesearch>
|table time,username

Where username is the same and time is within 24 hours ( 1 day), I want to count as 1. 

[ITWhisperer · Accepted solution]

<basesearch>
|bin span=1d _time
|stats count by _time username
|stats count by _time

[user93]

Actually, after some double checking, I don't this is the correct answer. Seems that when I cam a distinct count |stats dc(username) I get the same value. I would expect visits to be higher, because one user will login multiples times per month.

|rex field=_raw "User\s(?<username>[^\s]+)\swith\sIP"
|bin span=1mon _time |stats count by _time username |stats count by _time

count is the same as 

|rex field=_raw "User\s(?<username>[^\s]+)\swith\sIP"
|bin span=1mon _time |stats count by _time username |stats count by _time
|stats dc(username)

[ITWhisperer]

If you want to each day a user visits the site, why are you setting bin span to 1 month. This sets all the timestamps to the beginning of the month so with your search line

|bin span=1mon _time |stats count by _time username |stats count by _time

|bin span=1mon _time

sets all the timestamps to the beginning of the month

                     |stats count by _time username

gets you a count for the number of times each user has visited the site each month

                                                    |stats count by _time

counts the number of users that visited the site per month

Similarly, by using a span of 1 day (as I suggested), you get a count for each user per day (this is really just to get an event for each user - the count is ignored), then a count for the number of stats events per day, which is equivalent to the number of users per day, which is what you asked for. You are right, dc could also be used, although you still need to set the span to 1d if you want the daily count of visitors

<basesearch>
|bin span=1d _time
|stats dc(username) by _time

[user93]

Hi @ITWhisperer ,

Thank you again for your help. Yes, setting to 1 month is wrong in fact and 1 day is what I am trying to count where a visit is defined as 1 user per 1 day. 

Where this went wrong is that what I actually want to do is sum up that count for each day of the month, over 6 months or a year, to then average a number of visits per month. 

-

So, when checking the number that it provided, after I mistakenly set the bin to 1 month,  the sum ended up being the same as the distinct count, and that is how I knew there was an error. I continued on, removing the final |stats count _time, and exporting to get a table with 3 rows: _time, usercode,count of visits, and with the export I counted by row.

Today, following your council, I rerun with the correct bin value, export and sum the days for each month. and I get the same value.

-

While I now have the number I need, for me to achieve fully my goal in Splunk, I need to add some kind of  |eval following the |stats count of visits by day to provide a sum by month, that I can then chart by month for dash boarding or average in Splunk

Kind regards,

@user93 

[ITWhisperer]

<basesearch>
|bin span=1d _time
|stats dc(username) as users by _time
|bin span=1mon _time
|stats avg(users) as average by _time