Automatize suspended user deletion

[Max]

Hello!

I´m new to GAM (more used to Powershell) and I´m quite lost with a request I´ve been asked to do.

I´ve been asked to develop a script that delete suspended users whose last login time is older than 6 months. Not just this, but also moving the ownership of their Drive files to a generic user.

Step by step, first I would like to filter suspended users with a last login time larger than 180 days. Then transfer their document´s ownership to another user. And at last, get them deleted.

Is it possible to "chain comands" just like with Powershell pipeline?

Thank you very much in advance.

Regards.

[Kim Nilsson]

Yes, you can pipe commands.

You can also batch them.

You can create a csv with one command, and read that with the next, and then delete the csv when you're done.

There is a thread for this exact question - deleting users suspended 180 days ago.

https://groups.google.com/g/google-apps-manager/c/lC5GtiMFjq0/m/NOatde6eDAAJ

To do what you want you must of course run a datatransfer first, and not delete the user.

https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Drive-Transfer

[Max]

Hello,

I´ve tried the following from the threads you told me (Thank you very much!)

D:\GAM>gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d" report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./deleteSuspendedUsers.csv

ERROR: config is not a valid argument for "gam"

D:\GAM>gam version

GAM 5.11 - https://git.io/gam - pyinstaller

Jay Lee <jay...@gmail.com>

Python 3.8.3 64-bit final

google-api-python-client 1.9.3

Windows 10 10.0.18362  Multiprocessor Free AMD64

Path: D:\GAM

What am I doing wrong?

Is there a thread called "What every newbie to GAM should read" or something like that? :)

Thank you.

Regards.

[Kim Nilsson]

Such commands only work with GAMADV-XTD3. 

[Ross Scroggs]

Max,

In Basic GAM you want

set GAM_CSV_ROW_FILTER="accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d"

gam report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./deleteSuspendedUsers.csv

Ross

--

ross.s...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/de1c4a8f-7c63-441e-9821-908f944e5197n%40googlegroups.com.

[Max]

Hello, I´ve tried that but received this error:

D:\GAM>set GAM_CSV_ROW_FILTER="accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d"

D:\GAM>gam report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./deleteSuspendedUsers.csv

ERROR: Item: csv_row_filter, Value: "accounts.is_suspended": "boolean:true,accounts.disabled_time:date<-180d", Expected true|false

D:\GAM>

I tried then to follow GAMADV way and received the same error:

D:\>gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d" report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./deleteSuspendedUsers.csv

ERROR: Item: csv_row_filter, Value: "accounts.is_suspended": "boolean:true,accounts.disabled_time:date<-180d", Expected true|false

D:\>

What can I do?

[Ross Scroggs]

Max,

For Basic GAM remove the quotes and escape the < with an ^

set GAM_CSV_ROW_FILTER=accounts.is_suspended:boolean:true,accounts.disabled_time:date^<-180d

Advanced Gam will work as show except that you're not running it, you're still running Basic GAM

Move to the Advanced GAM folder, cd C:\GANADV-XTD3

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/70962d69-b353-4cc0-a025-2163c77bee02n%40googlegroups.com.

[Max]

Ok, I have extracted the users list perfectly. Thank you very much!

Now I´m making a test for step 2: transferring Drive files from the users that must be deleted to a specific user.

I´ve created two users: TestUser1 and TestUser2. I´ve placed some files in TestUser1 Drive and shared some of them with a third user. Run this command to make a background datatransfer:

D:\GAMADV-XTD3>gam create datatransfer test...@test.grupoiberostar.com drive test...@test.grupoiberostar.com all privacy_level private,shared
Transfer Request Created
  Transfer ID: AKrEtIYTvno7ONRYHfKwOQaFoAfQJ-yKIAEBQl81yn1_QMUlAmuIQ6Y-fMoJtDKYdsyNckQ--DTTxTnFoakN6cOTRKMmGRgnXA
  Service: Drive and Docs
  From: test...@test.grupoiberostar.com
  To: test...@test.grupoiberostar.com

It seems to work fine.

Now, I must find out the way to do this with all the users contained in the CSV "deleteSuspendedUsers.csv" I extracted previously.

My first guess is:

gam csv ./deleteSuspendedUsers.csv gam create datatransfer ~email drive Destina...@domain.com  all privacy_level private,shared

Is this going to work? I guess I have to make a csv with other test users, but any advice will be highly appreciated.

