Grab 'n Go Reports

[temple....@hackney.gov.uk]

Hi - I'm not a SQL developer ( I can understand the code though ) ... I was wondering if anyone might be able to share some SQL code that I'd be able to plug in to BigQuery to get some usage reports?  I'd also be interested in the code that returns the survey answers, if someone's done it already?

Thank you as always ...

Temple

[temple....@hackney.gov.uk]

A little more information - using a very simple query, I can use

Enter code here...SELECT * FROM `hackney-loaner-dev.loaner.Question` LIMIT 1000

and that returns all the records but I can't see the responses .... I can export the result to a json file then have a look at it ....

I can see the following:

{"ndb_key":"Key('Question', 5641906755207168)","timestamp":"2019-03-29 18:46:00 UTC","actor":"Loaner Role","method":"submit","summary":"Filing survey question response.","entity":{"question_text":"How was your experience with this loaner device?","enabled":true,"answers":[{"text":"It was great!","more_info_enabled":false},{"text":"Could use some improvements.","more_info_enabled":true,"placeholder_text":"Tell us more..."},{"text":"I had trouble with it.","more_info_enabled":true,"placeholder_text":"Tell us more..."}],"more_info_text":"this is a test message for how was your experience","question_type":"RETURN","rand_weight":"1","response":{"text":"I had trouble with it.","more_info_enabled":true,"placeholder_text":"Tell us more..."}}}

what I don't know is how to build the select statement so it returns just the more_info_text highlighted

[temple....@hackney.gov.uk]

easy when you know how :)

Enter code here...SELECT entity.more_info_text FROM `hackney-loaner-dev.loaner.Question` where entity.more_info_text is not null LIMIT 1000

On Monday, 25 March 2019 12:08:56 UTC, temple...@hackney.gov.uk wrote:

[Joe Parente]

Hi Temple!

Here's a simple query that will pull survey results. You'll probably want to filter null values.

SELECT

  timestamp,

  entity.question_text,

  entity.response.text,

  entity.more_info_text

FROM

  `projectname.loaner.Question`

And here's another query that will give you loan totals by month:

SELECT

  TIMESTAMP_TRUNC(timestamp, MONTH) AS month,

  COUNT(*) total_loans

FROM

  `projectname.loaner.Device`

WHERE

  method = "loan_assign"

GROUP BY

  month

ORDER BY

  month

Hope this helps!

[temple....@hackney.gov.uk]

Hi Joe - thank you, I'll try those out.  I'll also post some other queries that I managed to create and I even managed to create a very simple dashboard using DataStudio

thank you very much.

Temple

On Monday, 1 April 2019 21:55:12 UTC+1, Joe Parente wrote:

Hi Temple!

Here's a simple query that will pull survey results. You'll probably want to filter null values.

SELECT

  timestamp,

  entity.question_text,

  entity.response.text,

  entity.more_info_text

FROM

  `projectname.loaner.Question`

And here's another query that will give you loan totals by month:

SELECT

  TIMESTAMP_TRUNC(timestamp, MONTH) AS month,

  COUNT(*) total_loans

FROM

  `projectname.loaner.Device`

WHERE

  method = "loan_assign"

GROUP BY

  month

ORDER BY

  month

Hope this helps!

[temple....@hackney.gov.uk]

I thought I was beginning to understand but now life has become very interesting ... I don't understand how the entities fit together ... I have written some simple queries, but I suddenly don't understand where this data is coming from.

For example,

/*

 * COUNT OF LOANS PER MONTH

 * returns 1049 rows, giving the number of loans
 * per month per device

 */

SELECT

  TIMESTAMP_TRUNC(timestamp, MONTH) AS month,

  entity.serial_number,

  COUNT(*) total_loans

FROM

  `myproject.loaner.Device`

WHERE

  method = "loan_assign"

GROUP BY

  month,serial_number

ORDER BY

  month

then there's

