Multiple Report Filters

[Sam Woodhouse]

Hi, 

I'm trying on user accounts that haven't logged in for x number of days but not including accounts that are suspended. Now I can run this command and get all accounts not logged in before a date:

gam report users filters "accounts:last_login_time<2018-06-16T00:00:00.000Z" fields accounts:last_login_time

However, this is bringing up all the accounts that are currently suspended. Which I would like to exclude from this report. I have tried the following:

gam report users filters "accounts:last_login_time<2018-06-16T00:00:00.000Z, accounts:is_suspended==FALSE" fields accounts:last_login_time

As well as every other variation of the two filters I can think of, I'm just wondering if there is something I am missing, or is this just not possible to do. 

I've been through al the wiki pages for gam and GAMADV-X that are linked and I'm not seeing any way of doing this.

The ultimate goal is to run a python script to force logins for all users that haven't logged into their accounts for a give time frame. To try and verify that accounts are still in use and required.

Regards,

Sam

[Sam Woodhouse]

Anyone? :(

[Ross Scroggs]

Sam,

The queries work for me (n.b. you must remove the space after the comma in your query)

$ gam report users filters "accounts:last_login_time<2018-06-16T00:00:00.000Z,accounts:is_suspended==True" fields accounts:last_login_time > susp.lst

Getting all Users, may take some time on a large G Suite Account...

Getting Reports for all

Got 109 Reports...

$ gam report users filters "accounts:last_login_time<2018-06-16T00:00:00.000Z,accounts:is_suspended==False" fields accounts:last_login_time > ns.lst

Getting all Users, may take some time on a large G Suite Account...

Getting Reports for all

Got 445 Reports...

$ gam report users filters "accounts:last_login_time<2018-06-16T00:00:00.000Z" fields accounts:last_login_time > all.lst

Getting all Users, may take some time on a large G Suite Account...

Getting Reports for all

Got 554 Reports...

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/850f00a9-5c32-4296-a50c-da7ddf645ee8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sam Woodhouse]

Hi Ross,

Thanks! It was the space after the filters that was stopping it, of course the one thing I must not have tried.

If I wanted the report to be dynamic and run with the date being 60 days prior to the run date can I do that through GAM? Or if not can it be done through Bash variables? Or neither?

Kind Regards,

Sam

--

ross.s...@gmail.com

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.

[+KimNilsson]

Any progress on this?

[Bala Kumar]

Hi Sam,

It's working now. thanks !!

--

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/0d59b2ab-89cf-427c-a31b-ccd048802791%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Regards,

V.Bala Kumar.

[Bala Kumar]

Hey,

ignore that .. I replied to another comments

--

Regards,

V.Bala Kumar.

[Joetje F]

On linux you can use the date command:

date --date='-60 days' +'%FT%T.%3NZ'

The gam command would look like this:

gam report users filters "accounts:last_login_time<$(date --date='-60 days' +'%FT%T.%3NZ')" fields accounts:last_login_time

[Sam Woodhouse]

Hi Joetje, 

I'm on Mac if that makes a difference, but that date command doesn't seem to be returning anything for me. I've got the following, which provides the current date minus 30 days:

date -v -30d +'%Y-%m-%dT00:00:00.00Z'

Which provides the output as I would like to put it into the gam command, however using your example I can't seem to input the date correctly, not sure if there is anything glaringly obvious, but based on your example I tried the following:

gam report users filters 'accounts:last_login_time<$(date -v -30d +"%Y-%m-%dT00:00:00.00Z")' fields accounts:last_login_time

I've had to swap the quotes round, since GAM-ADV seems to only allow single quotes around filters in reporting commands now.

[+KimNilsson]

This feels like something that would work with a report of devices lastsync too?

Trying to find unused devices to disable, and deprovision.

[Sam Woodhouse]

I don't see why not, anywhere you can do a filter/sort by a date should be able to make use of it. 

[Ross Scroggs]

Sam/Joetje/Others,

In GAMADV-X(TD) this got added in 4.60.15:

Added filtertime <Time> option to gam report to allow times, usually relative, to be substituted into the filter <String> option.

If both filtertime and filter are specified, the filtertime value replaces the string #filtertime# in the filter <String>.

gam report users|user [todrive <ToDriveAttribute>*] [date <Date>] [nodatechange | (fulldatarequired all|<ReportsAppList>)]

[(user all|<UserItem>)|(select <UserTypeEntity>)] [filtertime <Time>] [filter|filters <String>] [fields|parameters <String>]

        [maxactivities <Number>] [maxresults <Number>]

