How do I edit my search to create a table of all currently logged on VPN users?

[reswob4]

So after reviewing a number of Q&As on this site, I created the following search to track currently logged on VPN users:

source="vpn.log" EventType="BROKER_USERLOGGEDIN" | eval n=strftime(_time, "%d-%m-%Y %H:%M:%S") | join UserDisplayName [ search source="vpn.log" EventType="BROKER_MACHINE_ALLOCATED" ] | search NOT [search source="vpn.log" EventType="BROKER_USERLOGGEDOUT" | table UserDisplayName] | iplocation ClientIpAddress | table n,UserDisplayName, MachineName, ClientIpAddress, Country

This gives me a very nice table of users currently logged on, when they logged on, machine name, remote IP and the country of the IP.

BUT there is a flaw. If, during the time parameters of the search, a user logs on, logs off, then logs back on, that user is not shown in the final table even though they are currently logged on.

How do I correct that problem? Ideally, I could use some sort of session ID, but our VPN doesn't provide that in a log.

Thanks

[woodcock]

Whenever I have a problem like this, I manufacture a SessionID and then use that like this:

source="vpn.log" | reverse | streamstats count(eval(EventType="BROKER_USERLOGGEDIN")) AS SessionID by UserDisplayName
| eventstats earliest(_time) AS LoginTime latest(_time) AS lastTime latest(EventType) AS lastEventType by SessionID UserDisplayName

This is your bas search.
Now if you do this:

| stats values(*) AS * BY SessionID UserDisplayName

You have statistics on all Sessions.
If you do this:

| reverse | dedup UserDisplayName

You will have the information for the last (current) session.

[somesoni2]

Try something like this

source="vpn.log" EventType="BROKER_USERLOGGEDIN" OR EventType="BROKER_USERLOGGEDOUT" OR EventType="BROKER_MACHINE_ALLOCATED" | eval n=if(source="BROKER_USERLOGGEDIN",strftime(_time, "%d-%m-%Y %H:%M:%S"),null())
| stats values(n) Values(MachineName) as MachineName values(ClientIpAddress) as ClientIpAddress values(EventType) as EventType values(Country) Country by UserDisplayName | where mvcount(EventType)>1 AND isnotnull(mvfind(EventType,"BROKER_MACHINE_ALLOCATED")) AND isnull(mvfind(EventType,"BROKER_USERLOGGEDOUT")) | table n,UserDisplayName, MachineName, ClientIpAddress, Country