Export Users with Specific Expiry & and Valid Certificate

[PaddyCP]

Hello,

Happy New Year. Hoping someone out there can help on this one. 

R81.10 Take 45 Management (Testing in Take 79 Replica)

Background to this is that we have a "Large" number of Internal Database VPN Accounts (+22,000) and they Authenticate via Certificate. We had ~12,000 expiring on 31/12/22 and gathered Login data from logs to focus on renewing only the accounts that had login activity in last 6 months. We could also discount ~7,000 as they didn't have a Certificate and therefore couldn't login so we are happy to let their account expire 3 days ago.

We ran a mgmt_cli update to extend the expiry on ~4,300 accounts but can see now over last 3 days that many accounts have been missed - and majority of business users return to work tomorrow morning after Christmas/New Years. These are possibly accounts missed via XLS reporting done on log exports etc (Unfortunately there doesn't appear to be a simple way to just report and tell us who logged in over last 6 months and who didn't so we used Excel via SmartLog exports etc).

We are now trying to run some mgmt_cli queries to identify users with an expiry of 31/12/22 and a "Valid" cert i.e. users capable of logging in that are expired - It appears in mgmt_cli that you can't use the "certificate" & "valid" filters in show users but can in show user (Plural Vs Singular). All we need is simply an export with username (Internal Database) expiry date = 31/12/22 & certificate=valid, but in some way that mgmt_cli doesn't fall over!

We started by trying to just export using show users all out to JSON etc but mgmt_cli falls over very quick with ~22,000 accounts and even then it is difficult to differentiate for users with certificates as we need to add the show-certificates "true" filter to show users with an actual cert, but that exports the whole certificate text; and that makes it totally unusable too. We have a case open with TAC on how we can possibly get this info but it is very difficult to properly try get engagement to achieve what we want.

We may need to just deal with accounts as they come up but the above export/query would help massively - we are just regularly looking at logs for action:"Failed Log In" and we can then see the Reason "user account expired".

Any help would be really appreciated. Thanks, Paddy

[the_rock]

Great question. Lets see if someone may have a good way to do this. Personally, Im not aware of any commands for it, but Im sure there has to be one.

Happy New Year!

[PhoneBoy]

The API will not reliably return that many results, regardless of the method querying it (including mgmt_cli).
You need to make multiple calls using limit/offset parameters to both constrain the results to a reasonable number and iterate over a chunk of results (e.g. 50 at a time).
This generally means writing a script.

[Blason_R]

22000 Internal users?? OMG - first time I am seeing so many users created internally 

Any way I guess have you tried with object.5.0.c file? I guess that might help with just linux tricks

[the_rock]

I get the logic in @PhoneBoy answer, makes sense. What @Blason_R said also makes sense, for sure, though I cant tell if "parsing" objects.5.0.c file would do you any good, but does not hurt, I guess.

[PaddyCP]

Thanks all for the replies.. Yes, you read it right.. 22k Internal Database accounts on Certs. There is a clear divide between AD and Firewall teams so we don't use any external user databases for Auth. We are evaluating DynamicID currently to layer that in with Certs too.

We basically updated the accounts by creating multiple manageable .sh files with the lines "mgmt_cli set user name <username> expiration-date 2025-12-31 -s sid.txt" - if there was some reverse way to dump out the info required line by line then that would be perfect.. i.e. use the "show user" with the expiry date and the certificate "valid" field.

Any help or scripting wizardry would be really appreciated as we are grappling this morning with lots of expired accounts.. manageable so far but to be able to get a definitive report would be great to help anticipate future users with issues.

Thanks again for replies so far. Paddy

[PhoneBoy · Accepted solution]

jq is your friend here:

mgmt_cli -s sid.txt --format json show users details-level full show-certificates true | jq -r '.objects[] | [.name, ."expiration-date"."iso-8601", .certificates[].status] | @csv'

This outputs the following in CSV format:

• username
• expiration date
• certificate valid field

Obviously you'll need to call this multiple times with different offset values for each call to get the entire user database.

Note that a user may have multiple certificates, thus you may see "valid" (or another status) multiple times on the same line.

[PaddyCP]

Thanks PhoneBoy.. this is really appreciated.

I have run this in our test box and get the following below (after about 30-40 seconds)

jq: error: Cannot iterate over null

At this point I just wanted to get an idea for what was involved on this and as such I didn't do anything as regards the "offset values" etc. as honestly I am not sure what that means.. in all my years I have never been a coder or scripter; Sorry.. 

Any additional guidance would be fantastic? Thanks again. Paddy

[Blason_R]

May be submit object.5.0.c via dm and let me see if I can help you.

[PaddyCP]

Thanks Blason_R, I honestly couldn't do that as there would be all sorts of Data Protection concerns for me and the customer. Even when TAC regularly request Mgmt Exports etc I have to be very specific on getting agreements. 

I do appreciate the willingness to help.. Thanks again. Paddy

[PhoneBoy · Accepted solution]

You will need to modify this command to use it as it will not work as-is.

Note that API generally requires you to perform the following actions:

• login
• "do stuff"
• publish (possibly followed by more "do stuff")
• logout

The command I provided is a "do stuff" command.
-s sid.txt refers to a session file created with mgmt_cli login > sid.txt
There are other ways to reference a session you've created (e.g. --session-id).
Given that you need to execute this command numerous times, it's best to create a proper session versus using the -r true shortcut that is often shown in mgmt_cli commands.
For more on the API: https://sc1.checkpoint.com/documents/latest/APIs/index.html#introduction~v1.9%20 

A given API call will only return a certain number of results, even if there are more results to be had.
Think of it this way: you don't read Moby Dick all at once, you read it a page at a time.
The API works on the same principle, only providing you a limited amount of data at a time.
Think of the offset parameter as how you specify the "page" you want to read.

You have 22,000+ users.
The API is only going to give you, say, 500 at a time (it could be less).
You can verify the precise number with:

mgmt_cli -r true --format json show users | jq '.to'

The API will also tell you exactly how many results it has.
It's easy enough to find out with:

mgmt_cli -r true --format json show users | jq '.total'

Note that in both cases, I am using -r true here, which effectively mimics the API flow I mentioned above and should only be used for simple operations.
For the task you're doing, proper session management (with explicit login/logout) is highly recommended for performance reasons.

Which means, to get the second page of results (i.e. the next 500 users), my command would look something like:

mgmt_cli -s sid.txt --format json show users details-level full show-certificates true offset 500 | jq -r '.objects[] | [.name, ."expiration-date"."iso-8601", .certificates[].status] | @csv'

The third page of results (i.e. the 500 users after that), the command would look something like:

mgmt_cli -s sid.txt --format json show users details-level full show-certificates true offset 1000 | jq -r '.objects[] | [.name, ."expiration-date"."iso-8601", .certificates[].status] | @csv'

And so on until you have all the users.
You could create a simple for loop in a bash script that calls this command numerous times.

Just for context, mgmt_cli is returning the results and the results are being piped to jq in order to provide only the data you're interested in and in a format that is easier to parse. 

[PaddyCP]

Thanks PhoneBoy.. this is brilliant. I am going to test through all this on the replica build to see how we can work it. This info will be invaluable going forward too as we continue managing all these users in months and years to come.

Will update back how all goes and hopefully this info will be helpful to others too.

Much appreciated.. Paddy