How to write a query that can identify user activity on VPN?

Hi Phil,

Thank you for the clarifications. I think I understand what you are willing to achieve.

I tried to create some sample queries to get a similar output as you expect. You can use these to modify them or add more details, as per your requirements.

DISCONNECTED

fields @timestamp, @message
| filter @message like /SIGTERM/
| parse @message "* SIGTERM[*] *" as Details, ExitDetails 
| stats count(*) as Count by "Disconnected" as Type, Details

Output:

CONNECTED

fields @timestamp, @message
| filter @message like /Peer Connection Initiated/
| parse @message "* with *" as Details, OtherDetails
| stats count(*) as Count by "Connected" as Type, Details

Output:

RESTARTED

fields @timestamp, @message
| filter @message like /Inactivity/
| parse @message "* (--ping-restart), restarting" as Details
| stats count(*) as Count by "Restarted" as Type, Details

Output:

Please let me know if this helps.

Regards,

Atul

Hi Phil,

You can write a custom query for your logs, which seems to be in a predefined format.

For reference, to count unique logged in users, write a query like this:

fields @timestamp, @message
| parse @message "Logged In: * * * * * * * Peer Connection Initiated with *" as loggedInDay, loggedInMon, loggedInDate, loggedInTime, loggedInYear, loggedInIp, loggedInUser, inetIP
| stats count_distinct(loggedInUser) as LoggedInClients

This gives output like:

#  |  LoggedInClients
1  |  3

Similarly, you can add parse and stats statements for Exited and Restarted Clients.

You can refer to this documentation to write regular expression to extract specific fields: https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-examples.html#CWL_QuerySyntax-examples-parse

[Query Syntax Reference] https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html

Hope this answers what you were looking for.

If you need further assistance, please let me know.

Thanks, Atul

Morning Atul,

Many thanks for you help on this so far. I think I may need to elaborate a little bit more, and I hope this makes sense.

Firstly, I have the following discovered fields in my Log: @log @logStream @message @timestamp

Secondly, each line of data I am try to count looks like this and each one has a specific string I am looking for: Exit lines look like this: Mon Sep 25 22:43:55 2023 username/123.123.123.123:53823 SIGTERM[soft,remote-exit] received, client-instance exiting

Connected lines look like this: Tue Sep 26 06:33:58 2023 123.123.123.123:56703 [username] Peer Connection Initiated with [AF_INET]123.123.123.123:56703

Restart lines look like this: Tue Sep 26 09:18:15 2023 username/123.123.123.123:56703 [username] Inactivity timeout (--ping-restart), restarting

I am using the following query, which I can adapt to look for specific parts of the @message lines, but I can't seem to piece them together: fields @timestamp, @message | filter @message like /.SIGTERM./ | parse @message "SIGTERM" as connectionType1 | stats count() as VPN_Disconnected by connectionType1

Ideally what I'd like to get is an output that looks similar to this (if possible): Details Connected Restarted Disconnected Tue Sep 26 06:33:58 2023 123.123.123.123:56703 [username] Peer Connection Initiated 1 Tue Sep 26 07:18:15 2023 username/123.123.123.123:56703 [username] Inactivity timeout 1 Tue Sep 26 09:43:55 2023 username/123.123.123.123:53823 SIGTERM 1

Apologies if this doesn't make sense...I'm pretty new to all this....I've just submitted this and the formatting is all a bit messed up. Many thanks Phil