Table: auth0_log - Query Auth0 Logs using SQL

Auth0 Logs is a resource within the Auth0 Identity Platform that records and stores user activities, system events, and security-relevant incidents. It is designed to give administrators detailed visibility into the behaviors and actions within their Auth0 environment. Auth0 Logs aids in monitoring, troubleshooting, and maintaining the health and security of Auth0 applications.

Table Usage Guide

The auth0_log table provides insights into the logs within Auth0 Identity Platform. As a system administrator or security analyst, you can use this table to explore detailed log entries, including user activities, system events, and potential security incidents. This table is particularly useful for monitoring user behavior, troubleshooting issues, and enhancing the security posture of your Auth0 applications.

Examples

Identify instances where login attempts have failed to gain insights into potential security risks. This allows for a review of the associated IP addresses and user agents, enabling the detection and prevention of unauthorized access.

select
  date,
  description,
  ip,
  user_agent
from
  auth0_log
where
  type = 'f'
order by
  date desc;

select
  date,
  description,
  ip,
  user_agent
from
  auth0_log
where
  type = 'f'
order by
  date desc;

Logs filtered by client

Explore which authentication events are associated with a specific client ID. This can help in analyzing user behavior or troubleshooting issues related to a particular client application.

select
  date,
  description,
  ip,
  is_mobile
from
  auth0_log
where
  client_id = 'FrSZDDFGUH0afar5LHmCji1khmPmst6R'
order by
  date desc;

select
  date,
  description,
  ip,
  is_mobile
from
  auth0_log
where
  client_id = 'FrSZDDFGUH0afar5LHmCji1khmPmst6R'
order by
  date desc;

Account and IP blockings

Explore instances of account and IP blockings to understand potential security threats. This query helps in identifying suspicious activities by analyzing the patterns of blocked accounts and IP addresses.

select
  date,
  description,
  ip,
  is_mobile
from
  auth0_log
where
  type in ('limit_mu', 'limit_wc', 'limit_sul')
order by
  date desc;

select
  date,
  description,
  ip,
  is_mobile
from
  auth0_log
where
  type in ('limit_mu', 'limit_wc', 'limit_sul')
order by
  date desc;

Number of mobile and non-mobile successful logins

Analyze successful login patterns to understand the proportion of mobile versus non-mobile users. This can aid in tailoring user experiences based on device preference.

select
  is_mobile,
  count(1)
from
  auth0_log
where
  type = 's'
group by
  is_mobile;

select
  is_mobile,
  count(1)
from
  auth0_log
where
  type = 's'
group by
  is_mobile;

Schema for auth0_log

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
audience	text		API audience the event applies to.
client_id	text		The ID of the client (application).
client_name	text		The name of the client (application).
connection	text		Name of the connection the log event relates to.
connection_id	text		ID of the connection the log event relates to.
date	timestamp with time zone		The date when the log event was created.
description	text		The log event description.
details	jsonb		Additional useful details about this event (structure is dependent upon event type).
hostname	text		Hostname the log event applies to.
ip	text		The IP address of the log event source.
is_mobile	boolean		Whether the client was a mobile device (true) or desktop/laptop/server (false).
location_info	jsonb		Information about the location that triggered this event based on the `IP`.
log_id	text	=	Log identifier
organization_id	text		ID of the organization the log event relates to.
organization_name	text		Name of the organization the log event relates to.
scope	text		Scope permissions applied to the event.
strategy	text		Name of the strategy involved in the event.
strategy_type	text		Type of strategy involved in the event.
type	text		The log event type.
user_agent	text		User agent string from the client device that caused the event.
user_id	text		ID of the user involved in the log event.
user_name	text		Name of the user involved in the log event.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- auth0

You can pass the configuration to the command with the --config argument:

steampipe_export_auth0 --config '<your_config>' auth0_log