Playing with G Suite MDM mobile device data using Go

Our desktop support & G Suite admin folks needed a simple, fast command-line tool to query basic info about our company’s mobile devices (which are all managed using G Suite’s built-in MDM).

So I wrote one.

Since this tool needs to be run via command-line, it can’t use any interactive or browser-based authentication, so we need to use a service account for authentication.

Pre-requisites (GCP & G Suite):

  • Create a service account in your G Suite domain (Google Cloud Console –> IAM & Admin –> Service Accounts)
  • Be sure that you have checked the “Enable G Suite Domain-wide Delegation” box as this will give your new service account the appropriate permissions to query the G Suite Admin API
  • Download the service account JSON credentials file. Open it up and find the client_id. Make a note of it!
  • Now we need to authorize the client_id of your service account for the necessary API scopes. In the Admin Console for your G Suite domain (Admin Console –> Security –> Advanced Settings –> Authentication –> Manage API Client Access), add your client_id in the “Client Name” box, and add 
    https://www.googleapis.com/auth/admin.directory.device.mobile.readonly
    in the “One or more API scopes” box
  • Click the “Authorize” button when you’re finished

Pre-requisites (Go)
You’ll need to “go get” a few packages:

  • go get -u golang.org/x/oauth2/google
  • go get -u google.golang.org/api/admin/directory/v1
  • go get -u github.com/dustin/go-humanize

Pre-requisites (Environment Variables)
Because it’s never good to store runtime configuration within code, you’ll notice that the code references several environment variables. Setup them up to suit your preference but something like this will do:

export GSUITE_COMPANYID="A01234567"
export SVC_ACCOUNT_CREDS_JSON="/home/rickt/dev/adminsdk/golang/creds.json
export GSUITE_ADMINUSER="foo@bar.com"

And finally, the code
Gist URL: https://gist.github.com/rickt/199ca2be87522496e83de77bd5cd7db2

package main
// https://rickt.org/2019/01/08/playing-with-g-suite-mdm-mobile-device-data-using-go/
import (
"context"
"errors"
"flag"
"fmt"
"github.com/dustin/go-humanize"
"golang.org/x/oauth2/google"
"google.golang.org/api/admin/directory/v1"
"io/ioutil"
"log"
"os"
"strings"
"time"
)
// runtime usage:
// Usage of ./mdmtool:
// -all
// List all MDM mobile devices
// -imei string
// IMEI of mobile device to search fors
// -name string
// Name of mobile device owner to search for
// -sn string
// Serial number of mobile device to search for
// -status string
// Search for mobile devices with specific status
var (
adminuser = os.Getenv("GSUITE_ADMINUSER")
companyid = os.Getenv("GSUITE_COMPANYID")
credsfile = os.Getenv("SVC_ACCOUNT_CREDS_JSON")
devices *admin.MobileDevices
row int = 0
scopes = "https://www.googleapis.com/auth/admin.directory.device.mobile.readonly"
searchtype = "all" // default search type
sortorder = "name" // default sort order
)
// flags
var (
all *bool = flag.Bool("all", false, "List all MDM mobile devices")
imei *string = flag.String("imei", "", "IMEI of mobile device to search for")
name *string = flag.String("name", "", "Name of mobile device owner to search for")
sn *string = flag.String("sn", "", "Serial number of mobile device to search for")
status *string = flag.String("status", "", "Search for mobile devices with specific status")
)
// helper func to check errors
func checkError(err error) {
if err != nil {
log.Fatal(err)
}
}
// check the command line arguments/flags
func checkFlags() (string, error) {
// parse the flags
flag.Parse()
// show all devices?
if *all == true {
// -all shows ALL devices so doesn't work with any other option
if *name != "" || *imei != "" || *sn != "" || *status != "" {
return "", errors.New("Error: -all cannot be used with any other option")
}
return "all", nil
}
// name search
if *name != "" {
// don't use -name and any other search option
if *imei != "" || *sn != "" || *status != "" {
return "", errors.New("Error: cannot use -name and any other search options")
}
return "name", nil
}
// IMEI search
if *imei != "" {
// don't use -imei and any other search option
if *name != "" || *sn != "" || *status != "" {
return "", errors.New("Error: cannot use -imei and any other search options")
}
return "imei", nil
}
// Serial number search
if *sn != "" {
// don't use -sn and any other search option
if *name != "" || *imei != "" || *status != "" {
return "", errors.New("Error: cannot use -sn and any other search options")
}
return "sn", nil
}
// Status search
if *status != "" {
// don't use -status and any other search option
if *name != "" || *imei != "" || *sn != "" {
return "", errors.New("Error: cannot use -status and any other search options")
}
return "status", nil
}
// invalid search
if *all == false && *name == "" && *imei == "" && *sn == "" && *status == "" {
flag.PrintDefaults()
return "", errors.New("Error: no search options specified")
}
return "", nil
}
// helper function to do a case-insensitive search
func ciContains(a, b string) bool {
return strings.Contains(strings.ToUpper(a), strings.ToUpper(b))
}
func main() {
// check the flags to determine type of search
searchtype, err := checkFlags()
checkError(err)
// read in the service account's JSON credentials file
creds, err := ioutil.ReadFile(credsfile)
checkError(err)
// create JWT config from the service account's JSON credentials file
jwtcfg, err := google.JWTConfigFromJSON(creds, scopes)
checkError(err)
// specify which admin user the API calls should "run as"
jwtcfg.Subject = adminuser
// make the client using our JWT config
gc, err := admin.New(jwtcfg.Client(context.Background()))
checkError(err)
// get the data
devices, err = gc.Mobiledevices.List(companyid).OrderBy(sortorder).Do()
checkError(err)
// iterate through the slice of devices
for _, device := range devices.Mobiledevices {
// what type of search are we doing?
switch searchtype {
// show all mobile devices
case "all":
row++
printDeviceData(device)
// name search: iterate through the slice of names associated with the device
case "name":
for _, username := range device.Name {
// look for the specific user
if ciContains(username, *name) {
row++
printDeviceData(device)
}
}
// IMEI search: look for a specific IMEI
case "imei":
// remove all spaces from IMEI then search for specific IMEI
// IMEI can be misreported via MDM with spaces, so remove them
if strings.Replace(device.Imei, " ", "", -1) == strings.Replace(*imei, " ", "", -1) {
row++
printDeviceData(device)
break
}
// serial number search: look for a specific serial number
// SN can be misreported via MDM with spaces, so remove them
case "sn":
if strings.Replace(device.SerialNumber, " ", "", -1) == strings.Replace(*sn, " ", "", -1) {
row++
printDeviceData(device)
break
}
// Status search
case "status":
if ciContains(device.Status, *status) {
row++
printDeviceData(device)
}
}
}
// if 0 rows returned, exit
if row == 0 {
log.Fatal("No mobile devices match specified search criteria")
} else {
// print the final/closing line of dashes
printLine()
}
fmt.Printf("%d row(s) of mobile device data returned.\n", row)
}
// func to print out device data
func printDeviceData(device *admin.MobileDevice) {
// print header only on first row of data
if row == 1 {
printHeader()
}
// convert last sync string to time.Time so we can humanize the last sync timestamp
t, err := time.Parse(time.RFC3339, device.LastSync)
checkError(err)
fmt.Printf("%-16.16s | %-14.14s | %-16.16s | %-18.18s | %-13.13s | %-18.18s | %-20.20s\n", device.Model, device.Os, strings.Replace(device.SerialNumber, " ", "", -1), strings.Replace(device.Imei, " ", "", -1), device.Status, humanize.Time(t), device.Name[0])
return
}
// func to print a line
func printLine() {
// print a line
fmt.Printf("—————–+—————-+——————+——————–+—————+——————–+—————\n")
}
// func to print the header
func printHeader() {
// print the first line of dashes
printLine()
// print header line
fmt.Printf("Model | OS & Version | Serial # | IMEI | Status | Last Sync | Owner\n")
// print a line of dashes under the header line
printLine()
}
// EOF
view raw gsuitemdmtool.go hosted with ❤ by GitHub

