Sorry for the confusing title. I'm trying to find out:
1) How many days a user has logged on (doesn't matter how many times in a day, just one or more counts)
2) Average this over the number of days total that they've been in the system (today minus the days since the user first logged in ever)
I tried something like this, which I know is totally wrong because I can't even get all of the parts to run properly and go into the table.
| stats earliest(_time) as first_login by client_user
| eval days_since = (now() - first_login)/86400
| eval duration_since = tostring(now() - first_login, "duration")
| stats dc(_time) as Active_Days by client_user
| bucket _time span=1day
| eval Average_Activity = Active_Days / duration_since
| table client_user, duration_since, Active_Days, Average_Activity
Thanks for any and all help! I have absolutely no coding background, I've been just trying to piece together answers from other Splunk questions into my query.
@sharonmok, please try the following search:
<yourBaseSearch>
| eventstats min(_time) as First_Login by client_user
| bin _time span=1d
| stats min(First_Login) as First_Login by _time client_user
| streamstats count as Active_Days by client_user
| reverse
| dedup client_user
| eval First_Login=strptime(strftime(First_Login,"%Y/%m/%d"),"%Y/%m/%d")
| eval Duration_Since=round((_time-First_Login)/86400)
| eval Average_Activity = Active_Days/Duration_Since
| fillnull value=0 Average_Activity
| fieldformat First_Login=strftime(First_Login,"%Y/%m/%d")
Following is a run anywhere search based on Splunk's _internal index on similar lines where component field is used instead of client_user:
index=_internal sourcetype=splunkd log_level!="INFO"
| eventstats min(_time) as First_Error by component
| bin _time span=1d
| stats min(First_Error) as First_Error by _time component
| streamstats count as Active_Days by component
| reverse
| dedup component
| eval First_Error=strptime(strftime(First_Error,"%Y/%m/%d"),"%Y/%m/%d")
| eval Duration_Since=round((_time-First_Error)/86400)
| eval Average_Activity = Active_Days/Duration_Since
| fillnull value=0 Average_Activity
| fieldformat First_Error=strftime(First_Error,"%Y/%m/%d")
@sharonmok, please try the following search:
<yourBaseSearch>
| eventstats min(_time) as First_Login by client_user
| bin _time span=1d
| stats min(First_Login) as First_Login by _time client_user
| streamstats count as Active_Days by client_user
| reverse
| dedup client_user
| eval First_Login=strptime(strftime(First_Login,"%Y/%m/%d"),"%Y/%m/%d")
| eval Duration_Since=round((_time-First_Login)/86400)
| eval Average_Activity = Active_Days/Duration_Since
| fillnull value=0 Average_Activity
| fieldformat First_Login=strftime(First_Login,"%Y/%m/%d")
Following is a run anywhere search based on Splunk's _internal index on similar lines where component field is used instead of client_user:
index=_internal sourcetype=splunkd log_level!="INFO"
| eventstats min(_time) as First_Error by component
| bin _time span=1d
| stats min(First_Error) as First_Error by _time component
| streamstats count as Active_Days by component
| reverse
| dedup component
| eval First_Error=strptime(strftime(First_Error,"%Y/%m/%d"),"%Y/%m/%d")
| eval Duration_Since=round((_time-First_Error)/86400)
| eval Average_Activity = Active_Days/Duration_Since
| fillnull value=0 Average_Activity
| fieldformat First_Error=strftime(First_Error,"%Y/%m/%d")
Thank you! This did exactly what I wanted. If you don't mind me asking, how did you get a table to run without a table command?
@sharonmok table is a transforming command which should be used to present data in a proper sequence as expected. If you dont really need it(might have performed some other transforming command or retained only specific fields) you may not need to run a separate table command. This documentation will be a good to read stuff: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commandsbytype
The moment I ran a stats command in my query, I retained only the fields referred in that command and then built up the remaining fields as needed.
The reason why fieldformat has been used in the final pipe instead of eval for converting EPOCH time to String time, is that fieldformat retains the value as epoch and displays result as string time. Hence this field can be used further as epoch in subsequent pipes if required.
Hope these clarify things 🙂
Would you be able to help me out with one more thing? I'm trying to look at only business days, rather than all days. I've found this part of a query but I don't know where to put it in the original query to make it work.
| eval date_wday = tonumber(strftime(_time, "%w"))
| where date_wday>=1 AND date_wday<=5
Thanks!! That was very useful