Remote VPN users report

[Moe_89]

Hello,

Is there a way to export a list of Remote VPN users in the local MGMT database which includes last login time etc. ?

Something similar to fwm dbexport.

[PhoneBoy]

fw tab commands that list a specific table only list a few entries by default.
When you add -u, it shows all entries.

[Paul_Warnagiris]

Dood! Rock star status! You just solved a HUGE problem of mine. Thank you so much.

[PhoneBoy · Accepted solution]

For those following along at home, here's what the command outputs:

[Expert@sbt-check-point-gw-r8040:0]# fw tab -t userc_users -f -u
  Using cptfmt
Formatting table's data - this might take a while...

localhost:
Date: Mar 19, 2020
3:30:34 5 N/A N/A 192.168.101.253 > N/A LogId: <max_null>; ContextNum: <max_null>; OriginSicName: <max_null>; : (+)====================================(+); Table_Name: userc_users; : (+); Attributes: dynamic, id 144, attributes: keep, sync, kbuf 1, local sync, expires 900, , hashsize 16384, limit 10000; LastUpdateTime: 19Mar2020 3:30:34; ProductName: VPN-1 & FireWall-1; ProductFamily: Network;

3:30:34 5 N/A N/A 192.168.101.253 > N/A LogId: <max_null>; ContextNum: <max_null>; OriginSicName: <max_null>; SRAddress: 172.16.10.1; Schema: SSL(8); UserName: Bob; UserDN: CN=Bob,CN=Users,DC=ir,DC=local; MyRange:First: ; Last: 255.255.255.255; PeerRange:First: 172.16.10.1; PeerLast: 172.16.10.1; ConnectTime: 1584581121; RouteTraffic: 0; Expires: 900/900; LastUpdateTime: 19Mar2020 3:30:34; ProductName: VPN-1 & FireWall-1; ProductFamily: Network;

The ConnectTime is when the users connected, but it's listed as seconds since the Epoch (Jan 1 1970 @ 00:00:00 GMT).
There are ways to convert this to a usable date/time, I'm sure.

[Saagarg007]

Hi PhoneBoy,

Is there a way to get a count of number of users who are connected over VPN by some command? 

[Paul_Warnagiris]

This is a good way to see the number of office mode IPs that are used:

fw tab -t om_assigned_ips -s

Depending on which version you are running and if you have SmartEvent you can do it relatively easy from there as well.

Also you can go to SmartVeiwMonitor (Tunnel & User Monitoring) as in the attachment.  But its not reliable because it counts users more than once.

You can also use this command to create a file with all of the users 

fw tab -t userc_users -f -u >userc_users

Then with a little bit of scripting figure out how many users are currently connected or how long they have been connected, etc., etc.  I think depending on your requirements the om_assigned command is good enough if you are just trying to get an idea generally of how many have connected in the recent past.  If you need exact numbers at a certain time the userc_users is the way to go, but it will require a bit of extra scripting.

[PhoneBoy]

fw tab -t userc_users -s will give you a reasonably accurate number.
I say that because if the client doesn't properly disconnect (log off), they won't age out of that table for at least 15 minutes.

[Expert@oscar:0]# fw tab -t userc_users -s
HOST                  NAME                                ID #VALS #PEAK #SLINKS
localhost             userc_users                        143     0     0       0