Automated insert of CSV data into Bigquery via GCS bucket + Python

i wanted to try out the automatic loading of CSV data into Bigquery, specifically using a Cloud Function that would automatically run whenever a new CSV file was uploaded into a Google Cloud Storage bucket.

it worked like a champ. here’s what i did to PoC:

  1. generate a CSV file with 1000 lines of dummy data via https://www.mockaroo.com/ with their default schema so it looks like:
$ head -3 testdata.csv

id,first_name,last_name,email,gender,ip_address

1,Andres,Berthot,aberthot0@pbs.org,Male,104.204.241.0

2,Iris,Zwicker,izwicker1@icq.com,Female,61.87.224.4
  • make a test Google Cloud Storage bucket:
     $ gsutil mb gs://csvtestbucket
  • install the necessary python bits & pieces:
    $ pip3 install google-cloud-bigquery --upgrade
  • make a Bigquery dataset:
    $ bq mk --dataset rickts-dev-project:csvtestdataset
  • make a table within that dataset to match the CSV schema:
    • $ bq mk -t csvtestdataset.csvtable \

id:INTEGER,first_name:STRING,last_name:STRING,email:STRING,gender:STRING,ip_address:STRING
  • eyeball the table in the Bigquery dataset and verify it is clean and fresh:
  • now its time to write some python. here’s mine:
    • import os
    from google.cloud import bigquery
    def csv_loader(data, context):
    client = bigquery.Client()
    dataset_id = os.environ['DATASET']
    dataset_ref = client.dataset(dataset_id)
    job_config = bigquery.LoadJobConfig()
    job_config.schema = [
    bigquery.SchemaField('id', 'INTEGER'),
    bigquery.SchemaField('first_name', 'STRING'),
    bigquery.SchemaField('last_name', 'STRING'),
    bigquery.SchemaField('email', 'STRING'),
    bigquery.SchemaField('gender', 'STRING'),
    bigquery.SchemaField('ip_address', 'STRING')
    ]
    job_config.skip_leading_rows = 1
    job_config.source_format = bigquery.SourceFormat.CSV
    # get the URI for uploaded CSV in GCS from 'data'
    uri = 'gs://' + os.environ['BUCKET'] + '/' + data['name']
    # lets do this
    load_job = client.load_table_from_uri(
    uri,
    dataset_ref.table(os.environ['TABLE']),
    job_config=job_config)
    print('Starting job {}'.format(load_job.job_id))
    print('Function=csv_loader, Version=' + os.environ['VERSION'])
    print('File: {}'.format(data['name']))
    load_job.result() # wait for table load to complete.
    print('Job finished.')
    destination_table = client.get_table(dataset_ref.table(os.environ['TABLE']))
    print('Loaded {} rows.'.format(destination_table.num_rows))
    view raw main.py hosted with ❤ by GitHub
  • because you don’t want to hardcode things like bucket/table/dataset names in code, create a yaml file that will store your deployment-specific configuration in environment variables
    •  BUCKET: csvtestbucket