/*

 * LAST LOAN FOR ASSET NUMBER
 * returns for each asset number the date when it was loan_assigned

 *

 */    SELECT

      entity.serial_number,

      MAX(TIMESTAMP_TRUNC(timestamp,SECOND)) AS last_loan

      FROM

      `myproject.loaner.Device`

    WHERE method = "loan_assign" /* this may pull records for GnG devices that subsequently have been removed from the GnG OU */

    GROUP BY serial_number

then I started to look at the entity 'device' which has thousands of rows, with data about all of our devices, grab 'n go, not grab 'n go, chromeboxes .... everything in all OU's.

So now I'm baffled.  What is the query looking at?  Is it the entity called 'BigQueryRow' - which then looks up the other data in the other entities using the ndb_key? ...

I'm curious.

My aiming point is to creat a simple dashboard for each of our rack owners, so they can see the following:

• select only the devices for their rack (simply using the serial numbers)
• who has a device
• how many times has it been borrowed per month
• when it was last borrowed
• when it last sync/heartbeat
• the date of the last loan

-------------snip------------

[temple....@hackney.gov.uk]

Well, I'll continue talking to myself :)

I've written an interesting query, can anyone think of a better way of achieving this?

/*

 *

 * for each Chromebook serial number

 *     list the last person to borrow

 *     list the timestamp of when it was borrowed

 *     list the timestamp of when it last checked-in

 *

 */

SELECT loaner_serial_no, borrower, loan_assign_timestamp, last_heartbeat

  FROM

    (SELECT entity.serial_number as loaner_serial_no, max(entity.last_heartbeat) last_heartbeat

      FROM

      `loaner.Device`

      WHERE entity.current_ou = "GrabnGo/Dev/Default"

      GROUP BY entity.serial_number) heartbeats

    LEFT JOIN

    (SELECT

      entity.serial_number as serno,

      max(timestamp) as loan_assign_timestamp

      FROM

      `loaner.Device`

      WHERE

      method = "loan_assign"

      GROUP BY serno) loaned_group

      ON heartbeats.loaner_serial_no = loaned_group.serno

    LEFT JOIN

    (SELECT

      entity.assigned_user as borrower,

      timestamp as user_assign_timestamp

      FROM

      `hackney-loaner-dev.loaner.Device`

      WHERE

      method = "loan_assign"

      GROUP BY borrower, timestamp) borrowers

      ON loaned_group.loan_assign_timestamp = borrowers.user_assign_timestamp

[DJNinNZ]

Hi Temple,

that looks great. I'm just about to try and do this myself. should be interesting, a newbie to both SQL, BigQuery and DataStudio. never stop learning :)

[temp...@gmail.com]

go for it @djninnz!

I have some more code to post, I'll add it real soon now ... let us know how you get on

[temple....@hackney.gov.uk]

Here's a query that I use to find a list of locked Chromebooks

SELECT entity.serial_number, method, timestamp, entity.assigned_user

    FROM `loanerproject.loaner.Device` timestamps

INNER JOIN

/*

 * need to select the last record for a device, if its method is "lock"

*/

(SELECT entity.serial_number, max(timestamp) as tstamp

          FROM `loanerproject.loaner.Device`

          group by entity.serial_number) maxima

     ON timestamps.entity.serial_number = maxima.serial_number and timestamp = maxima.tstamp

     where method = "lock"

     order by serial_number

limit 100

here's a query that tells you which devices someone's returned and when

SELECT timestamp as returned, actor as borrower, method, entity.serial_number as serial_number FROM `loanerproject.loaner.Device`

where method = "_loan_return" and actor = "user...@domain.uk"

group by borrower, returned, method, serial_number

order by borrower, returned, serial_number

get the history for a single device, given the serial number

/*

 *

 * GET DEVICE HISTORY FOR A SINGLE DEVICE, GIVEN THE SERIAL NUMBER

 *

 */

SELECT entity.serial_number, method, timestamp, entity.assigned_user, actor

    FROM `loanerproject.loaner.Device`

    where entity.serial_number = "ABCDEFG1234567ABAB9999"

    order by timestamp