Combining records with the same date

[Dabob]

I have a search that I am using for tracking VPN connection and I have found that I have users having multiple connections throughout a day and I would like to add the same day connections together.

Here is what I get from my search. 

user	Duration	Termination Reason	_time	LocationIP
jhicks	0h21m23s	User Requested	2021-03-25T14:43:48.000-0400	Waverly, United States
jhicks	0h31m16s	Idle Timeout	2021-03-25T14:15:42.000-0400	Waverly, United States
jhicks	0h09m49s	User Requested	2021-03-25T13:23:03.000-0400	Waverly, United States
jhicks	1h53m07s	Idle Timeout	2021-03-25T12:57:42.000-0400	, United States
jhicks	2h27m12s	Idle Timeout	2021-03-24T15:32:43.000-0400	, United States

and here is kind of what I am looking for. just adding the Duration fields together if they occur on the same day 

jhicks	2h55m35s	Idle Timeout	2021-03-25	, United States
jhicks	2h27m12s	Idle Timeout	2021-03-24	, United States

Here is what my search looks like 

Cisco_ASA_message_id=113019 NOT "AnyConnect-Parent"
| transaction user endswith="Duration:" keepevicted=true
| eval full_duration = duration_hour."h".duration_minute."m".duration_second."s"
| eval Start_time=strftime(_time,"%Y/%m/%d %H:%M:%S"), End_time=(strftime(_time + duration,"%Y/%m/%d %H:%M:%S")), Total_time=if(isnull(full_duration), Start_time." --> current session",Start_time." --> ".End_time)
| mvexpand src | iplocation src | eval LocationIP=City.", ".Country
| stats values(host) as host values(Total_time) as "Session Time" values(src) as "PublicIP" values(LocationIP) as LocationIP values(assigned_ip) as "Assigned IP" values(reason) as "Termination Reason" values(bytesMB) as bytesMB values(bytes_inMB) as bytes_inMB values(bytes_outMB) as bytes_outMB values(full_duration) as Duration by _time, user
| sort -_time
| search PublicIP=* |search user=$_user$
| table user Duration "Termination Reason" _time LocationIP "PublicIP" Duration

[richgalloway · Accepted solution]

Cisco_ASA_message_id=113019 NOT "AnyConnect-Parent"
| transaction user endswith="Duration:" keepevicted=true
| eval full_duration = duration_hour."h".duration_minute."m".duration_second."s"
| eval Start_time=strftime(_time,"%Y/%m/%d %H:%M:%S"), End_time=(strftime(_time + duration,"%Y/%m/%d %H:%M:%S")), Total_time=if(isnull(full_duration), Start_time." --> current session",Start_time." --> ".End_time)
| mvexpand src | iplocation src | eval LocationIP=City.", ".Country
| bin span=1d _time
| stats values(host) as host values(Total_time) as "Session Time" values(src) as "PublicIP" values(LocationIP) as LocationIP values(assigned_ip) as "Assigned IP" values(reason) as "Termination Reason" values(bytesMB) as bytesMB values(bytes_inMB) as bytes_inMB values(bytes_outMB) as bytes_outMB sum(full_duration) as Duration by _time, user
| sort - _time
| search PublicIP=* |search user=$_user$
| table user Duration "Termination Reason" _time LocationIP "PublicIP" Duration

I presume from the use of $_user$ that this is part of a dashboard.  If so, the search can be made more efficient by moving search user=$_user$ to before the first | or as early in the search as possible.

Try using the bin command to round _time to the beginning of the day.  Then stats can be used to add up the durations.