I'm building out a dashboard to identify VPN issues in our environment. The issue with the search below is that those failed VPN sessions do not have tunnel IDs, however successful VPN connections do. This results in 'no results found' for failed connections because there is no tunnel ID. If I 'fillnull tunnelid' the results in successful transactions having a null value generated in each set of results which shows an issue where one did not exist, while fixing the sessions that are failed. Any ideas? I'm not sure how to do a stats by a field that may or may not exist, and the fillnull creates misleading results. A session can have multiple tunnels throughout the day which is why I'm attempting to go down this road.
index=indexa sourcetype="sourcetype" SESSION_ID=$sessionid$ AND field9=* AND message=* AND message!=Session*
| dedup _raw
| rex field=message "(?<tunnelid>0x[0-9a-fA-F]{12})"
|fillnull tunnelid VALUE="No Tunnel"
| stats earliest(eval(field9=="Received User-Agent header")) as start latest(eval(like(field9,"%started%"))) as connect latest(eval(like(field9,"%closed%"))) as closed latest(eval(like(field9,"%unknown%"))) as unknownerrors latest(eval(like(field9,"%PPP%"))) as tunnels by SESSION_ID,tunnelid
| eval success=if(connect=1,1,0)
| eval closed=if(isnull(closed),0,1)
| eval errors=if(isnull(unknownerrors),0,1)
| eval Status=case(success=1 AND closed=0,"VPN Currently Connected",closed=1 AND errors=0,"VPN Connected Successfully & User Logged Off",errors=1,"VPN Connected Successfully & Encountered Issues",1=1,"Connection was not successful")
| rename SESSION_ID as "Session ID" tunnelid as "Tunnel ID"
| table "Session ID","Tunnel ID",Status
