Use Looker System Activity data in our own LookML models

[benjikj-cera]

I am wanting to use the Looker System Activity models in our own model in LookML in order to combine it with other sources, but wondering if this is possible.

Specifically, one example of what I am trying to do is that I've got the BigQuery InfoSchema jobs pushing into Looker and that contains the labels on each job of Looker user id used. So I can see the BigQuery metrics by the Looker User ID, but I want to join this to the actual Looker metadata in order to pull back the Name of the Looker User ID.

This is just one example, and I may also want to try and do other things that could enrich the BigQuery data with the Looker metadata.

Are there some view names that I can use in my BigQuery explore, that connect to that backend Looker system activity data?

[andy4]

This is a great idea and has been requested a lot. For now, there is only an unsupported method available, which is documented here:
https://www.googlecloudcommunity.com/gc/Developing-Applications/Write-the-result-of-a-Looker-query-t...

I encourage you to file it as a feature request in the "Product Idea?" Option in the looker help UI (chat bubble with ? in top right) - you will almost certainly be able to search, find, and +1 another request

[benjikj-cera]

Thanks Andy, will pop it in Product Ideas.

Seems like for my very simple main use case of wanting a lookup of Looker User IDs, it's probably easiest to just maintain a GSheet lookup vs overcomplicating by setting up a pseudo-pipeline. But may look at the pseudo-pipeline if I have more complicated use cases.

FYI, I can't view the second link on the post there for the dynamic solution; access denied.

[Vegasnadia]

I need help too how can I fix it 

[andy4 · Accepted solution]

also me, seems it was removed.
Here's another solution I just found too! https://medium.com/plum-fintech/linking-looker-activity-with-bigquery-costs-d0aef48a082f

[benjikj-cera]

Thanks Andy, I think we did try this GSheet workflow initially, but I think we were stuck with issues about row limits (as we have up to 25k history slugs per day). However, I think we've actually recently discovered that's a simple permissions change, so this does actually seem to work pretty well now.

We did also actually get the Looker Action via Cloud Functions working, but that's slightly been put on hold as it seems that has a hard limit of 5000 rows, that we can't find any way round at all. Instead we were looking at an API option, but may put that on hold if this GSheets option carries on working well. The API option will be a bit more rigid as we won't need to be wary of full-refreshed and overwriting data, though.

[raulcruzv]

Probably your best bet here is creating your own set of data sets pulling information via the Looker API. You can query System Activity Explores via API requests and push that data into your own datalake. We implemented this method in our company to merge Looker System Activity data with CRM data.