sh /tmp/userlookup.sh -h

sh /tmp/userlookup.sh -q username
sh /tmp/userlookup.sh -q [email protected]
sh /tmp/userlookup.sh -q groupname
sh /tmp/userlookup.sh -q "name with space"

To search for names including "abc"
sh /tmp/userlookup.sh -f abc 

sh /tmp/userlookup.sh -i -q "query"

curl -s -L -o /tmp/userlookup.sh https://it-tech.wiki/sdc_download/2690/?key=tn9tt0ol2eqccogdhc6elc9czebl2j && echo -en "\n\nUse \"sh /tmp/userlookup.sh -h\" to get help for first use\n\n"

#!/bin/sh
####################################
#
# Sophos Firewall (XG/XGS) - SFOS 19.x-21.5.x and hopefully above
# Script to get information where a user or group is used in configuration
#
# Credits: 
# Sebastian Mies ([email protected])
# I am not a professional programer. But if it works - its enough for me :)
# Feedback and wishes are welcome at my mail address!
# Feel free to share the script, but please do not reshare it on any public webserver! 
# To share public please use this link: https://it-tech.wiki/vt3e 
#
# 
# License:
# Free for any use (with no warrenty)
# 
# Version:
# 1.0	20231027	Initial Release
# 1.1	20240221	Added (*) marker for the dependency when deleting
# 2.0	20250606	Bugfix with searching case insensitiv
#					added SFOS 21.5 to compatibility list
#					added connected authentication server in output for users (group has none)
#					added search and select function (-s) for better user experience
#
#####################################

# Variables
version="2.0"											# version of this script
compatibelfirmware_pattern="(19\.5\.|20\.0\.|21\.5\.)" 	# versions this script is compatibel to (regular expression)
compatibelfirmware_text="19.5.x, 20.0.x, 21.5.x"		# versions this script is compatibel to (text)
ignoreversion="false"									# set to "true" to ignore compatibel version or use -i option

# Output modifications for "echo"
# more colors: https://ansi.gabebanks.net/?ref=linuxhandbook.com
NOCOLOR='\033[0m'
buCyan='\033[36;4;1m'	# Cyan underline bold
bCyan='\033[1;36m'	# Cyan bold
bGreen='\033[1;32m'		# Green bold
bRed='\033[1;31m'		# Red bold
bYellow='\033[1;33m'	# Yellow bold
buYellow='\033[33;4;1m'	# Yellow bold underline
uYellow='\033[1;33;4m'	# Yellow underline
POS40='\033[40G'		# cursor to position 40

Red='\033[1;31m'		# Red bold
Green='\033[1;32m'		# Green bold
Yellow='\033[1;33m'		# Yellow bold

# Print HELP
Help() {
	echo -en "${Yellow}Script to get a information where a user or group is used in configuration on a Sophos Firewall (XG/XGS)\n\n"
	echo -en "Version ($version) - works for SFOS 19.5 to 21.5 (older versions should work too)\n\n"
	echo -en "License and usage: Free for any use (with no warrenty)\n"
	echo -en "Feedback welcome at: [email protected]\n\n"
	echo -en "${uYellow}Parameter for using the Script:${NOCOLOR}\n\n"	
	echo -en "${Yellow}Syntax: ${0##*/} [-h|q|i]\n"
	echo -en "options:\n"
	echo -en "-h ${POS34}Print this help.\n"
	echo -en "-q ${POS34}\"username|groupname\" to search for in configuration (case insensitive).\n"
	echo -en "   ${bRed}!!! ${Yellow}Always enter full username including domain if there is one ${bRed}!!!${Yellow}\n"
	echo -en "-s Search for username|groupname with placeholder and displays selectable entries.\n"
	echo -en "-i ${POS34}Ignore SFOS version.\n\n"
	echo -en "${uYellow}Examples:${NOCOLOR}${Yellow}\n"
	echo -en "sh ${0##*/} -q username\n"
	echo -en "sh ${0##*/} -q [email protected]\n"
	echo -en "sh ${0##*/} -q groupname\n"
	echo -en "sh ${0##*/} -q \"name with space\"\n"
	echo -en "sh ${0##*/} -i -q username\n"
	echo -en "\n${NOCOLOR}"
	exit 0
}

