Hello, I am currently tracking a total count of VPN Users. I want to track the total over a timechart to see when the high and low parts are through out the day. Below I have provided the search I am using to get the total VPN Count. Could you please assist on editing the search to show it in timechart and the total count by each hour.
| transaction fields=Cisco_ASA_user maxspan=12h30m connected=f startswith="*connection established*"
| search eventtype!=cisco_vpn_end
| dedup user
| stats count by Cisco_ASA_user | eventstats sum(count) as totalCount
| rename totalCount as "Total Count"
| table "Total Count"
| streamstats count(eval(searchmatch("connection established"))) as session by Cisco_ASA_user
| stats min(_time) as start max(_time) as end by session Cisco_ASA_user
| eval tmp=mvrange(start,end,3600)
| mvexpand tmp
| rename tmp as _time
| bin span=1h _time
| stats count as "Total Count" by _time
VPN log has only start and end of connection.
It is necessary to make a log for the period in the middle.
FYI, it is generally preferred to use stats instead of transaction like this:
| (your search)
| eval StartTime=if(match(_raw, "cisco_vpn_start"), _time, null())
| eval EndTime=if(match(_raw, "cisco_vpn_end"), _time, null())
| stats earliest(StartTime) as StartTime latest(EndTime) as EndTime by Cisco_ASA_user
Then you can search for full sessions (have both StartTime and EndTime) or abandoned (StartTime but missing and well overdue end times) or in progress (StartTime, no EndTime, but not long enough for timeout). You can do a lot with this info.