Obviously my gateway doesn't have anyone connected to it at the moment. 😬
But you can see where it would provide the number currently connected (#VALS) and high water mark since last boot/cprestart (#PEAK).

Note this doesn't cover anyone connected via the MAB Portal.

[Saagarg007]

Hi I did ran these 2 commands and the numbers are different, 

fw tab -t userc_users -s ------> (Gives 410)

fw tab -t om_assigned_ips -s -------> (Gives 397)

so what we need exactly is total number of uses who've logged in through VPN and worked from home. The data should only unique users total info.

Also checked in smartview monitor the data over there is totally different attached a snip .

so don't know exactly which info is correct for business as they just want to see how many users are working from home over VPN. Hope you're getting my query. 

Thanks again for your time.

[PhoneBoy]

userc_users is the "most accurate" number but I believe it also includes multiple logins from the same user (which you can prevent, if you want).
There are ways to be connected via VPN without getting an Office Mode IP (e.g. SecuRemote).
Like I said previously, the userc_users is the most accurate measure we can provide.

[Paul_Warnagiris]

userc_users is great.  Gets me everything I need.  What is the switch to not count multiple (same) users?  Or are you saying you can prevent multiple users from simultaneously logging in?  I know how to do that.  If there is a switch not to count the same user twice though I would like to know what it is.  Thanks for the clarification.

[PhoneBoy]

Unless you pipe the output through various Unix tools, there isn't a switch to fw tab to eliminate duplicate users.

[hrath]

First of all, thanks for this awesome command : #fw tab -t om_assigned_ips -f -u

Is there a way to change the format of the ConnectTime without using the Epoch converter ? 

This output gives a list of 200+ users, just looking for a readable format.

3:55:01 5 N/A  N/A     > N/A  LogId: <max_null>; ContextNum: <max_null>; OriginSicName: <max_null>; SRAddress: 10.4.177.231; Schema: IKE(3); UserName: xxxxxx@abc.com; UserDN: CN=xxxxxx,OU=Employees,OU=All Users,OU=ABCD,DC=ABCD,DC=ABCD,DC=ABCD,DC=co,DC=nz; MyRange:First: ; Last: 255.255.255.255; PeerRange:First: 10.4.177.231; PeerLast: 10.4.177.231; ConnectTime: 1585167093; RouteTraffic: 0; Expires: 898/900; LastUpdateTime: 27Mar2020  3:55:01; ProductName: VPN-1 & FireWall-1; ProductFamily: Network;

[Paul_Warnagiris]

The way I did it to find the time with a script was date -d @1585167093

You can also do date +%s to get the current epoch time and then do the math.  

To make it more readable I did the following and then sent the output to where it needed to go where they updated the epoch time and displayed it on a webpage.  You may have to play around with the print $.  At one customer it was 8 and 14, another it was 6 and 12.  You just have to run it to see what happens and then adjust.

[Expert@XXX-FW01:0]# fw tab -t userc_users -f -u > userc_users
Using cptfmt
Formatting table's data - this might take a while...

[Expert@Pit-FW01:0]# grep UserName userc_users | awk 'BEGIN{FS=";"}{print $8,$14}'
UserName: aaaaaaa ConnectTime: 1585309850
UserName: ccccccc ConnectTime: 1585311541
UserName: ccccccc ConnectTime: 1585269476
UserName: ddddddd ConnectTime: 1585308342
UserName: eeeeeeee ConnectTime: 1585306535
UserName: fffffff ConnectTime: 1585308529
UserName: ggggggg ConnectTime: 1585310202
UserName: hhhhhhh ConnectTime: 1585302998
UserName: iiiiiii ConnectTime: 1585306864
UserName: jjjjjjj ConnectTime: 1585308625
UserName: kkkkkkk ConnectTime: 1585310378
UserName: lllllll ConnectTime: 1585304599
UserName: mmmmmmm ConnectTime: 1585307114
UserName: nnnnnnn ConnectTime: 1585307178
UserName: ooooooo ConnectTime: 1585310564

[Buhler]

Thanks for the share. Will look at it further ahead.

Either way, I'm managing to get what I want from the cp log exporter logs, and running it through a SIEM.

It's kinda hard to understand things between a thousand logs, but I'm making progress. and soon I believe I will have a nice and clean dashboard. Either way will look at RAsession_util. Thanks

[PhoneBoy]

In general, you'd be looking at historical data, which should all be in the logs...and should be possible to correlate in a SIEM.
Note that with VPN users, you may not have a complete picture of how long a user is connected in cases where the client doesn't inform the gateway.

Note that none of our reporting/logs are currently available via REST API, but I presume SmartEvent should be able to correlate SOME of that data.

[PhoneBoy · Accepted solution]

You might also want to look at this custom report put together by one of our SEs: https://community.checkpoint.com/t5/Visibility-Analytics/SmartView-VPN-Client-enhanced-view/m-p/7887...

[Paul_Warnagiris]

I'm  back here again because I have another customer asking for VPN statistics.

I'm running this report and everything is measured in logs.  So two comments.  Why do I care about the number of logs?  Shouldn't I care about the number of actual failed login attempts?  It seems to me that we cannot provide this information and this is a huge shortcoming?  I can have all of my customers request this RFE immediately if it helps, but even this custom report -- while the effort is good -- the data is not.

Here is the login activity by failed and successful and while the graph looks pretty cool, the data is not:

This customer has no more than 50 users that log in via VPN and its probably closer to 30.  However in this output we see 25k logins in one day.  Clear this is an identity awareness chart, but the report is entitled "remote access."  This is a fail IMHO.  Then the "Authentication Methods" widget shows a nice graph, but once again the data is useless.  Top login options shows VPN with 16 "logs."  What do I do with that data?  Seriously, what should I do with it? The Used Clients widget is nice if you were talking to someone that didn't understand VPN, but what does that get me from a technical perspective? The only widget that is worth its time is the VPN usage and that's showing gigs over days and days.  I'm sure that could be tweaked, but it actually provides usable data.

The bottom line is how can I help you help me with this data?  Is this anything that anyone with any pull is looking at?  Do they know the serious shortcomings in this reporting?  Does Check Point just not care?  Literally if you want 50 customers to request the same RFE I can do it.  But will it help?  Does anyone care?  Clearly the need is there as was just demonstrated by the pandemic and threads such as this one.  I know there are ways to get the data we need, but none of it is in a nice, neat, canned report.  Its not even able to be created in a custom report as is evidenced by this custom report.

You know I'm as big of a Check Point fan as they come.  I'm not saying this to be negative.  But how can we improve this. Its been years and years and years and every Check Point SE I talk to says....<"frowny face -- I know, its terrible">.

Let me know if there is anything I can do from a partner perspective.

Thanks,
Paul

[PhoneBoy]

I assume with a failed login, each log entry would be, in fact, a failed attempt.
That's not the case with "Log In" logs clearly.
That said, if you're interested in number of users who are using the VPN, you might look at this report instead: https://community.checkpoint.com/t5/Logging-and-Reporting/Remote-Access-VPN-Report/m-p/81177#M16398

[HeikoAnkenbrand]

In the past days I have been working on a CLI script that can display all Secure Client license information centrally. This script creates a new command on the management server to read the Secure Client licenses. It displays all Secure Client licenses in total (sum). Furthermore, it can read out the currently used licenses on the gateway. If a connection to the gateway can be established, the following values are read out: Currently used Secure Client licenses and the maximum used Secure Client licenses.

If you execute the script via "copy and past" on the management server, a new CLI command "sclic" is created. Afterwards you can use this command to display all licenses in an overview. Please note that the execution of the new command may take a few seconds. This is a normal behaviour.

More read here: R80.x - Secure Client License Counter Overview - replaces "dtps lic" 

Here an example:
# sclic 10.0.0.1

Now all license parameters for Secure Client are displayed: