Quickstart 1: Finding Expired GitHub GPG Keys
Click me to watch a recorded video version of this crul quickstart!
Introduction​
Let's start with a simple use cases. We want to use the GitHub API to determine which members of the Netflix organization have expired OR non expiring GPG keys that are still public.
Why is this an interesting use case? Firstly, it is a great demonstration of the capabilities of the crul language. But also, it's not a great practice to leave expired keys visible, or have keys that don't expire.
Be sure to check out our examples for additional queries!
The query in this quickstart will first list all public members in Netflix's GitHub organization, then retrieve each users GPG keys, then compare their expiration time to the current time, and return a list of user emails with expired keys.
Stage 1: Initial API request​
We will start with a single API request in our first stage, as simple as it gets!
api get https://api.github.com/orgs/netflix/members
Running the query above will make an HTTP GET request and return the response in a table. The response is a table containing all the public members of Netflix's GitHub organization.
Check out some of the available flags for the api command. Flags allow for finer grained control of our stage! For the api you can use flags to set custom headers, pass in credentials, and much more!
Stage 2: Expanding API requests​
For our next stage, we will take the result of our first request, and make an API request to a different endpoint for each row in order to retrieve that particular user's GPG keys.
Notice the --enrich flag, which tells the stage to enrich the previous row with the response.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys --enrich
After running this query we have a table containing all user GPG keys (including some empty rows), as well as the user information from our first API request.
Stage 3: Filtering empty results​
Some of the users did not have any keys associated with their account, so the API response for those requests was empty, let's filter out these empty results using the filter command, which allows to use an expression that will operate on each row and can compare column values with each other or with strings, numbers, etc.
In this case, the id column is null if the user has no keys, so we will filter out rows where the id value is null.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys --enrich
|| filter "id != null"
After running this query we have a table containing all the existing GPG keys, as well as the user information from our first API request.
Stage 4: Adding timestamps​
For our third stage, we will use the timestamp command to add the current time to our data set. We just transformed these api responses into time series data.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys
|| filter "id != null"
|| timestamp
Our data set now has several new timestamp fields named _timestamp.iso, _timestamp.epoch, _timestamp.month, etc.
Stage 5: Finding expired keys​
Let's run a logical operation on this data. After all, we're only interested in expired keys! We can again use the filter command to filter any keys whose expires_at field is older than the current time (_timestamp.iso) that we just added in the previous stage.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys
|| filter "id != null"
|| timestamp
|| filter "expires_at < _timestamp.iso"
Our data set now only contains keys that have expired, or have no expiration date.
Extra credit! You can combine both filter stages into just one stage!
|| filter "expires_at < _timestamp.iso and expires_at != null"
Stage 6: Removing columns​
This stage is really simple. We'll use the table command to keep only two of the columns and remove the rest.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys
|| filter "id != null"
|| timestamp
|| filter "expires_at < _timestamp.iso"
|| table emails.0.email expires_at
When this query was written, 7 members had expired keys, this may no longer be the case!
Stage 7: Renaming a column​
Our last stage is a simple rename. We'll use the rename to rename the emails.0.email column to just email.
api get https://api.github.com/orgs/netflix/members
|| api get https://api.github.com/users/$login$/gpg_keys
|| filter "id != null"
|| timestamp
|| filter "expires_at < _timestamp.iso"
|| table emails.0.email expires_at
|| rename emails.0.email email
Nice one!
You have run your first crul query! Pretty cool no? This only scratches the surface of the capabilities of crul. You can interact with web pages in a similar way, incorporate API keys and other credentials, send results to a data store with a single command, schedule queries, and more! If you would like a local copy of your query results, click on download as to save this as a CSV or JSON file.
If you haven't yet, try out the webpage focused quickstart!
To learn more about crul, check out the introduction and the getting started section next. Or check out our examples for additional queries!
Have fun and join us on slack or discord!
Contact us if you are interested in an Enterprise SaaS deployment of crul.