How to write a query

Hello,

I’d like to query the av-server to tell me how much I’ve used a program in a specific amount of time. For example how much I’ve used notepad.exe in the last hour, possibly ignoring the AFK time. I’m trying to read the documentation, but I can’t figure out how to write the query. Can you help me?

Thank you

https://activitywatch.readthedocs.io/en/latest/querying-data.html#writing-a-query Sorry for the bad documentation of the queries, we’re porting aw-server to rust where there’s a lot of improvements to the queries so I’ve not put much time to improve the documentation as it’s not been much of a priority.

If you’d want to know how much time you spend in notepad.exe only without AFK filtering, this would be the query

events = query_bucket("aw-watcher-window_johan-laptop2");
events = filter_keyvals(events, "app", ["notepad.exe"]);
events = merge_events_by_keys(events, ["app"]);
RETURN = events;
  • First row gets all events from the bucket “aw-watcher-window_johan-laptop2”
  • Second row removes all events which do not has the key “app” set to “notepad.exe”.
  • Third row merges the list of all events by the value of the “app” field into one event for each unique value (and since we filtered for “notepad.exe” there should only be a single event)
  • Fourth row tells us what data should be responded

If you remove the second row you will get something very similiar to what the web-ui shows as the “app summary”, only thing different is that it ignores AFK time and doesn’t sort by duration.

If you only want the data for the last hour you filter the timeperiod with the starttime and endtime fields.

It’s very easy to quickly accomplish this with the cli.py script, for example I just put the query in a file and executed it with ./aw_client/cli.py query notepad_query.txt

Thank you for your answer, and thank you for the great work you’re doing. Reading from the documentation, I’d assume this is how you specify the date?

res = client.query(query, start + timedelta(seconds=1), now - timedelta(seconds=2))

What I’d like to do is querying the local server from a browser extension, if that has been implemented. If not I can just run another local Python server and use that as a middleman.

Something like that should work, yes!

Instead of making a middle-man you could go straight to aw-server if you want though, there’s even a aw-client-js library with support for queries.

Thank you, that’s perfect. I’m still having some issues because I don’t know how to write the timeperiods.

{
  "timeperiods": [
    "2019-01-01/2020-01-01"
  ],
  "query": [
    "events = query_bucket("aw-watcher-window_DESKTOP-PC");
events = filter_keyvals(events, "app", ["notepad.exe"]);
events = merge_events_by_keys(events, ["app"]);
RETURN = events;"
  ]
}

I’ve tried to write in other ways, but I can’t get it to work.

My guess is that you are forgetting to escape your double quotes?
This works fine on my computer (switched bucketname and notepad.exe with firefox because I’m running Linux so I can’t start notepad).

{
   "timeperiods": [
     "2019-01-01/2020-01-01"
   ],
   "query": [
     "events = query_bucket(\"aw-watcher-window_johan-laptop2\");",
     "events = filter_keyvals(events, \"app\", [\"firefox\"]);",
     "events = merge_events_by_keys(events, [\"app\"]);",
     "RETURN = events;"
   ]
 }

Gives me this return value

[[{"id": null, "timestamp": "2019-09-16T20:29:31.969000+00:00", "duration": 104028.84, "data": {"app": "firefox"}}]]

I was just too distracted to notice that, thank you again!