gam report admin|calendar|calendars|drive|docs|doc|gplus|groups|group|logins|login|mobile|rules|tokens|token [todrive <ToDriveAttribute>*] [maxresults <Number>] [maxactivities <Number>]

        [([start <Time>] [end <Time>])|yesterday] [(user all|<UserItem>)|(select <UserTypeEntity>)]

        [event <String>] [filtertime <Time>] [filter|filters <String>] [ip <String>] [countsonly] [summary]

For example, this command reports on the users that haven't logged in in the last 5 years.

gam report users parameters accounts:last_login_time filters "accounts:last_login_time<#filtertime#" filtertime -5y

For example, this command reports on the users that haven't ever logged in.

gam report users parameters accounts:last_login_time filters "accounts:last_login_time==#filtertime#" filtertime never

I don't believe the following is not correct:

     I've had to swap the quotes round, since GAM-ADV seems to only allow single quotes around filters in reporting commands now.

All quoting is processed by the command interpreter, not Gam. You should surround the filter in double quotes and substrings in the filter with singlw quotes.

 

If you have a counter-example, please post and I'll evalutae it.

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/a1aa2660-a3b8-4844-afc1-6016c51c7feb%40googlegroups.com.

[Kim Nilsson]

Darn...

Got my hopes up, but then saw I couldn't find cros in the list of report targets. :-/

But, perhaps filtertime or similar can be used with cros commands? Or do we have to rely on shell commands?

/Kim

--

There is No Substitute!

google.com/+KimNilsson

[Ross Scroggs]

Kim,

What are you trying to do?

Ross

--

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFsE%2BdkG5kWk6qUThU%2B8n934K9zUKvEPVeCh04BXXXtOhA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Easily disable all devices with lastsync dates older than say six months, or a year.

Next step is, of course, to remove/deprovision devices older than that.

We have too many devices listed as "active", but the hardware is no longer in the organisation. Most likely in a scrap yard somewhere.

[Ross Scroggs]

Kim,

Coming soon:

Added querytime1 <Time> and querytime2 <Time> options to gam print cros/crosactivity/mobile to allow times,

usually relative, to be substituted into the query <QueryCrOS> and queries <QueryCrOSList> options.

The querytime1 <Time> value replaces the string #querytime1# in any queries.

The querytime2 <Time> value replaces the string #querytime2# in any queries.

For example, to print information about CrOS devices synced more than 30 days ago:

gam print cros query "sync:..#querytime1#" querytime1 -30d

For example, to print information about CrOS devices synced in the last 30 days:

gam print cros query "sync:#querytime1#.." querytime1 -30d

For example, to print information about CrOS devices synced between 45 days ago and 30 days ago:

gam print cros query "sync:#querytime1#..#querytime2#" querytime1 -45d querytime2 -30d

Ross

--

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFto%3DexC4yyNY-x0LUqMG6VNz98r%3DK98cx4jA%2B44CUvQCQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Nice!

Also so appreciated with the included explanatory examples.

[Sam Woodhouse]

Ross,

Is that something you can expand into the reporting commands? Would be useful to be able to specify a date range for last login time and I'm sure there are other use cases.

On Thursday, 20 September 2018 00:35:36 UTC+1, Ross Scroggs wrote:

Kim,

Coming soon:

Added querytime1 <Time> and querytime2 <Time> options to gam print cros/crosactivity/mobile to allow times,

usually relative, to be substituted into the query <QueryCrOS> and queries <QueryCrOSList> options.

The querytime1 <Time> value replaces the string #querytime1# in any queries.

The querytime2 <Time> value replaces the string #querytime2# in any queries.

For example, to print information about CrOS devices synced more than 30 days ago:

gam print cros query "sync:..#querytime1#" querytime1 -30d

For example, to print information about CrOS devices synced in the last 30 days:

gam print cros query "sync:#querytime1#.." querytime1 -30d

For example, to print information about CrOS devices synced between 45 days ago and 30 days ago:

gam print cros query "sync:#querytime1#..#querytime2#" querytime1 -45d querytime2 -30d

Ross

On Wed, Sep 19, 2018 at 12:50 PM Kim Nilsson <there.is.no...@gmail.com> wrote:

Easily disable all devices with lastsync dates older than say six months, or a year.

Next step is, of course, to remove/deprovision devices older than that.

We have too many devices listed as "active", but the hardware is no longer in the organisation. Most likely in a scrap yard somewhere.

/Kim

--

There is No Substitute!

google.com/+KimNilsson

--
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-manager+unsub...@googlegroups.com.

To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFto%3DexC4yyNY-x0LUqMG6VNz98r%3DK98cx4jA%2B44CUvQCQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

Ross Scroggs

ross.s...@gmail.com

[+KimNilsson]

Sam, that's where we came from. :-)

Look further up in the thread.

[Sam Woodhouse]

