https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477127#M7548 <P>many ways to accomplish, try this:</P> <PRE><CODE>index=sec_vpn sourcetype="cisco:acs" action=success | stats dc(UserName) as unique_users by date_wday | eval weekend_or_not = if(date_wday="Saturday" OR date_wday="Sunday","weekend","work day") | stats avg(unique_users) as avg_count by weekend_or_not </CODE></PRE> <P>try this search anywhere to understand the idea:</P> <PRE><CODE>| gentimes start=-7 | eval random_user_count = random()%5000 + 4000 | eval date_wday = strftime(starttime, "%A") | eval _time = starttime | rename COMMENT as "above generates fake data, below is your solution" | table _time date_wday random_user_count | eval weekend_or_not = if(date_wday="Saturday" OR date_wday="Sunday","weekend","work day") | stats avg(random_user_count) as avg_count by weekend_or_not </CODE></PRE> <P>hope it helps</P> Sat, 14 Sep 2019 01:34:29 GMT adonio 2019-09-14T01:34:29Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477126#M7547 <P>Curerntly using the search :<BR /> 1:: index=sec_vpn sourcetype="cisco:acs" action=success date_wday!=sunday OR date_wday!=saturday | dedup UserName<BR /> | stats dc(UserName) by date_wday</P> <P>which is giving me number of users on per day basis .<BR /> output being :<BR /> date_wday dc(UserName)</P> <P>friday 43996<BR /> monday 3055<BR /> thursday 19615<BR /> tuesday 8865<BR /> wednesday 12808</P> <P>Need to find the average number of user on weekday.</P> <P>2:: index=sec_vpn sourcetype="cisco:acs" action=success date_wday=sunday OR date_wday=saturday | dedup UserName<BR /> | stats dc(UserName) by date_wday<BR /> Need to find the average number of user on weekend.</P> Wed, 30 Sep 2020 02:08:12 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477126#M7547 vigneshit 2020-09-30T02:08:12Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477127#M7548 <P>many ways to accomplish, try this:</P> <PRE><CODE>index=sec_vpn sourcetype="cisco:acs" action=success | stats dc(UserName) as unique_users by date_wday | eval weekend_or_not = if(date_wday="Saturday" OR date_wday="Sunday","weekend","work day") | stats avg(unique_users) as avg_count by weekend_or_not </CODE></PRE> <P>try this search anywhere to understand the idea:</P> <PRE><CODE>| gentimes start=-7 | eval random_user_count = random()%5000 + 4000 | eval date_wday = strftime(starttime, "%A") | eval _time = starttime | rename COMMENT as "above generates fake data, below is your solution" | table _time date_wday random_user_count | eval weekend_or_not = if(date_wday="Saturday" OR date_wday="Sunday","weekend","work day") | stats avg(random_user_count) as avg_count by weekend_or_not </CODE></PRE> <P>hope it helps</P> Sat, 14 Sep 2019 01:34:29 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477127#M7548 adonio 2019-09-14T01:34:29Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477128#M7549 <P>Thanks it worked..<BR /> But I could not understand the 2nd search query how you got the random function and how you decided the number 5000 and 4000 if any explanation would be there it would be great:)<BR /> | gentimes start=-7 <BR /> | eval random_user_count = random()%5000 + 4000<BR /> | eval date_wday = strftime(starttime, "%A")<BR /> | eval _time = starttime</P> Wed, 30 Sep 2020 02:12:15 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477128#M7549 vigneshit 2020-09-30T02:12:15Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477129#M7550 <P>Thanks alot it worked.<BR /> But I could not understand the working of 2nd search query <BR /> | gentimes start=-7 <BR /> | eval random_user_count = random()%5000 + 4000<BR /> | eval date_wday = strftime(starttime, "%A")<BR /> | eval _time = starttime</P> <P>How you selected the random function and the number 5000/4000? So if there is an explanation it would be great for my Understanding Thank YOU.</P> Wed, 30 Sep 2020 02:12:18 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477129#M7550 vigneshit 2020-09-30T02:12:18Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477130#M7551 <P>the random function crates an integer between 0 and the number you specify after the <CODE>%</CODE> sign.<BR /> here i created a random number between 0 and 4999 and added 4000 to it<BR /> so in other words, a number between 4000 and 8999<BR /> read more here:<BR /> <A href="https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/StatisticalFunctions#random.28.29">https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/StatisticalFunctions#random.28.29</A></P> Sat, 14 Sep 2019 19:56:18 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477130#M7551 adonio 2019-09-14T19:56:18Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477131#M7552 <P>Hi I just was cross verifying the 2 search's found that the results are not same its different for a same time duration. Do you have any insight on why is there a mismatch since both of them run the same query but in different manner?<BR /> 1: Query output<BR /> weekend_or_not avg_count<BR /> weekend 13399.5<BR /> work day 40337.2</P> <P>2:query<BR /> weekend_or_not avg_count<BR /> weekend 6130.5<BR /> workday 6662</P> Wed, 30 Sep 2020 02:12:21 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477131#M7552 vigneshit 2020-09-30T02:12:21Z https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477132#M7553 <P>yes ... the second search is generating random data to show the idea ... obviously it will be different form real data</P> Sat, 14 Sep 2019 21:16:46 GMT https://community.splunk.com/t5/Splunk-Enterprise-Security/1-Need-to-find-Average-number-of-vpn-users-in-weekday-and/m-p/477132#M7553 adonio 2019-09-14T21:16:46Z