Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.

image

Okta Plugin for Steampipe

Use SQL to query infrastructure including users, groups, applications and more from Okta.

Quick start

Install the plugin with Steampipe:

steampipe plugin install okta

Run a query:

select login, id, email, created from okta_user;

Developing

Prerequisites:

Clone:

git clone https://github.com/turbot/steampipe-plugin-okta.git
cd steampipe-plugin-okta

Build, which automatically installs the new version to your ~/.steampipe/plugins directory:

make

Configure the plugin:

cp config/* ~/.steampipe/config
vi ~/.steampipe/config/okta.spc

Try it!

steampipe query
> .inspect okta

Further reading:

Contributing

Please see the contribution guidelines and our code of conduct. All contributions are subject to the Apache 2.0 open source license.

help wanted issues:

Owner
Turbot
Get cloud work done with Turbot — Creators of https://turbot.com/v5 and https://steampipe.io
Turbot
https://github.com/turbot/steampipe-plugin-okta https://hub.steampipe.io/plugins/turbot/okta
Comments
  • High API usage

    High API usage

    Describe the bug We are hitting rate limiting and triggering alerts when doing "straight forward" lookups with the new okta_app_assigned_group table. This may be happening with other tables too but I have only verified it with this tabled based on our testing.

    I set logging to trace and change the okta url to localhost to see what it was doing and it looks like it is getting all apps which would be costly with the size of our org and I am passing an app_id qualifier.

    > 2022-01-20T09:48:00.939-0800 [TRACE] steampipe: Got Session with PID: 86911
2022-01-20T09:48:00.939-0800 [TRACE] steampipe: Session with PID: 86911 - returning
> 
> select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<redacted>' limit 1;
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: resolveQuery select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<redacted>' limit 1; args <empty>
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: Got Session with PID: 86911
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: Session with PID: 86911 - returning
Error: Get "https://localhost/api/v1/apps": dial tcp [::1]:443: connect: connection refused (SQLSTATE HV000)
>

    I cant find where the list api (https://developer.okta.com/docs/reference/api/apps/#list-applications) call is being done (is it related to the parent hydrate code?)

    I believe for this table if app_id is specified it should just make a paginated call to api/v1/apps/<app_id>/groups for each app_id specified

    also https://github.com/turbot/steampipe-plugin-okta/blob/80bfd027a163b4b241b0e24cd6af220c752c74ea/okta/table_okta_app_assigned_group.go#L117

    Should that be app_id and not user_id? If you could explain how user_id is related that would be appreciated. I am trying to get up to speed so I can make some PRs.

    Steampipe version (steampipe -v) Example: v0.11.2

    Plugin version (steampipe plugin list) Example: v0.4.0

    To reproduce run a query with on okta_app_assigned_group with app_id qualifier

    select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<someappid>' limit 1;

    Expected behavior Not listing ALL apps before getting results

    Additional context none

  • profile data not available in okta_application table

    profile data not available in okta_application table

    Describe the bug With the removal of the 'assigned_groups' field in the 'okta_application' table the profile data we used to have access to is no longer available.

    Steampipe version (steampipe -v) Example: v0.11.2

    Plugin version (steampipe plugin list) Example: v0.3.0

    To reproduce With the 0.2 version of the plugin we were able to run:

    Begin transaction;
create temp table if not exists t1 on commit drop as select jsonb_array_elements(assigned_groups)#>>'{id}' as groupid, jsonb_array_elements(assigned_groups)#>>'{profile,samlRoles}' as roles from okta.okta_application where filter = 'group.id eq "xxxxxxxxxxxx"' AND label = 'foo';
create temp table if not exists t2 on commit drop as select name,id from okta.okta_group where id in (select groupid from t1);
select t2.name,t1.groupid, jsonb_array_elements_text(t1.roles::jsonb) as samlRole from t1,t2 where t1.groupid = t2.id;
commit;

    which would give us the first 20 group assignments (see issue #47) for an app with the associated samlRoles assigned to each group Note: with issue #40 fixed we dont have to use the "where filter" clause anymore (we can just do "where id = ....")

    Expected behavior Some way yo access the profile.SamlRoles data in the okta_application table or okta_app_assigned_group (or maybe a whole other table)

    Additional context Add any other context about the problem here.

  • Add table okta_domain

    Add table okta_domain

    References https://developer.okta.com/docs/reference/api/domains/#get-all-domains

    branch having some initial code - https://github.com/turbot/steampipe-plugin-okta/compare/new-table

  • assigned_groups field in okta_application is limited to the first 20 groups

    assigned_groups field in okta_application is limited to the first 20 groups

    Describe the bug When getting the 'assigned_groups' field of from okta_application table, the data is limited to the first 20 groups and the api calls are not pagenating. Also there is no way to pass the query param 'limit' (ie: “/api/v1/apps/${APPID}/groups?limit=200”) to work around the current default pagenation size of 20 (to reduce api calls).

    https://developer.okta.com/docs/reference/api/apps/#list-groups-assigned-to-application

    Steampipe version (steampipe -v) v0.10.0

    Plugin version (steampipe plugin list) v0.2.0

    To reproduce have an okta application with more than 20 groups assigned to it and query its assigned groups

    select jsonb_pretty(assigned_groups) as groups from okta.okta_application where filter = 'group.id eq "xxxxxxxxx"' AND label = 'foo'

    query is formatted with the where and condition due to issue #40

    Expected behavior all groups to be returned in the 'assigned_groups' field

    Additional context Use case is to get all group assignments for an application and then convert the returned group ids to the group name for auditing/reporting.

    I believe something similar to https://github.com/turbot/steampipe-plugin-okta/blob/4a2daf2a14a255206c744a666aa0e7bf41e37fd2/okta/table_okta_group.go#L183-L200 will need to be done around https://github.com/turbot/steampipe-plugin-okta/blob/4a2daf2a14a255206c744a666aa0e7bf41e37fd2/okta/table_okta_application.go#L207

    but not sure (not too familiar with this code base still)

    equiv functionality we are trying to reproductince in steampipe is

    !/bin/bash
set -e
APPID='SOME_APP_ID'
OKTA_API_TOKEN='SOME_API_TOKEN'

function okta_api() {
  API="$1"
curl -s -X GET \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-H "Authorization: SSWS ${OKTA_API_TOKEN}" \
"https://foo.okta.com/${API}"
}

#get application group assignments
okta_api "api/v1/apps/${APPID}/groups?limit=200" > blob.json

#convert groupids to names
for I in $(jq -r '.[].id' blob.json); do
  NAME=$(okta_api "api/v1/groups/${I}" | jq -r .profile.name | tr ' ' '_')
  jq -r '.[] | select(.id == "'${I}'") | .profile.samlRoles[]' blob.json > ${NAME}.txt
done
  • Fix GET API call for okta_app_assigned_group.

    Fix GET API call for okta_app_assigned_group.

    Example query results

    Results 
    > select * from okta_app_assigned_group where id = '00g3e9b6esCUiHSkK5d7' and app_id = '0oa1mf9l3dw26foa25d7';
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
| id                   | app_id               | last_updated              | priority | links                                                                                                                
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
| 00g3e9b6esCUiHSkK5d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-17T13:21:17+05:30 | 1        | {"app":{"href":"https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7"},"group":{"href":"https://dev-2131499
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
> select * from okta_app_assigned_group where app_id = '0oa1mf9l3dw26foa25d7';
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
| id                   | app_id               | last_updated              | priority | links                                                                                                                
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
| 00g3e9b6esCUiHSkK5d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-17T13:21:17+05:30 | 1        | {"app":{"href":"https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7"},"group":{"href":"https://dev-2131499
| 00g33kzj6xOZvlBUQ5d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-24T11:16:23+05:30 | 2        | {"app":{"href":"https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7"},"group":{"href":"https://dev-2131499
| 00g1kchdmqwsZEGG65d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-24T11:16:25+05:30 | 3        | {"app":{"href":"https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7"},"group":{"href":"https://dev-2131499
+----------------------+----------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------
> select
  id as group_id,
  app_id,
  last_updated,
  priority
from
  okta_app_assigned_group;
+----------------------+----------------------+---------------------------+----------+
| group_id             | app_id               | last_updated              | priority |
+----------------------+----------------------+---------------------------+----------+
| 00g1kchdmqwsZEGG65d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-24T11:16:25+05:30 | 3        |
| 00g33kzj6xOZvlBUQ5d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-24T11:16:23+05:30 | 2        |
| 00g3e9b6esCUiHSkK5d7 | 0oa1mf9l3dw26foa25d7 | 2022-01-17T13:21:17+05:30 | 1        |
+----------------------+----------------------+---------------------------+----------+
> select
  grp.name as name,
  grp.description as description,
  jsonb_pretty(grp.group_members) as group_members
from
  okta_group grp
full outer join okta_app_assigned_group ag on grp.id = ag.id
where
  grp.id is null or ag.id is null;
+------+-------------+---------------+
| name | description | group_members |
+------+-------------+---------------+
+------+-------------+---------------+
> select
  app.name as app_name,
  app.id as app_id,
  app.created as app_created,
  app.status as app_status,
  ag.id as group_id,
  grp.name as group_name,
  grp.description as group_description,
  jsonb_pretty(grp.group_members) as group_members
from
  okta_application app
left join okta_app_assigned_group ag on app.id = ag.app_id
left join okta_group grp on ag.id = grp.id;
+---------------------+----------------------+---------------------------+------------+----------------------+---------------+--------------------------------+----------------------------------------+
| app_name            | app_id               | app_created               | app_status | group_id             | group_name    | group_description              | group_members                          |
+---------------------+----------------------+---------------------------+------------+----------------------+---------------+--------------------------------+----------------------------------------+
| okta_enduser        | 0oa1kchdrfcXTbEzV5d7 | 2021-08-26T09:56:36+05:30 | ACTIVE     | <null>               | <null>        | <null>                         | <null>                                 |
| okta_browser_plugin | 0oa1kcigd9Kob07k05d7 | 2021-08-26T09:56:36+05:30 | ACTIVE     | <null>               | <null>        | <null>                         | <null>                                 |
| oidc_client         | 0oa1mf49z9iZurdzA5d7 | 2021-08-31T18:01:44+05:30 | ACTIVE     | <null>               | <null>        | <null>                         | <null>                                 |
| saasure             | 0oa1kchdmerpZir9y5d7 | 2021-08-26T09:56:28+05:30 | ACTIVE     | <null>               | <null>        | <null>                         | <null>                                 |
| oidc_client         | 0oa1mf9l3dw26foa25d7 | 2021-08-31T18:16:30+05:30 | ACTIVE     | 00g1kchdmqwsZEGG65d7 | Everyone      | All users in your organization | [                                      |
|                     |                      |                           |            |                      |               |                                |     {                                  |
|                     |                      |                           |            |                      |               |                                |         "id": "00u1kcigdvWtR96HY5d7",  |
|                     |                      |                           |            |                      |               |                                |         "email": "[email protected]",   |
|                     |                      |                           |            |                      |               |                                |         "login": "[email protected]"    |
|                     |                      |                           |            |                      |               |                                |     },                                 |
|                     |                      |                           |            |                      |               |                                |     {                                  |
|                     |                      |                           |            |                      |               |                                |         "id": "00u33m8oarePFyaTm5d7",  |
|                     |                      |                           |            |                      |               |                                |         "email": "[email protected]", |
|                     |                      |                           |            |                      |               |                                |         "login": "[email protected]"  |
|                     |                      |                           |            |                      |               |                                |     },                                 |
|                     |                      |                           |            |                      |               |                                |     {                                  |
|                     |                      |                           |            |                      |               |                                |         "id": "00u3e9oo5e7hR6X815d7",  |
|                     |                      |                           |            |                      |               |                                |         "email": "[email protected]", |
|                     |                      |                           |            |                      |               |                                |         "login": "[email protected]"  |
|                     |                      |                           |            |                      |               |                                |     }                                  |
|                     |                      |                           |            |                      |               |                                | ]                                      |
| oidc_client         | 0oa1mf9l3dw26foa25d7 | 2021-08-31T18:16:30+05:30 | ACTIVE     | 00g33kzj6xOZvlBUQ5d7 | TestGroup     | This is a test group           | [                                      |
|                     |                      |                           |            |                      |               |                                |     {                                  |
|                     |                      |                           |            |                      |               |                                |         "id": "00u33m8oarePFyaTm5d7",  |
|                     |                      |                           |            |                      |               |                                |         "email": "[email protected]", |
|                     |                      |                           |            |                      |               |                                |         "login": "[email protected]"  |
|                     |                      |                           |            |                      |               |                                |     }                                  |
|                     |                      |                           |            |                      |               |                                | ]                                      |
| oidc_client         | 0oa1mf9l3dw26foa25d7 | 2021-08-31T18:16:30+05:30 | ACTIVE     | 00g3e9b6esCUiHSkK5d7 | Another Group | This is another group          | [                                      |
|                     |                      |                           |            |                      |               |                                |     {                                  |
|                     |                      |                           |            |                      |               |                                |         "id": "00u33m8oarePFyaTm5d7",  |
|                     |                      |                           |            |                      |               |                                |         "email": "[email protected]", |
|                     |                      |                           |            |                      |               |                                |         "login": "[email protected]"  |
|                     |                      |                           |            |                      |               |                                |     }                                  |
|                     |                      |                           |            |                      |               |                                | ]                                      |
+---------------------+----------------------+---------------------------+------------+----------------------+---------------+--------------------------------+----------------------------------------+
  • Update tables for better use of API filters, context cancellation in list calls and page limiting for limit clause in query closes #65

    Update tables for better use of API filters, context cancellation in list calls and page limiting for limit clause in query closes #65

    Changes made for the tables

    1. okta_app_assigned_group
    2. okta_app_assigned_user
    3. okta_application
    4. okta_auth_server
    5. okta_factor
    6. okta_group
    7. okta_idp_discovery_policy

    Example query results

    Results 
    > select * from okta_app_assigned_user
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| id                   | user_name         | app_id               | created                   | status | email             | external_id | first_name | last_name | last_sync | last_updated              
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| 00u1kcfw5yDiVG80P5d7 | <null>            | 0oa1kcfvy3rRLMLYC5d7 | 2021-08-26T09:52:55+05:30 | ACTIVE | <null>            |             | <null>     | <null>    | <null>    | 2021-08-26T09:52:55+05:30 
| 00u1kcfw5yDiVG80P5d7 | [email protected] | 0oa1mds05a7V0Gkbb5d7 | 2022-02-11T13:36:29+05:30 | ACTIVE | [email protected] |             | Partha     | Behera    | <null>    | 2022-02-11T13:36:29+05:30 
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------

> select * from okta_app_assigned_user where app_id = '0oa1mds05a7V0Gkbb5d7';
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| id                   | user_name         | app_id               | created                   | status | email             | external_id | first_name | last_name | last_sync | last_updated              
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| 00u1kcfw5yDiVG80P5d7 | [email protected] | 0oa1mds05a7V0Gkbb5d7 | 2022-02-11T13:36:29+05:30 | ACTIVE | [email protected] |             | Partha     | Behera    | <null>    | 2022-02-11T13:36:29+05:30 
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
> select * from okta_app_assigned_user where user_naem = '[email protected]'
Error: column "user_naem" does not exist (SQLSTATE 42703)
> select * from okta_app_assigned_user where user_name = '[email protected]'
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| id                   | user_name         | app_id               | created                   | status | email             | external_id | first_name | last_name | last_sync | last_updated              
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| 00u1kcfw5yDiVG80P5d7 | [email protected] | 0oa1mds05a7V0Gkbb5d7 | 2022-02-11T13:36:29+05:30 | ACTIVE | [email protected] |             | Partha     | Behera    | <null>    | 2022-02-11T13:36:29+05:30 
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
> select * from okta_app_assigned_user where first_name = 'Partha'
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| id                   | user_name         | app_id               | created                   | status | email             | external_id | first_name | last_name | last_sync | last_updated              
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| 00u1kcfw5yDiVG80P5d7 | [email protected] | 0oa1mds05a7V0Gkbb5d7 | 2022-02-11T13:36:29+05:30 | ACTIVE | [email protected] |             | Partha     | Behera    | <null>    | 2022-02-11T13:36:29+05:30 
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------

> select * from okta_app_assigned_user where last_name = 'Behera'
+----+-----------+--------+---------+--------+-------+-------------+------------+-----------+-----------+--------------+------------------+-------+----------------+------------+-------+---------+-------
| id | user_name | app_id | created | status | email | external_id | first_name | last_name | last_sync | last_updated | password_changed | scope | status_changed | sync_state | links | profile | title 
+----+-----------+--------+---------+--------+-------+-------------+------------+-----------+-----------+--------------+------------------+-------+----------------+------------+-------+---------+-------
+----+-----------+--------+---------+--------+-------+-------------+------------+-----------+-----------+--------------+------------------+-------+----------------+------------+-------+---------+-------
> select * from okta_app_assigned_user where email = '[email protected]'
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| id                   | user_name         | app_id               | created                   | status | email             | external_id | first_name | last_name | last_sync | last_updated              
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------
| 00u1kcfw5yDiVG80P5d7 | [email protected] | 0oa1mds05a7V0Gkbb5d7 | 2022-02-11T13:36:29+05:30 | ACTIVE | [email protected] |             | Partha     | Behera    | <null>    | 2022-02-11T13:36:29+05:30 
+----------------------+-------------------+----------------------+---------------------------+--------+-------------------+-------------+------------+-----------+-----------+---------------------------

> select * from okta_application
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
| name                | id                   | label               | created                   | filter | last_updated              | status | sign_on_mode   | settings                                  
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
| oidc_client         | 0oa1me6urfbwt5Mjw5d7 | Service Client Name | 2021-08-31T15:02:50+05:30 | <null> | 2021-08-31T15:02:50+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| okta_browser_plugin | 0oa1kcfw4jCRniSph5d7 | Okta Browser Plugin | 2021-08-26T09:52:54+05:30 | <null> | 2021-08-26T09:52:54+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| saasure             | 0oa1kcfvy3rRLMLYC5d7 | Okta Admin Console  | 2021-08-26T09:52:48+05:30 | <null> | 2021-08-26T09:52:51+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| okta_enduser        | 0oa1kcfw58aNTCvke5d7 | Okta Dashboard      | 2021-08-26T09:52:55+05:30 | <null> | 2021-08-26T09:52:55+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| oidc_client         | 0oa1mds05a7V0Gkbb5d7 | My API Services App | 2021-08-31T14:02:28+05:30 | <null> | 2021-08-31T14:02:28+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
> select * from okta_application where name = 'oidc_client'
+-------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+---------------------------------------------------
| name        | id                   | label               | created                   | filter | last_updated              | status | sign_on_mode   | settings                                          
+-------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+---------------------------------------------------
| oidc_client | 0oa1me6urfbwt5Mjw5d7 | Service Client Name | 2021-08-31T15:02:50+05:30 | <null> | 2021-08-31T15:02:50+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network":{"conn
| oidc_client | 0oa1mds05a7V0Gkbb5d7 | My API Services App | 2021-08-31T14:02:28+05:30 | <null> | 2021-08-31T14:02:28+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network":{"conn
+-------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+---------------------------------------------------
> select * from okta_application where status = 'ACTIVE'
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
| name                | id                   | label               | created                   | filter | last_updated              | status | sign_on_mode   | settings                                  
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
| saasure             | 0oa1kcfvy3rRLMLYC5d7 | Okta Admin Console  | 2021-08-26T09:52:48+05:30 | <null> | 2021-08-26T09:52:51+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| oidc_client         | 0oa1mds05a7V0Gkbb5d7 | My API Services App | 2021-08-31T14:02:28+05:30 | <null> | 2021-08-31T14:02:28+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| okta_browser_plugin | 0oa1kcfw4jCRniSph5d7 | Okta Browser Plugin | 2021-08-26T09:52:54+05:30 | <null> | 2021-08-26T09:52:54+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| okta_enduser        | 0oa1kcfw58aNTCvke5d7 | Okta Dashboard      | 2021-08-26T09:52:55+05:30 | <null> | 2021-08-26T09:52:55+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
| oidc_client         | 0oa1me6urfbwt5Mjw5d7 | Service Client Name | 2021-08-31T15:02:50+05:30 | <null> | 2021-08-31T15:02:50+05:30 | ACTIVE | OPENID_CONNECT | {"app":{},"notifications":{"vpn":{"network
+---------------------+----------------------+---------------------+---------------------------+--------+---------------------------+--------+----------------+-------------------------------------------
> select * from okta_auth_server
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
| name    | id                   | created                   | description                                        | issuer                                       | issuer_mode | last_updated             
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
| default | aus1kcfvzjch1gpKh5d7 | 2021-08-26T09:52:50+05:30 | Default Authorization Server for your Applications | https://dev-50078045.okta.com/oauth2/default | ORG_URL     | 2021-08-26T09:52:50+05:30
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
> select * from okta_auth_server where name = 'default'
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
| name    | id                   | created                   | description                                        | issuer                                       | issuer_mode | last_updated             
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
| default | aus1kcfvzjch1gpKh5d7 | 2021-08-26T09:52:50+05:30 | Default Authorization Server for your Applications | https://dev-50078045.okta.com/oauth2/default | ORG_URL     | 2021-08-26T09:52:50+05:30
+---------+----------------------+---------------------------+----------------------------------------------------+----------------------------------------------+-------------+--------------------------
> select * from okta_factor where user_id = '00u1kcfw5yDiVG80P5d7'
+----------------------+----------------------+-------------------+---------------------+---------------------------+---------------------------+----------+--------------------+----------+--------+-----
| id                   | user_id              | user_name         | factor_type         | created                   | last_updated              | provider | status             | embedded | verify | titl
+----------------------+----------------------+-------------------+---------------------+---------------------------+---------------------------+----------+--------------------+----------+--------+-----
| mbl1m6472anhxTgOE5d7 | 00u1kcfw5yDiVG80P5d7 | [email protected] | sms                 | 2021-08-30T18:24:44+05:30 | 2021-08-30T18:24:44+05:30 | OKTA     | PENDING_ACTIVATION | <null>   | <null> | mbl1
| ost1m64qvuJk6dICv5d7 | 00u1kcfw5yDiVG80P5d7 | [email protected] | token:software:totp | 2021-08-30T18:23:16+05:30 | 2021-08-30T18:23:16+05:30 | OKTA     | PENDING_ACTIVATION | <null>   | <null> | ost1
+----------------------+----------------------+-------------------+---------------------+---------------------------+---------------------------+----------+--------------------+----------+--------+-----
  • Update tables for better use of API filters, context cancellation in list calls and page limiting for limit clause in query

    Update tables for better use of API filters, context cancellation in list calls and page limiting for limit clause in query

    Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

    Describe the solution you'd like A clear and concise description of what you want to happen.

    Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

    Additional context Add any other context or screenshots about the feature request here.

  • Fix GET API call in table okta_application

    Fix GET API call in table okta_application

    Describe the bug The GET API call is throwing an error: cannot unmarshal object into Go value of type okta.App

    Steampipe version (steampipe -v) Example: v0.10.0

    Plugin version (steampipe plugin list) Example: v0.1.0

    To reproduce

    > select * from okta_application where id = '0oa1kchdmerpZir9y5d7'
Error: json: cannot unmarshal object into Go value of type okta.App
  • The `profile` column in the `okta_application` table does not contain any information

    The `profile` column in the `okta_application` table does not contain any information

    Describe the bug For any rows from the okta_application table, the profile column never has data

    Steampipe version (steampipe -v) v0.10.0

    Plugin version (steampipe plugin list) v0.1.0

    To reproduce Run select name, profile from okta_application while having at least 1 application

    Expected behavior The profile column should have data or not be defined if the API doesn't return any data for it

    Additional context Application type - https://github.com/okta/okta-sdk-golang/blob/master/okta/application.go#L35

  • Add table okta_factor. Closes #17

    Add table okta_factor. Closes #17

    Example query results

    Results 
    > select
  id,
  user_id,
  factor_type,
  created,
  status
from
  okta_factor;
2021-09-01T22:09:21.631+0530 [TRACE] steampipe: executor in
⠇ Loading results... 2021-09-01T22:09:23.484+0530 [TRACE] steampipe: restartInteractiveSession
2021-09-01T22:09:23.484+0530 [TRACE] steampipe: Close prompt - then 1
2021-09-01T22:09:23.484+0530 [TRACE] steampipe: executor out
+----------------------+----------------------+---------------------+---------------------+--------------------+
| id                   | user_id              | factor_type         | created             | status             |
+----------------------+----------------------+---------------------+---------------------+--------------------+
| mbl1l5dh18Js2io3S5d7 | 00u1kcigdvWtR96HY5d7 | sms                 | 2021-08-27 10:03:19 | PENDING_ACTIVATION |
| ost1l5cklwIRvLzUY5d7 | 00u1kcigdvWtR96HY5d7 | token:software:totp | 2021-08-27 09:56:43 | ACTIVE             |
| uft1l5e40d7dkyGXM5d7 | 00u1kcigdvWtR96HY5d7 | token:software:totp | 2021-08-27 10:02:26 | ACTIVE             |
+----------------------+----------------------+---------------------+---------------------+--------------------+
> select
  id,
  user_id,
  factor_type,
  created,
  status
from
  okta_factor
where
  status = 'PENDING_ACTIVATION';
2021-09-01T22:09:31.522+0530 [TRACE] steampipe: executor in
2021-09-01T22:09:31.526+0530 [TRACE] steampipe: restartInteractiveSession
2021-09-01T22:09:31.526+0530 [TRACE] steampipe: Close prompt - then 1
2021-09-01T22:09:31.526+0530 [TRACE] steampipe: executor out
+----------------------+----------------------+-------------+---------------------+--------------------+
| id                   | user_id              | factor_type | created             | status             |
+----------------------+----------------------+-------------+---------------------+--------------------+
| mbl1l5dh18Js2io3S5d7 | 00u1kcigdvWtR96HY5d7 | sms         | 2021-08-27 10:03:19 | PENDING_ACTIVATION |
+----------------------+----------------------+-------------+---------------------+--------------------+
> select
  id,
  user_id,
  factor_type,
  created,
  provider,
  status
from
  okta_factor
where
  provider = 'OKTA';
2021-09-01T22:09:39.173+0530 [TRACE] steampipe: executor in
2021-09-01T22:09:39.175+0530 [TRACE] steampipe: restartInteractiveSession
2021-09-01T22:09:39.175+0530 [TRACE] steampipe: Close prompt - then 1
2021-09-01T22:09:39.175+0530 [TRACE] steampipe: executor out
+----------------------+----------------------+---------------------+---------------------+----------+--------------------+
| id                   | user_id              | factor_type         | created             | provider | status             |
+----------------------+----------------------+---------------------+---------------------+----------+--------------------+
| mbl1l5dh18Js2io3S5d7 | 00u1kcigdvWtR96HY5d7 | sms                 | 2021-08-27 10:03:19 | OKTA     | PENDING_ACTIVATION |
| ost1l5cklwIRvLzUY5d7 | 00u1kcigdvWtR96HY5d7 | token:software:totp | 2021-08-27 09:56:43 | OKTA     | ACTIVE             |
+----------------------+----------------------+---------------------+---------------------+----------+--------------------+
> select
  id,
  user_id,
  factor_type,
  created,
  status
from
  okta_factor
where
  id = 'ost1l5cklwIRvLzUY5d7' and user_id = '00u1kcigdvWtR96HY5d7';
2021-09-01T22:09:47.439+0530 [TRACE] steampipe: executor in
2021-09-01T22:09:47.806+0530 [TRACE] steampipe: restartInteractiveSession
2021-09-01T22:09:47.806+0530 [TRACE] steampipe: Close prompt - then 1
2021-09-01T22:09:47.806+0530 [TRACE] steampipe: executor out
+----------------------+----------------------+---------------------+---------------------+--------+
| id                   | user_id              | factor_type         | created             | status |
+----------------------+----------------------+---------------------+---------------------+--------+
| ost1l5cklwIRvLzUY5d7 | 00u1kcigdvWtR96HY5d7 | token:software:totp | 2021-08-27 09:56:43 | ACTIVE |
+----------------------+----------------------+---------------------+---------------------+--------+
>
  • Add table okta_network_zone closes #13

    Add table okta_network_zone closes #13

    Integration test logs

    Logs 
    Add passing integration test logs here

    Example query results

    Results

    Basic info

    select
  name,
  id,
  created,
  status,
  system,
  locations,
  proxy_type,
  usage
from
  okta_network_zone;

    +---------------+----------------------+---------------------+--------+--------+-----------------------------------------------------------------------------------------+---------------+-----------+
| name          | id                   | created             | status | system | locations                                                                               | proxy_type    | usage     |
+---------------+----------------------+---------------------+--------+--------+-----------------------------------------------------------------------------------------+---------------+-----------+
| BlockedIpZone | nzo1kcfw1rkgD4Ctm5d7 | 2021-08-26 04:22:51 | ACTIVE | true   | <null>                                                                                  |               | BLOCKLIST |
| ind-022       | nzo1kefi33MO8ZrK85d7 | 2021-08-26 06:34:25 | ACTIVE | false  | [{"country":"AF","region":"AF-BAL"},{"country":"AS"},{"country":"AU","region":"AU-NT"}] | TorAnonymizer | POLICY    |
| LegacyIpZone  | nzo1kcfw1qwLaONDt5d7 | 2021-08-26 04:22:51 | ACTIVE | true   | <null>                                                                                  |               | POLICY    |
+---------------+----------------------+---------------------+--------+--------+-----------------------------------------------------------------------------------------+---------------+-----------+

    List TorAnonymizer proxy type network zone

    select
  name,
  id,
  created,
  status,
  system,
  proxy_type
from
  okta_network_zone
where
  proxy_type = 'TorAnonymizer';

    +---------+----------------------+---------------------+--------+--------+---------------+
| name    | id                   | created             | status | system | proxy_type    |
+---------+----------------------+---------------------+--------+--------+---------------+
| ind-022 | nzo1kefi33MO8ZrK85d7 | 2021-08-26 06:34:25 | ACTIVE | false  | TorAnonymizer |
+---------+----------------------+---------------------+--------+--------+---------------+

    List network zones location and region details

    select
  name,
  id,
  l -> 'country' as country,
  l -> 'region' as region
from
  okta_network_zone,
  jsonb_array_elements(locations) as l;

    +---------+----------------------+---------+----------+
| name    | id                   | country | region   |
+---------+----------------------+---------+----------+
| ind-022 | nzo1kefi33MO8ZrK85d7 | "AF"    | "AF-BAL" |
| ind-022 | nzo1kefi33MO8ZrK85d7 | "AS"    | <null>   |
| ind-022 | nzo1kefi33MO8ZrK85d7 | "AU"    | "AU-NT"  |
+---------+----------------------+---------+----------+
  • Bug: `okta_user` and `okta_factor` table fails with `context canceled` error.

    Bug: `okta_user` and `okta_factor` table fails with `context canceled` error.

    Describe the bug okta_user and okta_factor table fails with context canceled errors. It seems to fail due to a timeout issue for a larger number of users in the account.

    There should be a better mechanism to retry for such errors.

     WITH OKTA_MFA as (
  SELECT 
    user_id
  FROM
    okta_factor
  WHERE
    status = 'ACTIVE'
  AND
    factor_type IN ('push','token:software:totp')
  ORDER BY
    user_id, factor_type
), 
OKTA_USERS as (
  SELECT
    id,
    email,
    status,
    last_login
  FROM
    okta_user
  ORDER BY
    id, email
)
SELECT
    U.email as resource,
    CASE
        WHEN U.status <> 'ACTIVE' THEN 'skip'
        WHEN COUNT(F.user_id) = 0 THEN 'alarm'
        ELSE 'ok'
    END AS status,
    CASE
        WHEN U.status <> 'ACTIVE' THEN 'User ' || u.email || ' is not active.'
        WHEN COUNT(F.user_id) = 0 THEN 'User ' || u.email || ' does not have MFA configured.'
        ELSE 'User ' || u.email || ' is ok'
    END AS reason,
    U.email,
    U.last_login
FROM
    OKTA_USERS U
LEFT JOIN OKTA_MFA F on F.user_id = U.id
GROUP BY
    U.email,
    U.status,
    U.last_login

    2022-09-09 07:50:11.972 UTC [WARN] PluginManager setPluginCacheSizeMap: 6 connections.
2022-09-09 07:50:11.972 UTC [WARN] Total cache size 0Mb
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814946: listOktaUsers: list_users_error="Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled"
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: listOktaUsers: list_users_error="Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled"
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: streamRows error chan select: Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled
2022-09-09 07:50:14.481 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709814927: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: streamRows error chan select: Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled
2022-09-09 07:50:14.481 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709814927: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:24.004 UTC [WARN] PluginManager setPluginCacheSizeMap: 6 connections.
2022-09-09 07:50:24.004 UTC [WARN] Total cache size 0Mb
2022-09-09 07:50:58.971 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.972 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: streamRows error chan select: context deadline exceeded
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: error chan select: context deadline exceeded
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709826246: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaUsers: list_users_paging_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.975 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled" (edited)

    Steampipe version (steampipe -v) Example: v0.3.0

    Plugin version (steampipe plugin list) Example: v0.5.0

    To reproduce Steps to reproduce the behavior (please include relevant code and/or commands).

    Expected behavior A clear and concise description of what you expected to happen.

    Additional context Slack thread

Related tags
Database tools sql postgresql okta okta-cli steampipe steampipe-plugin postgresql-fdw
Use SQL to instantly query resources, data sources and more from Terraform code. Open source CLI. No DB required.
 Use SQL to instantly query resources, data sources and more from Terraform code. Open source CLI. No DB required.

Terraform Plugin for Steampipe Use SQL to query data from Terraform configuration files. Get started → Documentation: Table definitions & examples Com

Dec 22, 2022
Use SQL to instantly query Algolia indexes and configuration. Open source CLI. No DB required

Use SQL to instantly query Algolia indexes and configuration. Open source CLI. No DB required

Oct 1, 2022
Use SQL to instantly query Datadog resources across accounts. Open source CLI. No DB required.

steampipe-plugin-datadog Datadog Plugin for Steampipe Use SQL to query dashboards, users, roles and more from Datadog. Get started → Documentation: Ta

Dec 17, 2022
Use SQL to instantly query Hypothesis resources. Open source CLI. No DB required.

Hypothesis Plugin for Steampipe Prerequisites Steampipe Golang Build $ git clone https://github.com/judell/steampipe-plugin-hypothesis.git $ cd steam

Dec 11, 2022
Use SQL to query information including Users, Groups, Clients, Roles and more from Keycloak.

Keycloak Plugin for Steampipe [WIP] THIS IS NOT ACTIVE NOR WORKING YET - DO NOT USE Use SQL to query information including Users, Groups, Clients, Rol

Jan 6, 2023
Use SQL to query host, DNS and exploit information using Shodan. Open source CLI. No DB required.

Shodan Plugin for Steampipe Query Shodan with SQL Use SQL to query host, DNS and exploit information using Shodan. For example: select * from shod

Nov 10, 2022
Use SQL to data from CSV files. Open source CLI. No DB required.
 Use SQL to data from CSV files. Open source CLI. No DB required.

CSV Plugin for Steampipe Use SQL to query data from CSV files. Get started → Documentation: Table definitions & examples Community: Slack Channel Get

Nov 10, 2022
Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.
 Use SQL to instantly query file, domain, URL and IP scanning results from VirusTotal.

VirusTotal Plugin for Steampipe Use SQL to query file, domain, URL and IP scanning results from VirusTotal. Get started → Documentation: Table definit

Nov 10, 2022
Use SQL to query instances, domains and more from Prometheus.
 Use SQL to query instances, domains and more from Prometheus.

Use SQL to instantly query Prometheus metrics, alerts, labels and more. Open source CLI. No DB required.

Nov 28, 2022
Use SQL to query databases, logs and more from PlanetScale

Use SQL to instantly query PlanetScale databases, branches and more. Open source CLI. No DB required.

Sep 30, 2022
This is the code example how to use SQL to query data from any relational databases in Go programming language.

Go with SQL example This is the code example how to use SQL to query data from any relational databases in Go programming language. To start, please m

Mar 12, 2022
Manage SQL databases, users and grant using kubernetes manifests

SqlOperator Operate sql databases, users and grants. This is a WIP project and should not at all be used in production at this time. Feel free to vali

Nov 28, 2021
Steampipe plugin to query your Baleen namespaces, custom rules and more

Baleen plugin for Steampipe Use SQL to query namespaces, rules and more from Baleen. Get started → Documentation: Table definitions & examples Quick s

Jun 16, 2022
Go-Postgresql-Query-Builder - A query builder for Postgresql in Go

Postgresql Query Builder for Go This query builder aims to make complex queries

Nov 17, 2022
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.
 OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Dec 29, 2022
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Dec 6, 2022
Query and Provision Cloud Infrastructure using an extensible SQL based grammar
 Query and Provision Cloud Infrastructure using an extensible SQL based grammar

Deploy, Manage and Query Cloud Infrastructure using SQL [Documentation] [Developer Guide] Cloud infrastructure coding using SQL InfraQL allows you to

Oct 25, 2022
sqlbench measures and compares the execution time of one or more SQL queries.

sqlbench sqlbench measures and compares the execution time of one or more SQL queries. The main use case is benchmarking simple CPU-bound query varian

Dec 6, 2022
Run SQL queries against JSON, CSV, Excel, Parquet, and more.

Run SQL queries against JSON, CSV, Excel, Parquet, and more This is a CLI companion to DataStation (a GUI) for running SQL queries against data files.

Dec 31, 2022