Moving over from the conversation on Slack:

I think the key fact here is that cu_log_event and cu_log serve very different purposes despite the confusing similarity in their names.

cu_log_event stores the IP and user-agent used to perform a log event (e.g. if I move a page, my IP and user-agent get stored) so that checkusers can use it for sockpuppet investigations. Since this is a supplement the information in logging which details what happened in the log event, naturally it has the foreign key to join with logging.

However, cu_log records when checkusers use their privileges (e.g. if you are a checkuser and look up my IP, that event gets stored in cu_log) so that those actions can be scrutinized if necessary. The information is stored in cu_log instead of in the logging table, so the rows here don't correspond to any rows in logging and therefore there is no foreign key.

Interestingly, revealing the IPs of temp accounts apparently is stored in the logging with log_type = 'checkuser-temporary-account'. But that strengthens my point, because there is no similar log_type='checkuser' that you could join cu_log to.

Can you please associate one or more active project tags with this task (via the Add Action... → Change Project Tags dropdown)? That will allow to see a task when looking at project workboards or searching for tasks in certain projects, and get notified about a task when watching a related project tag. Thanks!

Having done some searching, I'm guessing that T419434: [SPIKE] Mobile tags for log actions is the the motivation behind this?

Based on that, I can give some additional info which may be helpful:

• We do not store whether a user was using a mobile or desktop device for actions that result in a cu_log row being created.
  • If this is a need for future actions, then instrumentation for loads of these pages would probably be quicker to implement than the database migration of cu_log rows to the logging table
• The web request logs for POST requests to Special:CheckUser should give you a good approximation of this data, as generally one POST request corresponds to one cu_log row. However, that is not easy to query and would have more pitfalls than checking the tags on a log entry

Thanks so much for all the info @Dreamy_Jazz. I've added more context to the description, so that my use case and its connection to OKR work is clearer.

Just to be clear,

• We do not store whether a user was using a mobile or desktop device for actions that result in a cu_log row being created.

Does this mean (1) we do not store whether the user running the check did so via mobile or desktop, and/or (2) these checks do not receive a "mobile" or "mobile edit" change tag when performed on mobile?

In T419459#11694193, @CMyrick-WMF wrote:

Does this mean (1) we do not store whether the user running the check did so via mobile or desktop, and/or (2) these checks do not receive a "mobile" or "mobile edit" change tag when performed on mobile?

Yes, both of these are the case.

Essentially we have no data on whether the user running the check was using a mobile device or desktop device. The only way to get this data that I can think of is the web request logs or for a small subset of the recent rows in cu_log the /analytics/legacy/specialinvestigate/1.1.0 eventlogging schema may have this data(?)

For any large scale collection of this data, we would need to migrate cu_log to the logging table which is a very large technical effort

If a general overview of desktop vs mobile is useful, a vast majority of the rows in cu_log will have been made by users either using a desktop device, as the tools are not well optimised for mobile devices. Of those using mobile, most will be using the desktop view

In T419459#11691177, @Aklapper wrote:

Can you please associate one or more active project tags with this task (via the Add Action... → Change Project Tags dropdown)? That will allow to see a task when looking at project workboards or searching for tasks in certain projects, and get notified about a task when watching a related project tag. Thanks!

Definitely! Done.

In T419459#11694212, @Dreamy_Jazz wrote:

If a general overview of desktop vs mobile is useful, a vast majority of the rows in cu_log will have been made by users either using a desktop device, as the tools are not well optimised for mobile devices. Of those using mobile, most will be using the desktop view

Yes, we figured that would be the case. However, we'd like to get a baseline, so that as (potential) tool improvements are made, we can track the number of users doing checks on mobile.

In T419459#11694199, @Dreamy_Jazz wrote:

In T419459#11694193, @CMyrick-WMF wrote:

Does this mean (1) we do not store whether the user running the check did so via mobile or desktop, and/or (2) these checks do not receive a "mobile" or "mobile edit" change tag when performed on mobile?

Yes, both of these are the case.

Essentially we have no data on whether the user running the check was using a mobile device or desktop device. The only way to get this data that I can think of is the web request logs or for a small subset of the recent rows in cu_log the /analytics/legacy/specialinvestigate/1.1.0 eventlogging schema may have this data(?)

For any large scale collection of this data, we would need to migrate cu_log to the logging table which is a very large technical effort

Understood. Thank you for the explanations!

However, we'd like to get a baseline, so that as (potential) tool improvements are made, we can track the number of users doing checks on mobile.

Sure. For generating this data, I would recommend that this task be re-written as a feature request to add/update instrumentation (and then reopen this task too). This instrumentation would indicate whether mobile devices are being used to run the check

Implementing that instrumentation would be a lot easier than adding change_tag support (change_tag support for cu_log probably requires it's own OKR hypothesis to do properly).

I am not sure that Product Safety and Integrity will have the time to add this instrumentation, so if this is something you would need Product Safety and Integrity to do I'd recommend coordinating with @OKryva-WMF, @EMill-WMF and @Rsilvola to work how this might fit in to Product Safety and Integrity work.