sh /tmp/userlookup.sh -h

sh /tmp/userlookup.sh -q Benutzername
sh /tmp/userlookup.sh -q Gruppenname
sh /tmp/userlookup.sh -q "Name mit Leerzeichen"

sh /tmp/userlookup.sh -i -q Abfrage

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-20.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
#
#####################################

# Variables
version="1.1"									# version of this script
compatibelfirmware_pattern="(19\.5\.|20\.0\.)" 	# versions this script is compatibel to (regular expression)
compatibelfirmware_text="19.5.x, 20.0.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 20.0 (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 "-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 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:hi options; do
    case $options in
		h) 	Help;;	
		i)	ignoreversion="true";;
		q) 	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



# 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 username=LOWER('$query');")
usertype=$(psql -U nobody -d corporate -t -A -c "select usertype from tbluser where 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
		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
			echo -en "(type: group)${NOCOLOR}\n\n"
		else
			echo -en "(type: unknown)${NOCOLOR}\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