85 lines
4.9 KiB
Org Mode
85 lines
4.9 KiB
Org Mode
: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
|