Skip to content

Instantly share code, notes, and snippets.

@jrafanie

jrafanie/freeipa_user_impact_analysis.rb

Last active March 12, 2026 15:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jrafanie/496f84db31cf8837d45334fdb4ec158f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jrafanie/496f84db31cf8837d45334fdb4ec158f to your computer and use it in GitHub Desktop.
Download ZIP
FreeIPA User Impact Analysis Script
Raw
freeipa_user_impact_analysis.rb
#!/usr/bin/env ruby
# frozen_string_literal: true
# FreeIPA User Impact Analysis Script
# Purpose: Analyze users affected by FreeIPA authentication changes
# Usage: 1) Copy to /var/www/miq/vmdb/tools.
# 2) cd to /var/www/miq/vmdb/
# 3) Run via ruby tools/freeipa_user_impact_analysis.rb
#   (or bundle exec ruby tools/freeipa_user_impact_analysis.rb)
#
class FreeIpaUserImpactAnalysis
SEPARATOR = "=" * 100
SUB_SEPARATOR = "-" * 100
def self.run
analyzer = new
analyzer.generate_report
end
def initialize
@stats = {
total_users: 0,
email_only: 0,
duplicate_email: 0,
freeipa_only: 0,
total_resources: Hash.new(0)
}
end
def generate_report
print_header
print_summary
puts
analyze_category_1_email_only
puts
analyze_category_2_duplicate_email
puts
analyze_category_3_freeipa_only
puts
print_final_summary
end
private
def print_header
puts SEPARATOR
puts "FreeIPA USER IMPACT ANALYSIS REPORT"
puts "Generated: #{Time.current}"
puts SEPARATOR
puts
end
def print_summary
@stats[:total_users] = User.where.not(userid: 'admin').count
email_only_count = User.where("LOWER(userid) = LOWER(email)").where.not(email: nil).count
duplicate_email_count = User.where.not(email: nil).group(:email).having("COUNT(*) > 1").count.keys.count
freeipa_only_count = User.where("LOWER(userid) != LOWER(COALESCE(email, ''))").where.not("userid LIKE '%@%'").count
puts "SUMMARY:"
puts " Total Users (excluding admin): #{@stats[:total_users]}"
puts " Category 1 - Email-Only Users: #{email_only_count}"
puts " Category 2 - Duplicate Email Users: #{duplicate_email_count} email addresses"
puts " Category 3 - FreeIPA-Only Users: #{freeipa_only_count}"
end
def analyze_category_1_email_only
puts SEPARATOR
puts "CATEGORY 1: EMAIL-ONLY LOGIN USERS"
puts "Risk Level: MEDIUM - Need userid update but no merge required"
puts SEPARATOR
puts
puts "Users where userid matches their email (case-insensitive)"
puts "These users logged in via email and will need userid updated to FreeIPA principal"
puts
users = User.where("LOWER(userid) = LOWER(email)")
.where.not(email: nil)
.order(Arel.sql("lastlogon DESC NULLS LAST"))
if users.empty?
puts "✓ No email-only users found"
return
end
@stats[:email_only] = users.count
puts "Found #{users.count} email-only users:"
puts
users.each_with_index do |user, idx|
print_user_details(user, idx + 1, "EMAIL_ONLY", "MEDIUM")
puts
end
end
def analyze_category_2_duplicate_email
puts SEPARATOR
puts "CATEGORY 2: DUPLICATE EMAIL USERS (PRIMARY CONCERN)"
puts "Risk Level: HIGH - Complex merge with potential data loss"
puts SEPARATOR
puts
puts "Multiple users sharing the same email address"
puts "These are problematic duplicates that need merging"
puts
duplicate_emails = User.where.not(email: nil)
.group(:email)
.having("COUNT(*) > 1")
.pluck(:email)
if duplicate_emails.empty?
puts "✓ No duplicate email users found"
return
end
@stats[:duplicate_email] = duplicate_emails.count
puts "Found #{duplicate_emails.count} email addresses with duplicates:"
puts
duplicate_emails.sort.each_with_index do |email, idx|
users = User.where(email: email).order(Arel.sql("lastlogon DESC NULLS LAST"))
print_duplicate_email_group(email, users, idx + 1)
puts
end
end
def analyze_category_3_freeipa_only
puts SEPARATOR
puts "CATEGORY 3: FREEIPA USERNAME ONLY"
puts "Risk Level: LOW - Likely unaffected by the change"
puts SEPARATOR
puts
puts "Users with FreeIPA-style userids that don't match their email"
puts "These users should be unaffected but verify no duplicates exist"
puts
users = User.where("LOWER(userid) != LOWER(COALESCE(email, ''))")
.where.not("userid LIKE '%@%'")
.order(Arel.sql("lastlogon DESC NULLS LAST"))
if users.empty?
puts "✓ No FreeIPA-only users found"
return
end
@stats[:freeipa_only] = users.count
puts "Found #{users.count} FreeIPA-only users:"
puts
users.each_with_index do |user, idx|
print_user_details(user, idx + 1, "FREEIPA_ONLY", "LOW")
puts
end
end
def print_user_details(user, index, category, risk_level)
resources = collect_user_resources(user)
total_resources = resources.values.sum
puts "#{index}. User ID: #{user.id} | Category: #{category} | Risk: #{risk_level}"
puts SUB_SEPARATOR
# Basic Info
puts " UserID: #{user.userid}"
puts " Email: #{user.email || 'N/A'}"
puts " Name: #{user.name || 'N/A'}"
puts " First Name: #{user.first_name || 'N/A'}"
puts " Last Name: #{user.last_name || 'N/A'}"
# Login History
puts " Last Login: #{format_datetime(user.lastlogon)}"
puts " Last Logoff: #{format_datetime(user.lastlogoff)}"
puts " Failed Login Attempts: #{user.failed_login_attempts || 0}"
# Days since last login
if user.lastlogon
days_since = ((Time.current - user.lastlogon) / 1.day).round
puts " Days Since Last Login: #{days_since}"
end
# Group and Role Info
puts " Current Group: #{user.current_group&.description || 'N/A'}"
puts " Current Role: #{user.current_group&.miq_user_role&.name || 'N/A'}"
all_groups = user.miq_groups.pluck(:description)
if all_groups.any?
puts " All Groups: #{all_groups.join(', ')}"
end
# Resource Ownership
puts " Total Resources: #{total_resources}"
if total_resources > 0
puts " Resource Breakdown:"
resources.each do |type, count|
puts " - #{type.to_s.titleize.ljust(20)}: #{count}" if count > 0
end
end
# Risk Assessment
print_risk_assessment(user, category, total_resources)
end
def print_duplicate_email_group(email, users, group_index)
puts "#{group_index}. Email: #{email} (#{users.count} users) | Risk: HIGH"
puts SUB_SEPARATOR
users.each_with_index do |user, idx|
resources = collect_user_resources(user)
total_resources = resources.values.sum
puts
puts " User #{idx + 1} of #{users.count}:"
puts " ID: #{user.id}"
puts " UserID: #{user.userid}"
puts " Name: #{user.name || 'N/A'}"
puts " Last Login: #{format_datetime(user.lastlogon)}"
puts " Last Logoff: #{format_datetime(user.lastlogoff)}"
puts " Failed Login Attempts: #{user.failed_login_attempts || 0}"
if user.lastlogon
days_since = ((Time.current - user.lastlogon) / 1.day).round
puts " Days Since Last Login: #{days_since}"
end
puts " Current Group: #{user.current_group&.description || 'N/A'}"
puts " Current Role: #{user.current_group&.miq_user_role&.name || 'N/A'}"
all_groups = user.miq_groups.pluck(:description)
if all_groups.any?
puts " All Groups: #{all_groups.join(', ')}"
end
puts " Total Resources: #{total_resources}"
if total_resources > 0
puts " Resource Breakdown:"
resources.each do |type, count|
puts " - #{type.to_s.titleize.ljust(18)}: #{count}" if count > 0
end
end
end
puts
print_merge_recommendation(users)
end
def print_merge_recommendation(users)
# Sort by: most recent login, then most resources
sorted_users = users.sort_by do |u|
resources = collect_user_resources(u)
[-(u.lastlogon&.to_i || 0), -resources.values.sum]
end
primary = sorted_users.first
secondaries = sorted_users[1..]
puts " MERGE RECOMMENDATION:"
puts " Primary (Keep): User ID #{primary.id} (#{primary.userid})"
puts " Reason: #{merge_reason(primary, users)}"
if secondaries.any?
puts " Secondary (Merge): #{secondaries.map { |u| "User ID #{u.id} (#{u.userid})" }.join(', ')}"
end
# Check for conflicts
conflicts = detect_conflicts(users)
if conflicts.any?
puts " ⚠ CONFLICTS DETECTED:"
conflicts.each { |conflict| puts " - #{conflict}" }
end
puts " Action Required: Manual merge and userid update to FreeIPA principal"
end
def merge_reason(primary, all_users)
reasons = []
# Check if most recent login
most_recent = all_users.max_by { |u| u.lastlogon || Time.at(0) }
reasons << "most recent login" if primary.id == most_recent.id
# Check if most resources
primary_resources = collect_user_resources(primary).values.sum
max_resources = all_users.map { |u| collect_user_resources(u).values.sum }.max
reasons << "most resources (#{primary_resources})" if primary_resources == max_resources && primary_resources > 0
reasons.any? ? reasons.join(", ") : "first in list"
end
def detect_conflicts(users)
conflicts = []
# Different roles
roles = users.map { |u| u.current_group&.miq_user_role&.name }.compact.uniq
conflicts << "Different roles: #{roles.join(', ')}" if roles.count > 1
# Different group memberships
all_group_sets = users.map { |u| u.miq_groups.pluck(:id).sort }
conflicts << "Different group memberships" if all_group_sets.uniq.count > 1
# Both have resources
users_with_resources = users.select { |u| collect_user_resources(u).values.sum > 0 }
conflicts << "Multiple users have resources (#{users_with_resources.count} users)" if users_with_resources.count > 1
conflicts
end
def print_risk_assessment(user, category, total_resources)
puts " Risk Assessment:"
case category
when "EMAIL_ONLY"
puts " - MEDIUM risk: Userid needs update to FreeIPA principal"
puts " - No merge required, but userid change needed"
if total_resources > 0
puts " - Has #{total_resources} resources that will remain owned after userid update"
else
puts " - No resources to worry about"
end
when "FREEIPA_ONLY"
puts " - LOW risk: Likely unaffected by authentication changes"
if total_resources > 0
puts " - Has #{total_resources} resources that should remain unaffected"
end
end
end
def collect_user_resources(user)
resources = {}
# Direct user_id ownership
resources[:reports] = user.miq_reports.count
resources[:external_urls] = user.external_urls.count rescue 0
resources[:automate_workspaces] = user.automate_workspaces.count rescue 0
resources[:service_orders] = user.service_orders.count rescue 0
# EVM owner (evm_owner_id)
resources[:vms] = Vm.where(evm_owner_id: user.id, template: false).count
resources[:templates] = Vm.where(evm_owner_id: user.id, template: true).count
resources[:authentications] = Authentication.where(evm_owner_id: user.id).count
# String userid column
resources[:custom_buttons] = CustomButton.where(userid: user.userid).count
resources[:widget_sets_by_userid] = MiqWidgetSet.where(userid: user.userid).count
# Polymorphic owner
resources[:widget_sets_by_owner] = MiqWidgetSet.where(owner_type: 'User', owner_id: user.id).count
# Requests
resources[:requests] = user.miq_requests.count
# Services (via requests)
service_ids = MiqRequestTask.joins(:miq_request)
.where(miq_requests: { requester_id: user.id })
.where(destination_type: 'Service')
.pluck(:destination_id)
.uniq
resources[:services] = service_ids.count
# Approvals
resources[:approvals_as_approver] = MiqApproval.where(approver_type: 'User', approver_id: user.id).count
resources[:approval_stamps] = MiqApproval.where(stamper_id: user.id).count
# Update stats
resources.each { |type, count| @stats[:total_resources][type] += count }
resources
end
def format_datetime(dt)
return "Never" if dt.nil?
dt.strftime("%Y-%m-%d %H:%M:%S %Z")
end
def print_final_summary
puts SEPARATOR
puts "FINAL SUMMARY"
puts SEPARATOR
puts
puts "User Categories:"
puts " Email-Only Users: #{@stats[:email_only]}"
puts " Duplicate Email Users: #{@stats[:duplicate_email]} email addresses"
puts " FreeIPA-Only Users: #{@stats[:freeipa_only]}"
puts
puts "Total Resources Across All Users:"
@stats[:total_resources].sort_by { |k, v| -v }.each do |type, count|
puts " #{type.to_s.titleize.ljust(25)}: #{count}" if count > 0
end
puts
puts "RECOMMENDATIONS:"
puts " 1. Review Category 2 (Duplicate Email) users first - HIGH PRIORITY"
puts " 2. Plan merge strategy for each duplicate email group"
puts " 3. Update Category 1 (Email-Only) userids to FreeIPA principals"
puts " 4. Verify Category 3 (FreeIPA-Only) users have no issues"
puts " 5. Create database backup before making any changes"
puts
puts SEPARATOR
puts "Analysis Complete"
puts SEPARATOR
end
end
# now load rails
require File.expand_path('../config/environment', __dir__)
FreeIpaUserImpactAnalysis.run
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment