See quickstart Menu

How to extract data for reporting

A frequently asked question is how can a user extract user or event records from OneLogin?

One way to get this job done is using the API which supports the ability to page through large datasets and get granualar about the fields you want to return.

In the examples below we will demonstrate how to use OneLogin SDKs to loop through and extract the data you need.

Export user details to CSV

Run the extract from the command line to produce a CSV file with a column for user attributes, including any custom attributes you defined.

This example uses the OneLogin Ruby SDK. Get the code for the example on Github.

all-users-to-csv.rb

require 'onelogin'

#
# This example shows how you can export user details from OneLogin using the Ruby SDK
#
# Usage:
# 1. Set your own CLIENT_ID and CLIENT_SECRET below
# 2. From terminal run "ruby all-users-to-csv.rb" to extract all users including
#    any custom attributes that might have been defined for the each user
#

client = OneLogin::Api::Client.new(
    client_id: 'ONELOGIN_CLIENT_ID',
    client_secret:'ONELOGIN_CLIENT_SECRET',
    region: 'us'
)

attribute_names = ['id', 'external_id', 'email', 'username', 'firstname', 'lastname', 'distinguished_name',
                  'phone', 'company', 'department', 'status', 'member_of', 'samaccountname', 'userprincipalname',
                  'group_id', 'role_ids', 'custom_attributes', 'openid_name', 'locale_code', 'comment', 'directory_id',
                  'manager_ad_id', 'trusted_idp_id', 'activated_at', 'created_at', 'updated_at',
                  'password_changed_at', 'invitation_sent_at', 'invalid_login_attempts', 'last_login', 'locked_until']

custom_attribute_names = client.get_custom_attributes

CSV.open('users.csv', 'wb') do |csv|
  # header row
  csv << attribute_names + custom_attribute_names

  client.get_users.each do |user|

    row = []

    # standard attributes
    attribute_names.each do |attribute_name|
      row << user.send(attribute_name)
    end

    # custom attributes
    custom_attribute_names.each do |attribute_name|
      row << user.custom_attributes[attribute_name] unless user.custom_attributes.empty?
    end

    csv << row
  end
end
      

API Reference

Export events to CSV

Run the extract from the command line to produce a CSV file with a column for event attributes.

You can filter the events by providing the event_type_id and date ranges in the since and until parameters.

This example uses the OneLogin Ruby SDK. Get the code for the example on Github.

events-to-csv.rb

require 'onelogin'
require 'optparse'
require 'optparse/time'

#
# This example shows how you can export events from OneLogin using the Ruby SDK
#
# Usage:
# 1. Set your own CLIENT_ID and CLIENT_SECRET below
# 2. From terminal run "ruby events-to-csv.rb" for the last 1000 events
# 3. Use the command line args to filter events
#
# e.g. "ruby events-to-csv.rb -t 5 -s 2018-01-01" for login events since Jan 1 2018
#


# Parse CLI arguments
options = {}

OptionParser.new do |opts|
  opts.banner = "Usage: events-to-csv.rb [options]"

  opts.on("-sSINCE", "--since=SINCE", Time, "Events after this date") do |s|
    options[:since] = s.iso8601
  end

  opts.on("-uUNTIL", "--UNTIL=UNTIL", Time, "Events before this date") do |u|
    options[:until] = u.iso8601
  end

  opts.on("-lLIMIT", "--limit=LIMIT", Integer, "Only return this many events, Default 1000") do |l|
    options[:limit] = l
  end

  opts.on("-tTYPE", "--type=TYPE", Integer, "Filter by event type id") do |t|
    options[:event_type_id] = t
  end
end.parse!

# Fetch the events
client = OneLogin::Api::Client.new(
    client_id: 'ONELOGIN_CLIENT_ID',
    client_secret: 'ONELOGIN_CLIENT_SECRET',
    region: 'us'
)

attribute_names = ['id', 'created_at', 'account_id', 'user_id', 'user_name', 'event_type_id',
                  'notes', 'ipaddr', 'actor_user_id', 'actor_user_name', 'assuming_acting_user_id',
                  'role_id', 'role_name', 'app_id', 'group_id', 'group_name', 'otp_device_id',
                  'otp_device_name', 'policy_id', 'policy_name', 'actor_system', 'custom_message',
                  'operation_name', 'directory_sync_run_id', 'directory_id', 'resolution', 'client_id',
                  'resource_type_id', 'error_description']

counter = 0
limit = options[:limit] || 1000

# We remove limit from options parsed to the api as we want to fetch
# the max number of records possible and then use the cursor that is
# built into the ruby sdk to limit the results
options.delete(:limit)

CSV.open('events.csv', 'wb') do |csv|
  puts "Exporting events to events.csv"

  # header row
  csv << attribute_names

  # fetch the events
  client.get_events(options).take(limit).each do |event|
    csv << attribute_names.map { |attribute_name| event.send(attribute_name) }
  end
end

puts "Exported #{counter} events to events.csv"
      

API Reference


Have a Question?

Found a problem or a bug? Submit a support ticket.

Looking for walkthroughs or how-to guides on OneLogin's user and admin features? Check out the documentation in our Knowledge Base.

Have a product idea or request? Share it with us in our Ideas Portal.