WebSVN – ALCASAR – Diff – /scripts/test-retrieve-users-attributes-sh



db_query="SELECT attribute, value FROM ( \
 	( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) UNION \
	( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ORDER BY ug.priority ) UNION \
	( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) \
) attrs GROUP BY attribute;"
db_radreply_res=$(mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns)
 
filter=$(echo "$db_radreply_res"             | awk '$1 == "Alcasar-Filter"                     { print $2 }')
filterProto=$(echo "$db_radreply_res"        | awk '$1 == "Alcasar-Protocols-Filter"           { print $2 }')
statusOpenRequired=$(echo "$db_radreply_res" | awk '$1 == "Alcasar-Status-Page-Must-Stay-Open" { print $2 }')
echo "USER_NAME = $USER_NAME; filter = $filter; filterproto = $filterProto; statusOpenRequired = $statusOpenRequired";

db_query="SELECT attribute, value FROM ( \
 	( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' AND attribute = 'Expiration' ) UNION \
	( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND attribute = 'Expiration' ORDER BY ug.priority ) UNION \
	( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' AND attribute = 'Expiration' ) \
	) attrs GROUP BY attribute;"
db_radcheck_expiration_res=$(mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns) 
# if a expiration date exists we retrieve all radreply attributes
	if [ `echo $db_radcheck_expiration_res|wc -l` == '1' ]; then
		echo "###########################"
		echo "## Radreply attributes"
		db_query="SELECT attribute, value FROM ( \
 			( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' ) UNION \
			( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \
			( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' ) \
			) attrs GROUP BY attribute;"
		mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns |  while IFS= read -r loop
		do
			attr=`echo $loop|cut -d" " -f1`
			attr_value=`echo $loop|cut -d" " -f2-`
			echo "$attr = $attr_value"
		done 

		db_query="SELECT attribute, value FROM ( \
		( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' ) UNION \
		( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \
		( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' ) \
		) attrs GROUP BY attribute;"
		mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns |  while IFS= read -r loop
		do
			attr=`echo $loop|cut -d" " -f1`
			attr_value=`echo $loop|cut -d" " -f2-`
			echo "$attr = $attr_value"
		done

Subversion Repositories ALCASAR

(root)/scripts/test-retrieve-users-attributes-sh – Rev 2971 → 3269

Rev 2971		Rev 3269
Line 18...		Line 18...
18	`db_query="SELECT attribute, value FROM ( \`	18	`db_query="SELECT attribute, value FROM ( \`
19	`( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) UNION \`	19	`( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) UNION \`
20	`( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ORDER BY ug.priority ) UNION \`	20	`( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ORDER BY ug.priority ) UNION \`
21	`( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) \`	21	`( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' AND (attribute IN ('Alcasar-Filter', 'Alcasar-Protocols-Filter', 'Alcasar-Status-Page-Must-Stay-Open')) ) \`
22	`) attrs GROUP BY attribute;"`	22	`) attrs GROUP BY attribute;"`
23	`db_radreply_res=$(mysql -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns)`	23	`db_radreply_res=$(mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns)`
24		24
25	`filter=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Filter" { print $2 }')`	25	`filter=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Filter" { print $2 }')`
26	`filterProto=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Protocols-Filter" { print $2 }')`	26	`filterProto=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Protocols-Filter" { print $2 }')`
27	`statusOpenRequired=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Status-Page-Must-Stay-Open" { print $2 }')`	27	`statusOpenRequired=$(echo "$db_radreply_res" \| awk '$1 == "Alcasar-Status-Page-Must-Stay-Open" { print $2 }')`
28	`echo "USER_NAME = $USER_NAME; filter = $filter; filterproto = $filterProto; statusOpenRequired = $statusOpenRequired";`	28	`echo "USER_NAME = $USER_NAME; filter = $filter; filterproto = $filterProto; statusOpenRequired = $statusOpenRequired";`
Line 34...		Line 34...
34	`db_query="SELECT attribute, value FROM ( \`	34	`db_query="SELECT attribute, value FROM ( \`
35	`( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' AND attribute = 'Expiration' ) UNION \`	35	`( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' AND attribute = 'Expiration' ) UNION \`
36	`( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND attribute = 'Expiration' ORDER BY ug.priority ) UNION \`	36	`( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' AND attribute = 'Expiration' ORDER BY ug.priority ) UNION \`
37	`( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' AND attribute = 'Expiration' ) \`	37	`( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' AND attribute = 'Expiration' ) \`
38	`) attrs GROUP BY attribute;"`	38	`) attrs GROUP BY attribute;"`
39	`db_radcheck_expiration_res=$(mysql -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns)`	39	`db_radcheck_expiration_res=$(mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns)`
40	`# if a expiration date exists we retrieve all radreply attributes`	40	`# if a expiration date exists we retrieve all radreply attributes`
41	if [ `echo $db_radcheck_expiration_res\|wc -l` == '1' ]; then	41	if [ `echo $db_radcheck_expiration_res\|wc -l` == '1' ]; then
42	`echo "###########################"`	42	`echo "###########################"`
43	`echo "## Radreply attributes"`	43	`echo "## Radreply attributes"`
44	`db_query="SELECT attribute, value FROM ( \`	44	`db_query="SELECT attribute, value FROM ( \`
45	`( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' ) UNION \`	45	`( SELECT attribute, value FROM radreply WHERE username = '$USER_NAME' ) UNION \`
46	`( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \`	46	`( SELECT attribute, value FROM radgroupreply gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \`
47	`( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' ) \`	47	`( SELECT attribute, value FROM radgroupreply WHERE groupname = 'default' ) \`
48	`) attrs GROUP BY attribute;"`	48	`) attrs GROUP BY attribute;"`
49	`mysql -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns \| while IFS= read -r loop`	49	`mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns \| while IFS= read -r loop`
50	`do`	50	`do`
51	attr=`echo $loop\|cut -d" " -f1`	51	attr=`echo $loop\|cut -d" " -f1`
52	attr_value=`echo $loop\|cut -d" " -f2-`	52	attr_value=`echo $loop\|cut -d" " -f2-`
53	`echo "$attr = $attr_value"`	53	`echo "$attr = $attr_value"`
54	`done`	54	`done`
Line 57...		Line 57...
57	`db_query="SELECT attribute, value FROM ( \`	57	`db_query="SELECT attribute, value FROM ( \`
58	`( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' ) UNION \`	58	`( SELECT attribute, value FROM radcheck WHERE username = '$USER_NAME' ) UNION \`
59	`( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \`	59	`( SELECT attribute, value FROM radgroupcheck gr LEFT JOIN radusergroup ug ON gr.groupname = ug.groupname WHERE username = '$USER_NAME' ORDER BY ug.priority ) UNION \`
60	`( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' ) \`	60	`( SELECT attribute, value FROM radgroupcheck WHERE groupname = 'default' ) \`
61	`) attrs GROUP BY attribute;"`	61	`) attrs GROUP BY attribute;"`
62	`mysql -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns \| while IFS= read -r loop`	62	`mariadb -u$DB_USER -p$DB_PASSWORD -D radius -e "$db_query" -Ns \| while IFS= read -r loop`
63	`do`	63	`do`
64	attr=`echo $loop\|cut -d" " -f1`	64	attr=`echo $loop\|cut -d" " -f1`
65	attr_value=`echo $loop\|cut -d" " -f2-`	65	attr_value=`echo $loop\|cut -d" " -f2-`
66	`echo "$attr = $attr_value"`	66	`echo "$attr = $attr_value"`
67	`done`	67	`done`