DATASET: csvtestdataset

TABLE: csvtable

VERSION: v14
  • create a requirements.txt file for the necessary imports
    • google-cloud

google-cloud-bigquery
  • create a .gcloudignore file so that your yaml or CSV files will not be deployed into GCP
    • *csv

*yaml
  • at this point, your folder should look something like this:
    • $ ls

env.yaml  main.py  requirements.txt  testdata.csv
  • now we are ready to deploy the cloud function, we will add a trigger on the storage bucket that will fire every time a new file is added to the bucket. here we are creating a cloud function named “csv_loader”:
    • $ gcloud beta functions deploy csv_loader \

--runtime=python37 \

--trigger-resource=gs://csvtestbucket \

--trigger-event=google.storage.object.finalize \

--entry-point=csv_loader \

--env-vars-file=env.yaml
  • ok, the function is deployed, yay! copy your test data CSV into the test bucket:
    • $ gsutil cp testdata.csv gs://csvtestbucket/

Copying file://testdata.csv [Content-Type=text/csv]...

- [1 files][ 60.4 KiB/ 60.4 KiB]

Operation completed over 1 objects/60.4 KiB.
  •  now that we have copied a CSV file in the bucket, the function should fire! check the cloud function logs:
    • $ gcloud functions logs read

[ ... snipped for brevity ... ]

D      csv_loader  274732139359754  2018-10-22 20:48:27.852  Function execution started

I      csv_loader  274732139359754  2018-10-22 20:48:28.492  Starting job 9ca2f39c-539f-454d-aa8e-3299bc9f7287

I      csv_loader  274732139359754  2018-10-22 20:48:28.492  Function=csv_loader, Version=v14

I      csv_loader  274732139359754  2018-10-22 20:48:28.492  File: testdata2.csv

I      csv_loader  274732139359754  2018-10-22 20:48:31.022  Job finished.

I      csv_loader  274732139359754  2018-10-22 20:48:31.136  Loaded 1000 rows.

D      csv_loader  274732139359754  2018-10-22 20:48:31.139  Function execution took 3288 ms, finished with status: 'ok'

    looks like the function ran as expected!

  • lets eyeball the bigquery table again, and see if the row count has changed
    • $ bq show csvtestdataset.csvtable

Table rickts-dev-project:csvtestdataset.csvtable

Last modified           Schema           Total Rows   Total Bytes   Expiration   Time Partitioning   Labels

----------------- ----------------------- ------------ ------------- ------------ ------------------- --------

22 Oct 13:48:29   |- id: integer          1000         70950

|- first_name: string

|- last_name: string

|- email: string

|- gender: string

|- ip_address: string

    great! there are now 1000 rows. looking good.

  • as a final check, lets compare the first 3 rows of the CSV
    • $ egrep '^[1,2,3],' testdata.csv

1,Andres,Berthot,aberthot0@pbs.org,Male,104.204.241.0

2,Iris,Zwicker,izwicker1@icq.com,Female,61.87.224.4

3,Aime,Gladdis,agladdis2@hugedomains.com,Female,29.55.250.191

    with the first 3 rows of the bigquery table

    $ bq query 'select * from csvtestdataset.csvtable \

where id IN (1,2,3)'

Waiting on bqjob_r6a3239576845ac4d_000001669d987208_1 ... (0s) Current status: DONE

+----+------------+-----------+---------------------------+--------+---------------+

| id | first_name | last_name |           email           | gender |  ip_address   |

+----+------------+-----------+---------------------------+--------+---------------+

|  1 | Andres     | Berthot   | aberthot0@pbs.org         | Male   | 104.204.241.0 |

|  2 | Iris       | Zwicker   | izwicker1@icq.com         | Female | 61.87.224.4   |

|  3 | Aime       | Gladdis   | agladdis2@hugedomains.com | Female | 29.55.250.191 |

+----+------------+-----------+---------------------------+--------+---------------+

    and whaddyaknow, they match! w00t!

    proof of concept: complete!

    conclusion: cloud functions are pretty great.