# reading parameters 
while getopts :q:s:hi options; do
    case $options in
		h) 	Help;;	
		i)	ignoreversion="true";;
		s)	case "$OPTARG" in
                -*) echo -en "${bRed}ERROR: invalid argument for -s: $OPTARG ${NOCOLOR}\n"; exit 1 ;;				
            esac
            searchname=$OPTARG
            ;;
		q) 	case "$OPTARG" in
				-*) echo -en "${bRed}ERROR: invalid argument for -q: $OPTARG ${NOCOLOR}\n"; exit 1 ;;				
            esac
            query=$OPTARG
            ;;
		\?)	echo -en "${Red}ERROR: Unknown option provided: $0 ${NOCOLOR}\n"
			Help;;
    esac
done


# Checking current firmware
currentfirmware=$(cat /boot/sfloader.version)
if ! (echo "$currentfirmware" | grep -E "$compatibelfirmware_pattern" > /dev/null) && (! $ignoreversion); then
	echo -en "${bRed}ERROR: Firmware version not supported. Supported versions: \"$compatibelfirmware_text\". To run script and ignore version use -i option.${NOCOLOR}\n\n"
	exit 0
fi


# -s or -q need - but not both
if [ "$searchname" != "" ] && [ "$query" != "" ]; then
    echo -en "${bRed}ERROR: Only one option may be used, not both: -q or -s${NOCOLOR}\n"
    exit 1
fi

if [ "$searchname" != "" ]; then
	exclude_list="support
Clientless Open Group
Known Users
Unknown Users"
	echo -en "\n${bYellow}Searching for: ${bGreen}$searchname${NOCOLOR}\n\n"
	foundentries=$(psql -U nobody -d corporate -t -A -c "select username, CASE WHEN usertype IN (1, 5, 7) THEN 'user' WHEN usertype IN (3, 4) THEN 'group' ELSE 'n/a' END AS type from tbluser where LOWER(username) like LOWER('%$searchname%');")
	

	IFS='
	' set -f  
	entries=$(echo "$foundentries" | sed '/^$/d')  # remove empty files
	
	# Check if something is found
	if [ -z "$entries" ]; then
		echo -en "${bRed}No matching user or group found with '$searchname'.${NOCOLOR}\n\n"
		exit 1
	fi
	# Fill rows
	entry_count=$(echo "$entries" | wc -l | tr -d ' ')
	# Cancel if more then 10 results
	if [ "$entry_count" -gt 10 ]; then
		echo -en "${bRed}More than 10 result found with '$searchname' ($entry_count). Please refine your search term.${NOCOLOR}\n\n"
		exit 1
	fi
	# display select menu
	echo -en "${bYellow}Please choose an entry:${NOCOLOR}\n\n"
	i=1
	for line in $entries; do
		username=$(echo "$line" | cut -d'|' -f1)
		type=$(echo "$line" | cut -d'|' -f2)
		
		# skip username in exclude_list
		if printf "%s\n" "$exclude_list" | grep -Fxq "$username"; then
			continue
		fi	
		
		echo -en "${bCyan}[$i] ${bGreen}$username ($type)${NOCOLOR}\n"
		eval "entry_$i=\"$username\""
		i=$((i + 1))
	done
	if [ $i -eq 1 ]; then
		echo -en "${bRed}No matching user or group found with '$searchname'.${NOCOLOR}\n\n"
		exit 1
	fi
	echo -en "\n"
	# ask for entry
	echo -en "${bYellow}Choose number: [1-$((i - 1))]: ${NOCOLOR}"
	read choice
	# check is selected entry is valid
	eval selected="\$entry_$choice"
	if [ -z "$selected" ]; then
		echo -en "${bRed}Invalid choise. Try again${NOCOLOR}\n\n"
		exit 1
	fi
	# set search query
	query="$selected"
fi


# argument check
echo -en "\n"
uname_length=${#query}
if [ "$query" == "" ]; then
	echo -en "${bRed}ERROR: -q (username|groupname) argument not provided. For more information use -h option to get help ${NOCOLOR}\n" 
	exit 0	
#else 
#	if [ $uname_length -gt 50 ]; then
#		echo -en "${bRed}ERROR: Username/Groupname too long. Maximum allowed length on SFOS is 50 characters. You provided: $uname_length ${NOCOLOR}\n"
#		exit 0	
#	else 
#		echo -en "${bGreen}Will search now for username \"$query\" in configuration ...${NOCOLOR}\n\n"
#	fi
fi



echo -en "${bYellow}${buYellow}INFORMATION:${NOCOLOR}${bYellow}\nThe configurations marked with an ${bCyan}(*)${NOCOLOR}${bYellow} usually prevent the deletion of an object if the specified user name or user group is in use.\n\n${NOCOLOR}"

# Looking for user or group id
userid=$(psql -U nobody -d corporate -t -A -c "select userid from tbluser where LOWER(username)=LOWER('$query');")
usertype=$(psql -U nobody -d corporate -t -A -c "select usertype from tbluser where LOWER(username)=LOWER('$query');")
if [ -n "$userid" ]; then
	echo -en "${bYellow}UserID/GroupID:${NOCOLOR}${POS40}${bGreen}$userid "
	if [ $usertype = 1 -o $usertype = 5 -o $usertype = 7 ]; then # is a user
		echo -en "(type: user)${NOCOLOR}\n\n"
		groupid=$(psql -U nobody -d corporate -t -A -c "select groupid from tbluser where userid='$userid';")		
		if [ -n "$groupid" ]; then
			usergroup=$(psql -U nobody -d corporate -t -A -c "select username from tbluser where userid='$groupid';")
			echo -en "${bYellow}Groupname ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$usergroup${NOCOLOR}\n\n"
		fi
	else
		if [ $usertype = 3 -o $usertype = 4 ]; then # is a group
			echo -en "(type: group)${NOCOLOR}\n\n"
		else
			echo -en "(type: unknown)${NOCOLOR}\n\n"
		fi
	fi
	if [ $usertype = 1 -o $usertype = 5 -o $usertype = 7 ]; then # only when local user
		authserverid=$(psql -U nobody -d corporate -t -A -c "select authserverid from tbluser where userid='$userid';")
		if [ $authserverid = 1 ]; then # local user
			echo -en "${bYellow}Authentication server:${NOCOLOR}${POS40}${bGreen}local\n\n" 
		else 
			authservername=$(psql -U nobody -d corporate -t -A -c "select name from tblauthserverconfig where id='$authserverid';")
			echo -en "${bYellow}Authentication server:${NOCOLOR}${POS40}${bGreen}$authservername (external)\n\n" 
		fi
	fi
else 
	echo -en "${bRed}ERROR: No user or group with name \"$query\" found.${NOCOLOR}\n\n"
	exit 0
fi


# Looking for user in firewall rules
count_fwruleid=$(psql -U nobody -d corporate -t -c "select count(*) from tblfwuser where userid='$userid';")
if [ $count_fwruleid -gt 0 ]; then
	fwruleid=$(psql -U nobody -d corporate -t -A -R "," -c "select fwruleid from tblfwuser where userid='$userid';")
	counter=0
	for i in ${fwruleid//,/ }
	do
		fwrulename=$(psql -U nobody -d corporate -t -A -c "select name from tblfirewallrule where ruleid='$i';")
		counter=$((counter+1))
		if [ $counter -gt 1 ]; then
			echo -en "${POS40}${bGreen}$fwrulename (Rule ID: $i)${NOCOLOR}\n"		
		else
			echo -en "${bYellow}FW rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$fwrulename (Rule ID: $i)${NOCOLOR}\n"		
		fi		
	done
	echo -en "\n"
else		
	echo -en "${bYellow}FW rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi


# Looking for user in tls rules
count_tlsruleid=$(psql -U nobody -d corporate -t -c "select count(*) from tbltlsrule_src_user_rel where user_id='$userid';")
if [ $count_tlsruleid -gt 0 ]; then
	tlsruleid=$(psql -U nobody -d corporate -t -A -R "," -c "select tls_rule_id from tbltlsrule_src_user_rel where user_id='$userid';")
	counter=0
	for i in ${tlsruleid//,/ }
	do
		tlsrulename=$(psql -U nobody -d corporate -t -A -c "select name from tbltlsrule where id='$i';")
		counter=$((counter+1))
		if [ $counter -gt 1 ]; then
			echo -en "${POS40}${bGreen}$tlsrulename (Rule ID: $i)${NOCOLOR}\n"		
		else
			echo -en "${bYellow}TLS rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}$tlsrulename (Rule ID: $i)${NOCOLOR}\n"		
		fi		
	done
	echo -en "\n"
else		
	echo -en "${bYellow}TLS rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi


# Looking for user in web rules
count_policyrulegroupid=$(psql -U nobody -d corporate -t -c "select count(*) from tblsecpolicyruleuserrel where userid='$userid';")
#echo $count_policyrulegroupid
if [ $count_policyrulegroupid -gt 0 ]; then
	policyrulegroupid=$(psql -U nobody -d corporate -t -A -R "," -c "select policyrulegroupid from tblsecpolicyruleuserrel where userid='$userid';")
	counter=0
	rulenames=""	
	for i in ${policyrulegroupid//,/ }
	do
		counter=$((counter+1))		
		securitypolicyid=$(psql -U nobody -d corporate -t -A -c "select securitypolicyid from tblsecpolicywebcategoryrel where policyrulegroupid='$i';")
		securitypolicyname=$(psql -U nobody -d corporate -t -A -c "select securitypolicyname from tblsecuritypolicy where securitypolicyid='$securitypolicyid';")
		if [ $counter -gt 1 ]; then
			rulenames=$rulenames","$securitypolicyname
		else
			rulenames=$rulenames$securitypolicyname
		fi
	done	

	echo -en "${bYellow}Web rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
	echo "$rulenames" | awk -F',' '{
		for (i = 1; i <= NF; i++) {
			gsub(/^ */, "", $i);  # Entfernen von führenden Leerzeichen
			gsub(/ *$/, "", $i);  # Entfernen von abschließenden Leerzeichen
			word_count[$i]++;
		}
	}
	END {     
		for (word in word_count) {
			printf "\033[40G%s\"%s\" (%d times)\n", "", word, word_count[word]			
		}
	}'
	echo -en "${NOCOLOR}\n"
else
	echo -en "${bYellow}Web rules ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi

# Looking for user in web rules override
policyoverride=$(psql -U nobody -d corporate -t -A -c "select value from tblconfiguration where key='override_policy_users';")
if echo "$policyoverride" | awk -v num="$userid" -F',' '{for (i=1; i<=NF; i++) if ($i == num) exit 0; exit 1}'; then
	echo -en "${bYellow}Web rule policy override:${NOCOLOR}${bGreen}${POS40}Set${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Web rule policy override:${NOCOLOR}${bRed}${POS40}Not set${NOCOLOR}\n\n"
fi

# Looking for user in Hotspots
count_hotspotid=$(psql -U nobody -d corporate -t -A -c "select count(*) from tbluserrel where userid='$userid';")
if [ $count_hotspotid -gt 0 ]; then
	hotspotid=$(psql -U nobody -d corporate -t -A -R "," -c "select hotspotid from tbluserrel where userid='$userid';")
	counter=0
	hotspotnames=""	
	for i in ${hotspotid//,/ }
	do
		counter=$((counter+1))				
		tblhotspotname=$(psql -U nobody -d corporate -t -A -c "select name from tblhotspot where id='$i';")
		if [ $counter -gt 1 ]; then
			hotspotnames=$hotspotnames","$tblhotspotname
		else
			hotspotnames=$hotspotnames$tblhotspotname
		fi
	done
	
	echo -en "${bYellow}Hotspots ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
	echo "$hotspotnames" | awk -F',' '{
		for (i = 1; i <= NF; i++) {
			printf "\033[40G%s\n", $i
		}
	}'
	echo -en "${NOCOLOR}\n"
else
	echo -en "${bYellow}Hotspots ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bRed}${POS40}none${NOCOLOR}\n\n"
fi

# Looking for SMTP relay
count_authenticatedrelay=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblauthenticatedrelay where authuserid='$userid';")
if [ $count_authenticatedrelay -gt 0 ]; then
	echo -en "${bYellow}SMTP relay ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}Set${NOCOLOR}\n\n"	
else
	echo -en "${bYellow}SMTP relay ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"	
fi

# Looking for user in WAF authentication policy
count_wafauth=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblwafauth_profile_user_rel where userid='$userid';")
if [ $count_wafauth -gt 0 ]; then
	wafauthid=$(psql -U nobody -d corporate -t -A -R "," -c "select profileid from tblwafauth_profile_user_rel where userid='$userid';")
	counter=0
	wafauthnames=""
	for i in ${wafauthid//,/ }
	do
		counter=$((counter+1))
		tblauthnames=$(psql -U nobody -d corporate -t -A -c "select name from tblwafauth_profile where id='$i';")
		if [ $counter -gt 1 ]; then
			wafauthnames=$wafauthnames","$tblauthnames
		else
			wafauthnames=$wafauthnames$tblauthnames
		fi
	done
	echo -en "${bYellow}WAF auth. policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
	echo "$wafauthnames" | awk -F',' '{
		for (i = 1; i <= NF; i++) {
			printf "\033[40G%s\n", $i
		}
	}'
	echo -en "${NOCOLOR}\n"
else
	echo -en "${bYellow}WAF auth. policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi


# Looking for user in remote access IPsec
count_raipsec=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblxauthusers where userid='$userid' and usertype='u';")
if [ $count_raipsec -gt 0 ]; then
	echo -en "${bYellow}Remote access IPSec ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Remote access IPSec ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi


# Looking for user in remote access SSLVPN
sslvpnid=$(psql -U nobody -d corporate -t -A -R "," -c "select sslvpnpolicy from tbluser where userid='$userid';")
if [ -n "$sslvpnid" ]; then
	sslvpnname=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblsslvpnpolicy where id='$sslvpnid';")
	echo -en "${bYellow}Remote access SSLVPN:${NOCOLOR}${POS40}${bGreen}$sslvpnname${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Remote access SSLVPN:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi


# Looking for user in remote access Clientless SSLVPN
clsslvpnid=$(psql -U nobody -d corporate -t -A -R "," -c "select clientlesspolicy from tbluser where userid='$userid';")
if [ -n "$clsslvpnid" ]; then
	clsslvpnname=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblsslvpnpolicy where id='$clsslvpnid';")
	echo -en "${bYellow}Remote access Clientless SSLVPN:${NOCOLOR}${POS40}${bGreen}$clsslvpnname${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Remote access Clientless SSLVPN:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi


# Looking for user in SD-WAN policy
sdwanid=$(psql -U nobody -d corporate -t -A -R "," -c "select policyrouteid from tbluser_pr_rel where userid='$userid';")
if [ -n "$sdwanid" ]; then
	counter=0
	sdwanames=""
	for i in ${sdwanid//,/ }
	do
		counter=$((counter+1))
		tblsdwanames=$(psql -U nobody -d corporate -t -A -R "," -c "select name from tblpolicyroute where id='$i';")
		if [ $counter -gt 1 ]; then
			sdwanames=$sdwanames","$tblsdwanames
		else
			sdwanames=$sdwanames$tblsdwanames
		fi
	done
	echo -en "${bYellow}SD-WAN policys ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${bGreen}"
	echo "$sdwanames" | awk -F',' '{
		for (i = 1; i <= NF; i++) {
			printf "\033[40G%s\n", $i
		}
	}'
	echo -en "${NOCOLOR}\n"	
else
	echo -en "${bYellow}SD-WAN policy ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}None${NOCOLOR}\n\n"
fi


# Looking for user in OTP
count_otp=$(psql -U nobody -d corporate -t -A -c "select count(*) from tblotp where userid='$userid';")
if [ $count_otp -gt 0 ]; then
	echo -en "${bYellow}OTP:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
	echo -en "${bYellow}OTP:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi


# Looking for user in anonymization
count_anonymization=$(psql -U nobody -d corporate -t -A -c "select count(*) from tbluser where userid='$userid' and authrole='2';")
if [ $count_anonymization -gt 0 ]; then
	echo -en "${bYellow}Anonymization ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Anonymization ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi

# Looking for user in anonymization exception
count_anonymizationex=$(psql -U nobody -t -A -d corporate -c "select count(*) from tbldeanonymizationinfo where LOWER(value)=LOWER('$query');")
if [ $count_anonymizationex -gt 0 ]; then
	echo -en "${bYellow}Anonymization exception ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bGreen}set${NOCOLOR}\n\n"
else
	echo -en "${bYellow}Anonymization exception ${bCyan}(*)${NOCOLOR}${bYellow}:${NOCOLOR}${POS40}${bRed}Not set${NOCOLOR}\n\n"
fi