Can I query system activity explores using sdk method?

[sho]

Hi all,
 

We’re working on building a data ingestion in java which pulls data from our system_activity explores using the sdk (eg: history, event, contentUsage).
Been experimenting this method all_lookml_models(), with our history explore, but that doesn’t seem to contain data of the view, but instead only the metadata.

Thanks in advance for the help!
 

[IceS2]

Hey @sho, were you able to do this?

thanks a lot (=

[Dr__Strangelove]

I provided the following query definition

{                     
  "view": "history",
  "fields": [
    "user.name",               
    "history.approximate_usage_in_minutes",
    "history.query_run_count",
    "user.id"                                                                                        
  ],            
  "pivots": null,     
  "fill_fields": null,
  "filters": {
    "history.is_single_query": "Yes", 
    "history.completed_date": "7 days",
    "user_facts.is_looker_employee": "No"
  },                                            
  "filter_expression": null,
  "sorts": [ "history.approximate_usage_in_minutes desc" ],
  "limit": "10",
  "column_limit": null,
  "total": null,
  "row_total": null,          
  "subtotals": null,                                                                                                                                                                                        
  "vis_config": {                                                                                                                                                                                         
    "show_view_names": true,
    "show_row_numbers": true, 
    "show_view_names": true,
    "show_row_numbers": true,
    "truncate_text": true,
    "size_to_fit": true,
    "table_theme": "white",
    "type": "looker_grid",
    "series_labels": {
      "user.name": "Name",
      "history.approximate_usage_in_minutes": "Minutes",
      "history.query_run_count": "Queries",
      "percent_under_10s": "% Queries Under 10s"
    },
    "note_state": "collapsed",
    "note_display": "hover",
    "note_text": "Top users by approximate minutes used and query count in the last 7 days.",
    "defaults_version": 1
  },
  "filter_config": {
    "history.is_single_query": [
      {
        "type": "is",
        "values": [ { "constant": "Yes" }, { } ],
        "id": 0,
        "error": false
      }
    ],
    "history.completed_date": [
      {
        "type": "past",
        "values": [ { "constant": "7", "unit": "day" }, { } ],
        "id": 1,
        "error": false
      }
    ],
    "user_facts.is_looker_employee": [
      {
        "type": "is",
        "values": [ { "constant": "No" }, { } ],
        "id": 2,
        "error": false
      }
    ]
  },
  "model": "system__activity",
  "dynamic_fields": "[{\"table_calculation\":\"percent_under_10s\",\"label\":\"Percent Under 10s\",\"expression\":\"${history.queries_under_10s} / ${history.query_run_count}\",\"value_format\":null,\"value_format_name\":\"percent_0\",\"_kind_hint\":\"measure\",\"_type_hint\":\"number\",\"is_disabled\":true}]"
}

to the "Run Inline Query" API endpoint https://developers.looker.com/api/explorer/4.0/methods/Query/run_inline_query and it works fine.

The easy way to get the query definition is to open the built in system activity dashboard, then choose "Explore From Here" to start exploring. Then save the explore as a Look. Now use the "Get Look" API endpoint to retrieve the json representation of the Look. That will contain the JSON representation of the query. You can use that JSON or modify it then use it with the run_inline_query API endpoint.

[Dr__Strangelove]

The run_inline_query API endpoint requires two parameters, the result format and the query definition. The query definition is given above, but it can be simplified for testing. (Note that the field labeled "view" is actually the explore. This misnaming is the result of the query json being available very early in Looker's history.)

At a minimum the query needs a model, explore, and a field list. So the simplified query would contain at least this...

{                     
  "model": "system__activity",
  "view": "history",
  "fields": [
    "user.name",               
    "history.approximate_usage_in_minutes",
    "history.query_run_count",
    "user.id"                                                                                        
  ]
}

The json for the query is passed as-is, not inside a set of quotes or anything like that (unless we need the quotes to protect it from interpretation by bash). In other words, it is a json object being passed to the API, not a string that contains a json object.

The simplest response format can be "json"/"json_detail" or "csv". The "txt" response is tab separated values. "md" gives a markdown style table and "html" a simple html style table. "xlsx" provides a Microsoft Excel spreadsheet. The response can also be "sql" to see the raw sql used for the query without actually running it. "jpeg" or "png" will give you a binary response with a visualization.

If this is not working, please provide the error message and http response code.

You can even do this with curl if you have the authentication token for the header. Something like this...

curl -X POST -H "Authorization: Bearer <AUTH-TOKEN-HERE>" \
  -d '{ "model": ... "view": ... "fields": ... }' \
  https://looker.example.com:19999/api/4.0/queries/run/json

If you use gazer something like this will work...

$ gzr query runquery '
{                     
  "model": "system__activity",
  "view": "history",
  "fields": [
    "user.name",               
    "history.approximate_usage_in_minutes",
    "history.query_run_count",
    "user.id"                                                                                        
  ]
}' --format csv --host looker.example.com

Note that in both these cases the json document is included in single quotes so that we don't have to escape all the double quotes on the command line.