It is, but it's currently only limited (from what I can see) to passing a single variable through, so you couldn't (as an example) do:

accounts:last_login_time<#filtertime1# accounts:last_login_time>#filtertime2# filtertime1 -30d filtertime2 -60d

To find accounts who haven't logged in in the last 30-60 days, to prompt a login. Similar to the steps you would like to take to de-activate and deprov devices, we are looking to do a similar thing for users.

[Kim Nilsson]

Hmm, why would you keep users who didn't login further back than 60 days, but remove users that didn't login between 30-60 dagar.

/Kim

--

There is No Substitute!

google.com/+KimNilsson

--
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/5EWegfqnQC4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-man...@googlegroups.com.

To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/4d3a0188-796b-4f97-9ea9-d03497ef1f8e%40googlegroups.com.

[Sam Woodhouse]

So our intention is to run a script weekly to prompt accounts that haven't signed in between 30 and 60 days to sign in again. If they then do not sign in after 60 days, we will suspend the account.

On Thursday, 20 September 2018 10:04:26 UTC+1, +KimNilsson wrote:

Hmm, why would you keep users who didn't login further back than 60 days, but remove users that didn't login between 30-60 dagar.

/Kim

--

There is No Substitute!

google.com/+KimNilsson

On Thu, 20 Sep 2018 at 11:01, Sam Woodhouse <sam.wo...@coopdigital.co.uk> wrote:

It is, but it's currently only limited (from what I can see) to passing a single variable through, so you couldn't (as an example) do:

accounts:last_login_time<#filtertime1# accounts:last_login_time>#filtertime2# filtertime1 -30d filtertime2 -60d

To find accounts who haven't logged in in the last 30-60 days, to prompt a login. Similar to the steps you would like to take to de-activate and deprov devices, we are looking to do a similar thing for users.


On Thursday, 20 September 2018 09:42:59 UTC+1, +KimNilsson wrote:

Sam, that's where we came from. :-)

Look further up in the thread.

--
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/5EWegfqnQC4/unsubscribe.

To unsubscribe from this group and all its topics, send an email to google-apps-manager+unsub...@googlegroups.com.

[Kim Nilsson]

Gotcha!

[Ross Scroggs]

Sam/Kim/Others,

Coming soon, you can have as many querytime/filtertime values as you want 

Added querytime.* <Time> option to gam print cros/crosactivity/mobile to allow times,

usually relative, to be substituted into the query <QueryCrOS> and queries <QueryCrOSList> options.

The querytime.* <Time> value replaces the string #querytime.*# in any queries.

For example, to print information about CrOS devices synced more than 30 days ago:

gam print cros query "sync:..#querytime1#" querytime1 -30d

For example, to print information about CrOS devices synced in the last 30 days:

gam print cros query "sync:#querytime1#.." querytime1 -30d

For example, to print information about CrOS devices synced between 45 days ago and 30 days ago:

gam print cros query "sync:#querytime1#..#querytime2#" querytime1 -45d querytime2 -30d

Added filtertime.* <Time> option to gam report to allow times, usually relative, to be substituted into the filter <String> option.

The filtertime.* value replaces the string #filtertime.*# in the filter <String>.

gam report users|user [todrive <ToDriveAttribute>*] [date <Date>] [nodatechange | (fulldatarequired all|<ReportsAppList>)]

        [(user all|<UserItem>)|(select <UserTypeEntity>)] [filtertime.* <Time>] [filter|filters <String>] [fields|parameters <String>]

        [maxactivities <Number>] [maxresults <Number>]

gam report admin|calendar|calendars|drive|docs|doc|gplus|groups|group|logins|login|mobile|rules|tokens|token [todrive <ToDriveAttribute>*] [maxresults <Number>] [maxactivities <Number>]

        [([start <Time>] [end <Time>])|yesterday] [(user all|<UserItem>)|(select <UserTypeEntity>)]

        [event <String>] [filtertime.* <Time>] [filter|filters <String>] [ip <String>] [countsonly] [summary]

For example, this command reports on the users that haven't logged in in the last 5 years.

gam report users parameters accounts:last_login_time filters "accounts:last_login_time<#filtertime#" filtertime -5y

For example, this command reports on the users that haven't logged in in the last 30 to 60 days.

gam report users parameters accounts:last_login_time filters "accounts:last_login_time<#filtertime1# accounts:last_login_time>#filtertime2#" filtertime1 -30d filtertime2 -60d

For example, this command reports on the users that haven't ever logged in.

gam report users parameters accounts:last_login_time filters "accounts:last_login_time==#filtertime#" filtertime never

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 post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/548c6fb3-3187-4dd2-9772-14bbaa8a4eec%40googlegroups.com.