Schema: User Activity Tables
This chapter contains information on the schema and supported joins for user activity and identity events. The Firepower System can detect user activity on your network by tracking various types of user logins, including LDAP, POP3, IMAP, SMTP, AIM, and SIP.
For more information, see the sections listed in the following table.
Table 8-1 Schema for User Identity Tables
|
For the table that stores information on...
|
|
discovered_users |
Information about the users detected by the system. |
5.0+ |
user_discovery_event |
User discovery events, which communicate the details of user activity on your network. |
5.0+ |
discovered_users
The discovered_users
table contains detailed information about each user detected by the system.
The discovered_users
table supersedes the deprecated rua_user
table starting with Version 5.0 of the Firepower System.
For more information, see the following sections:
discovered_users Fields
The following table describes the fields you can access in the discovered_users
table.
Table 8-2 discovered_users Fields
|
|
dept |
The department of the user. |
email |
The email address for the user. |
first_name |
The first name for the user. |
ip_address |
This field has been deprecated and returns null for all queries. |
ipaddr |
A binary representation of the IPv4 or IPv6 address for the host where the user login was detected. |
last_name |
The last name for the user. |
last_seen_sec |
The UNIX timestamp of the date and time the system last reported a login for the user. |
last_updated_sec |
The UNIX timestamp of the date and time the user’s information was last updated. |
name |
The name for the user. |
phone |
The phone number for the user. |
rna_service |
Field deprecated in Version 5.0. Returns null for all queries. |
user_id |
The internal identification number of the user who last logged onto the host. |
discovered_users Joins
The following table describes the joins you can perform on the rua_user
table.
discovered_users Sample Query
The following query returns up to 25 discovered user records that were generated since a specified date and time.
SELECT user_id, ip_address, email, name, last_seen_sec, last_updated_sec
FROM discovered_users
WHERE last_seen_sec >= UNIX_TIMESTAMP("2011-10-01 00:00:00")
LIMIT 0, 25;
user_discovery_event
The user_discovery_event
table contains a record for each user discovery event.
Note that starting in Version 5.0, the Firepower System records the detection of user activity at the managed device level, no longer by detection engine. The detection_engine_name
and detection_engine_uuid
fields in this table have been replaced by the sensor_name
and sensor_uuid
fields respectively. Queries on these fields will return information about the managed device that generated the user discovery event.
For more information, see the following sections:
user_discovery_event Fields
The following table describes the fields you can access in the user_discovery_event
table.
Table 8-4 user_discovery_event Fields
|
|
application_protocol_id |
An internal identifier for the detected application protocol. |
application_protocol_name |
One of:
- the name of the application used in the connection: LDAP, POP3, and so on
-
pending if the system cannot identify the application for one of several reasons
- blank if there is no application information in the connection
|
description |
The user name when the discovery event type is either Delete User Identity, or User Identity Dropped. Otherwise, blank. |
domain_name |
Name of the domain for the on which the user was detected. |
domain_uuid |
UUID of the domain in which the user was detected. This is presented in binary. |
endpoint_profile |
Name of the type of device used by the connection endpoint. |
event_id |
An internal identification number for the discovery event. |
event_time_sec |
The UNIX timestamp of the date and time of the discovery event. |
event_type |
The type of discovery event. For example, New User Identity or User Login . |
ip_address |
Field deprecated in Version 5.2. Returns null for all queries. |
ipaddr |
A binary representation of the IP address of the host where the user activity was detected. |
location_ip |
IP address of the interface communicating with ISE. Can be IPv4 or IPv6. |
reported_by |
The IPv4 address, IPv6 address, or NetBIOS name of the Active Directory server reporting a user login. |
security_group |
ID number of the network traffic group. |
sensor_address |
The IP address of the managed device that detected the user discovery event. Format is ipv4_address,ipv6_address. |
sensor_name |
The text name of the managed device that detected the user discovery event. |
sensor_uuid |
A unique identifier for the managed device, or 0 if sensor_name is null . |
user_dept |
The department of the user who last logged onto the host. |
user_email |
The email address of the user who last logged onto the host. |
user_first_name |
The first name of the user. |
user_id |
The internal identification number of the user who last logged onto the host. |
user_last_name |
The last name of the user. |
user_last_seen_sec |
The UNIX timestamp of the date and time the system last reported a login for the user. |
user_last_updated_sec |
The UNIX timestamp of the date and time the user’s information was last updated. |
user_name |
The user name for the user who last logged onto the host. |
user_phone |
The phone number for the user who last logged onto the host. |
user_discovery_event Joins
The following table describes the joins you can perform on the user_discovery_event
table.
user_discovery_event Sample Query
The following query returns up to 25 user event records generated by a selected managed device since a particular date and time.
SELECT event_time_sec, ipaddr, sensor_name, event_type, user_name, user_last_seen_sec, user_last_updated_sec
FROM user_discovery_event
WHERE sensor_name = sensor_name
AND user_last_seen_sec >= UNIX_TIMESTAMP("2011-10-01 00:00:00") ORDER BY event_type ASC
LIMIT 0, 25;