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