Regards.

[Max]

Hello,

Can anyone please share their experience on this operation or give some advice?

Thank you.

Regards.

[Kim Nilsson]

Your example csv command should work fine, as long as the header of the email addresses is email.

Since you did create testusers, you could've just added a few more files and then run your csv command to verify that it worked.

[Maximiliano Alvarez Luque]

Hello,

Test datatransfer worked fine and today we are going to test it with actual CSV in order to find out how much time the background datatransfer will consume.

It seems that problem solved!

Thank you very much!

El sáb., 26 sept. 2020 a las 8:00, Kim Nilsson (<there.is.no...@gmail.com>) escribió:

Your example csv command should work fine, as long as the header of the email addresses is email.

Since you did create testusers, you could've just added a few more files and then run your csv command to verify that it worked.

--
You received this message because you are subscribed to a topic in the Google Groups "GAM for G Suite" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-manager/mklEZRLIMvs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/c66af8db-7a47-4cc4-90a7-d18acb0bbbd8n%40googlegroups.com.

[Max]

Hello,

I´m not sure when, but the scheduled process that I had to get all the accounts which have been suspended more than 6 months ago, has stopped working. 

This is the error I get with GAMADV-XTD3 version (also tried older one, but I get the same error):

C:\GAMADV-XTD3>gam version
GAMADV-XTD3 6.16.18 - https://github.com/taers232c/GAMADV-XTD3 - pyinstaller
Ross Scroggs <ross.s...@gmail.com>
Python 3.10.2 64-bit final
Windows 10 10.0.14393 SP0 Multiprocessor Free AMD64
Path: C:\GAMADV-XTD3
Config File: C:\Users\admgcdstask\.gam\gam.cfg, Section: DEFAULT, customer_id: C02esxebl, domain: domain.com

C:\GAMADV-XTD3>gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d" report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./scripts/deleteSuspendedUsers.csv
Getting Reports for all users
Got 7936 Reports for all users on 2022-06-25...

WARNING: csv_output_row_filter column "^accounts.disabled_time$" does not match any output columns

C:\GAMADV-XTD3>

Can you please help? How can I fix this?

Thank you very much.

Regards.

[Kim Nilsson]

Hi, Max.

There is currently a bug which makes the information inaccessible, so the script will not work until the bug is fixed. 

The data is available in the admin console, but not via API. 

/Kim 

[Max]

So, there´s nothing I can do to fix this and this script won´t be working for now.

Is this bug on Google´s side? My client is going to ask me about that... 

Thank you.

Regards.

[Kim Nilsson]

Yes, it is on Google's side.

Case 39283619

/Kim 

[Ross Scroggs]

Max,

Contact me directly via Meet/Zoom.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFuXwron5vEnyrk7ex7Jnc-_cDBiPTg7U8k3oigP8A4NEQ%40mail.gmail.com.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Ross,

old suspensions work great, but not since last summer. You and I have looked into this before, and I have an active support case. Engineers are working on it, but no ETA. 

/Kim 

[Max]

Thank you very much, I´ll tell my client about this issue. I think the problem started about two months ago, since I´ve found suspended users 8 months ago. And the script, which is run once a month, should delete them in 6.

Ross I would like to contact you by that means but I guess our time zones aren´t very compatible, I´m in Spain (GMT+1). :)

I'll be looking forward for news regarding this case, is there any other thread I can follow to be informed?

Thank you very much again.

Best regards.

[eric...@ww.com]

This might help, 

I run this using the last login as my criteria to output any suspended users against my terminated user ou greater that 365 days. We hold all term users for up to 1 year, don't ask.. Then I run a delete user seperatly . 

gam config csv_output_row_filter "lastLoginTime:date<-365d" print users lastlogintime suspended query "orgUnitPath='/Terminated Users'" custom all > ~/downloads/xxx.csv

gam csv ~/downloads/xxx.csv gam delete user "~primaryEmail"

Eric

[Ross Scroggs]

Max,

I'm in California (PDT) and can be available as early as 6:30AM PDT for a Meet/Zoom session.

Ross

--

You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/c9235ed2-0ecf-4f48-8228-fa650f3abb09n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Max]

Thank you Eric, but my client´s requirement is suspended users time, there´s some delegated accounts which have very old logon times, and in this case, as soon as they were suspended, they´ll be deleted, won´t be keep suspended for 6 months.

Regards.

[Kim Nilsson]

