ldap-user-script.sh

#!/bin/bash

# LDAP Connection details
LDAP_SERVER="ldap://cofer.me"
BIND_DN="CN=ldap-bind,CN=Users,DC=cofer,DC=me"
PASSWORD="password"
BASE_DN="DC=cofer,DC=me"

# Output CSV file
OUTPUT_FILE="ad-users.csv"

# Write CSV header
echo "Username,AccountStatus,LastLogonTimestamp" > $OUTPUT_FILE

# Perform LDAP search
ldapsearch -x -H "$LDAP_SERVER" -D "$BIND_DN" -w "$PASSWORD" -b "$BASE_DN" "(objectClass=user)" sAMAccountName userAccountControl lastLogonTimestamp | while read -r line
do
  # Parse the relevant fields
  if [[ $line == sAMAccountName:* ]]; then
    USERNAME=$(echo "$line" | awk -F': ' '{print $2}')
  fi

  if [[ $line == userAccountControl:* ]]; then
    UAC=$(echo "$line" | awk -F': ' '{print $2}')
    
    # Check if account is disabled (userAccountControl flag 2 indicates account is disabled)
    if (( UAC & 2 )); then
      ACCOUNT_STATUS="Disabled"
    else
      ACCOUNT_STATUS="Enabled"
    fi
  fi

  if [[ $line == lastLogonTimestamp:* ]]; then
    # LastLogonTimestamp is in 100-nanosecond intervals since 1601-01-01
    LAST_LOGON_TIMESTAMP=$(echo "$line" | awk -F': ' '{print $2}')
    LAST_LOGON_SEEN=1
    
    if [[ "$LAST_LOGON_TIMESTAMP" != "0" ]]; then
      # Convert the timestamp from Windows filetime format to Unix epoch
      UNIX_TIMESTAMP=$((($LAST_LOGON_TIMESTAMP / 10000000) - 11644473600))
      LAST_LOGON_DATE=$(date -d @$UNIX_TIMESTAMP +"%Y-%m-%dT%H:%M:%S.%6NZ")
    else
        LAST_LOGON_DATE="Never"
    fi
  fi

  if [[ ! -z $USERNAME && ! -z $ACCOUNT_STATUS && ! -z $LAST_LOGON_SEEN ]]; then
    # Append to CSV file
    if [[ -z $LAST_LOGON_DATE ]]; then
      LAST_LOGON_DATE="1970-01-01T00:00:00.000000Z"
    fi
    echo "$USERNAME,$ACCOUNT_STATUS,$LAST_LOGON_DATE" >> $OUTPUT_FILE
    USERNAME=''
    ACCOUNT_STATUS=''
    LAST_LOGON_DATE=''
  fi
done

user-analysis.py

import pandas as pd
from datetime import datetime, timedelta

# Load the CSV files
wb_users = pd.read_csv('wb-users.csv')
ad_users = pd.read_csv('ad-users.csv')

# Convert date fields to datetime objects and ensure they are timezone-aware (UTC)
wb_users['last_sign_in'] = pd.to_datetime(wb_users['last_sign_in'], utc=True)
ad_users['LastLogonTimestamp'] = pd.to_datetime(ad_users['LastLogonTimestamp'], utc=True)

# Merge the two dataframes on the username fields
merged_users = pd.merge(wb_users, ad_users, left_on='user_name', right_on='Username')

# Get the current date and make it timezone-aware (UTC)
current_date = datetime.now().astimezone()

# Filter users who meet the conditions
# Condition 1: AccountStatus is "Disabled"
# Condition 2: Last sign-in is older than 365 days from today and LastLogonTimestamp is after last_sign_in
filtered_users = merged_users[
    (merged_users['AccountStatus'] == 'Disabled') |
    ((current_date - merged_users['last_sign_in'] > timedelta(days=365)) &
     (merged_users['LastLogonTimestamp'] > merged_users['last_sign_in']))
    ]

# Output the result
filtered_users_output = filtered_users[['user_name', 'AccountStatus', 'last_sign_in', 'LastLogonTimestamp']]
print(filtered_users_output)

# Optionally, save the filtered users to a CSV file
filtered_users_output.to_csv('filtered_users.csv', index=False)

user-count-sql.sql

psql postgres://posit_team:password@workbench-helm-postgresql:5432/posit_workbench?sslmode=disable -c "COPY (SELECT user_name, last_sign_in, locked FROM licensed_users) TO STDOUT WITH CSV HEADER;" > wb-users.csv