Create custom - Ipsec change state history

[Terrence_M]

Hi all,

I'm wanting to create a report that details each time an IPsec tunnel has an up/down event. Ideally the out up would be close to:

date/time | tunnel id | up/down | duration

so when the logs have a change state it would look like:

time | 123456 | up | 1 day

time | 123456 | down | 1 hr

time | 123456 | up | 1 day

[hzhao_FTNT]

Pls try: 

log type: event

select distinct on (tunnelid) from_itime(max(e_time)) as e_time, tunnelid, (case when action='tunnel-down' then 'down' else 'up' end) as status, (case when min(s_time)=max(e_time) then max(max_duration) else max(max_duration)-min(min_duration) end) as duration from ###(select min(coalesce(dtime, 0)) as s_time, max(coalesce(dtime, 0)) as e_time, tunnelid, max(coalesce(duration,0)) as max_duration, action, min(coalesce(duration,0)) as min_duration from $log where $filter and subtype='vpn' and tunneltype like 'ipsec%' and not (tunnelip is null or (tunnelip='0.0.0.0' and logver is null)) and action in ('tunnel-stats', 'tunnel-down', 'tunnel-up') and tunnelid is not null group by tunnelid, action order by e_time desc)### t group by tunnelid, status order by tunnelid, e_time desc

regards,

hz

[Terrence_M]

Hz,

Thanks. This however, returned the ssl vpn tunnels, I need our static tunnels we have set up as interfaces, that are found in the system log - these will show the p1/p2 negotiates in the event log, is there a way to see their total down times like you showed for the other ones? I need these by VPN Tunnel not tunnelid so that we can easily track them in our systems by their names.

Thanks!