topic Re: Timechart a total count in Splunk Search

Timechart a total count

cooperjaram — Mon, 13 Apr 2020 18:22:01 GMT

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.

index=cisco_asa Cisco_ASA_user=*
| 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"

Thanks,

Cooper J

Re: Timechart a total count

jpolvino — Mon, 13 Apr 2020 19:29:39 GMT

Will this get you close?

index=cisco_asa Cisco_ASA_user=*
| transaction fields=Cisco_ASA_user maxspan=12h30m connected=f startswith="*connection established*" 
| search eventtype!=cisco_vpn_end
| dedup user
| timechart span=1h count(user) AS UserCount

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.

Re: Timechart a total count

richgalloway — Mon, 13 Apr 2020 19:34:55 GMT

See if this gets you the desired results.

index=cisco_asa Cisco_ASA_user=*
| transaction fields=Cisco_ASA_user maxspan=12h30m connected=f startswith="*connection established*" 
| search eventtype!=cisco_vpn_end
| dedup user
| stats count by _time
| timechart span=1h sum(count) as totalCount
| rename totalCount as "Total Count"

Re: Timechart a total count

to4kawa — Mon, 13 Apr 2020 22:28:54 GMT

index=cisco_asa Cisco_ASA_user=*
| reverse
| 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.