deft/notes/cisco_user_data.org

Cisco User Data

tags

Cisco FT SecureX Simplified Registration

1. Go to the admin interface and dump the CSV of users and orgs
2. nix-shell csvtool
3. history:

311 csvtool namedcol "org-id" users.csv
312  csvtool namedcol "org-id" users.csv | sort
313  csvtool namedcol "org-id" users.csv | sort | uniq -c
314  csvtool namedcol "org-id" users.csv | sort | uniq -c| sort -nr
315  csvtool namedcol "org-id" users.csv | sort | uniq -c| sort -n
316  csvtool namedcol "org-id" users.csv | sort | uniq -c| sort -n|wc -l
317  csvtool namedcol "user-email" users.csv
318  csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'
319  csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'|sort|uniq -c
320  csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'|sort|uniq -c|sort -n
321  csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'|sort|uniq -c|sort -n|wc -l
322  csvtool namedcol "activation-status" orgs.csv
323  head orgs.csv
324  csvtool namedcol "scim-status" orgs.csv
325  csvtool namedcol "id,scim-status" orgs.csv
326  csvtool join namedcol "org-id" namedcol "id" user.csv org.csv
327  csvtool namedcol "id,scim-status" orgs.csv > sorgs.csv
328  csvtool namedcol "org-id,user-email,role" > susers.csv
329  head susers.csv
330  csvtool namedcol "org-id,user-email,role" users.csv > susers.csv
331  head susers.csv
332  csvtool join 1 1 susers.csv sorgs.csv > joined.csv
333  head joined.csv
334  rm joined.csv
335  join --help
336  join -t, -1 1 -2 1 susers sorgs
337  join -t, -1 1 -2 1 susers.csv sorgs.csv
338  sort susers.csv > su.csv
339  sort sorgs.csv > so.csv
340  join -t, -1 1 -2 1 su.csv so.csv
341  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'
342  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin"'
343  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated"'
344  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'
345  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq -c|sort -n
346  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq
347  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n
348  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|grep 6
349  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 6'|wc -l
350  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 6'
351  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 5'
352  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 5'|wc -l
353  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 4'|wc -l
354  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 3'|wc -l
355  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 2'|wc -l
356  join -t, -1 1 -2 1 su.csv so.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}'|sort|uniq|awk -F, '{print $1}'|uniq -c|sort -n|awk '$1 == 1'|wc -l

1. to resume

# number of  unique orgs
csvtool namedcol "org-id" users.csv | sort | uniq -c
# list domain name of emails
csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'
# number of user by domain name of their email
csvtool namedcol "user-email" users.csv |sed 's/[^@]*@//'|sort|uniq -c|sort -n|wc -l
# couple id of orgs and their activation status
csvtool namedcol "id,scim-status" orgs.csv

# prepare the join
csvtool namedcol "id,scim-status" orgs.csv > sorgs.csv
csvtool namedcol "org-id,user-email,role" users.csv > susers.csv
sort susers.csv > su.csv
sort sorgs.csv > so.csv
join -t, -1 1 -2 1 su.csv so.csv > joint.csv

cat joint.csv|sed 's#,[^,@]*@#,#'|awk -F, '$3 == "admin" && $4 == "activated" {print $2","$1}' > activated-admins.csv