:PROPERTIES:
:ID:       e6db475b-9ccc-43b2-bcfe-057215ddc1d1
:END:
#+TITLE: Cisco User Data
#+Author: Yann Esposito
#+Date: [2022-03-10]
- tags :: [[id:1208f09c-d37d-4e6b-9110-151f3c6b7d34][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~:


#+begin_src
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
#+end_src

4. to resume

#+begin_src bash
# 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

#+end_src