1: Need to find Average number of vpn users in weekday and weekend

[vigneshit]

Curerntly using the search :
1:: index=sec_vpn sourcetype="cisco:acs" action=success date_wday!=sunday OR date_wday!=saturday | dedup UserName
| stats dc(UserName) by date_wday

which is giving me number of users on per day basis .
output being :
date_wday dc(UserName)

friday 43996
monday 3055
thursday 19615
tuesday 8865
wednesday 12808

Need to find the average number of user on weekday.

2:: index=sec_vpn sourcetype="cisco:acs" action=success date_wday=sunday OR date_wday=saturday | dedup UserName
| stats dc(UserName) by date_wday
Need to find the average number of user on weekend.

[vigneshit]

Thanks it worked..
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:)
| gentimes start=-7
| eval random_user_count = random()%5000 + 4000
| eval date_wday = strftime(starttime, "%A")
| eval _time = starttime

[adonio · Accepted solution]

many ways to accomplish, try this:

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 

try this search anywhere to understand the idea:

| 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

hope it helps

[vigneshit]

Thanks alot it worked.
But I could not understand the working of 2nd search query
| gentimes start=-7
| eval random_user_count = random()%5000 + 4000
| eval date_wday = strftime(starttime, "%A")
| eval _time = starttime

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.

[vigneshit]

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?
1: Query output
weekend_or_not avg_count
weekend 13399.5
work day 40337.2

2:query
weekend_or_not avg_count
weekend 6130.5
workday 6662

[adonio]

yes ... the second search is generating random data to show the idea ... obviously it will be different form real data

[adonio]

the random function crates an integer between 0 and the number you specify after the % sign.
here i created a random number between 0 and 4999 and added 4000 to it
so in other words, a number between 4000 and 8999
read more here:
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/StatisticalFunctions#random.28.29