Yeah, there's almost nothing we can do about it till Google fixes it.

What you can do is report it to Google, with reference to my case #, and say you know it's a known issue, and that it greatly hinders your work.

/Kim

--

There is No Substitute!

[Max]

Hello again, 

Is there any news about this issue?

Thank you very much.

Regards.

[Kim Nilsson]

This doesn't work yet.
I get data until 2021-09-05, but nothing newer than that.
There should be data from pretty much every day since then, up until this and last week.

I have almost 700 users with missing data.

[Max]

Hello,

I´m noticing that deletion of suspended users process still isn´t working, so I guess that Google isn´t very concerned about the change in API that broke this.

Do you think it may be any workaround or cheat to achieve this in any other way? Or do we are on a dead end until Google does anything?

Thank you.

Regards.

[Kim Nilsson]

Max, sorry for not updating the thread.

This has been working for quite a while now, since mid-October.

[Kim Nilsson]

I just created three, lists, one for users suspended more than 180 days ago, one for 80 days ago and one for 8 days ago.

They all display different amounts of users. So it's working.

[Max]

Hello,

Our script is still failing, this is the output:

C:\GAMADV-XTD3>gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d" report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./scripts/deleteSuspendedUsers.csv
Getting Reports for all users

Got 8193 Reports for all users on 2023-01-15...
Traceback (most recent call last):
  File "__init__.py", line 60842, in ProcessGAMCommand
  File "__init__.py", line 11914, in doReport
  File "__init__.py", line 11530, in processUserUsage
KeyError: 'stringValue'

C:\GAMADV-XTD3>

We are using version GAMADV-XTD3 6.16.18 but this script was working with older versions.

 

What can be going wrong?

Thank you.

Regards

[Ross Scroggs]

Max,

6.16.18 is a hundred versions out of date. Update, the current version is 6.30.15.

After updating, do the following:

gam update project

gam oauth create

gam user us...@domain.com check serviceaccount

Ross

----

Ross Scroggs

Ross.S...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/52050d18-c98f-4592-a9af-128e30bbe55cn%40googlegroups.com.

[Max]

Hello,

Updating to latest version did the trick, the script is working again.

Thank you!

Regards

[Clint McGaha]

How can I get this filter to work, but only for a specific OU.

We delete students after a period of time, but not staff (for legal/compliance reasons).

C:\GAMADV-XTD3>gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-180d" report user user all parameters accounts:is_suspended,accounts:disabled_reason > ./scripts/deleteSuspendedUsers.csv

I'm wanting this part added, but struggling with the syntax as I am still a relative noob with GAMADV commands:

query "orgUnitPath='/CleverArchive/Students'"

[Ross Scroggs]

Replace:

user all

with

Ou "/Path/to/OU"

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/e1d28d69-706e-427b-88f8-2d98d872cb31n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Clint McGaha]

Man, I was all around it but not quite there, thanks Ross.

Here are the 2 commands I will be using in a bat file to automate deleting student accounts suspended 1yr ago+ that are already scripted to be put in the suspended students archive.  

gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-365d" report user OU "/CleverArchive/Students" parameters accounts:is_suspended,accounts:disabled_reason > C:\GAMWork/students365susp.csv

gam csv C:\GAMWork/students365susp.csv gam delete user "~primaryEmail"

[Ross Scroggs]

Clint,

I would use redirect csv instead of > to specify the output file

gam config csv_output_row_filter "accounts.is_suspended:boolean:true,accounts.disabled_time:date<-365d" redirect csv C:\GAMWork/students365susp.csv report user ou "/CleverArchive/Students" parameters accounts:is_suspended,accounts:disabled_reason

Header is email, not primaryEmail; make a log

gam redirect stdout C:\GAMWork/students365deleted.txt multiprocess redirect stderr stdout csv C:\GAMWork/students365susp.csv gam delete user "~email"

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/61a5b578-85cf-45ed-b4d0-7dfbbdae3fb7n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Daniel]

If it helps anyonee, I do it that way:

$targetDate = (Get-Date).AddDays(-90) | Get-Date -Format yyyy-MM-ddThh:mm:ss.000Z
gam report users filter accounts:last_login_time<$targetDate,accounts:is_suspended==true,accounts:gmail_used_quota_in_mb<100,accounts:drive_used_quota_in_mb<100 |gam csv - gam delete user ~email

I use GAM, not GAMADV-XTD3