actiTIME time reporting – MySql data and reporting
We’re using actiTIME (free version) for several years now. We’re happy with this time reporting/tracking solution, the only short coming for us are the integrated reports/the possibilities to create custom reports. In this article we’re going to
- Get an overview of actiTIME and database components
- Get access to the database
- Capture queries made by the actiTIME application to the database
- Get data directly out of the database into Excel
BEFORE YOU START TO MODIFY ANYTHING -> ALWAYS MAKE SURE YOU HAVE A CURRENT BACKUP!
Get an overview of actiTIME and database components
The about box says
Looking for Windows Services we find these two:
Getting details (path to executable) of the actiTIME MySQL Service:
Get-WmiObject win32_service | ?{$_.Name -like '*actiTIME mySQL*'} | select Name, DisplayName, State, PathName | Out-GridView
So this is the MySQL Server Windows Service instance for actiTIME
“C:\Program Files (x86)\actiTIME\database\MySQL\bin\mysqld” –defaults-file=”C:\Program Files (x86)\actiTIME\database\MySQL\my-medium.ini” “actiTIME MySQL”
Let’s investigate “C:\Program Files (x86)\actiTIME\database\MySQL\my-medium.ini”
Ok, now we now:
- mySQL Service Database is being used
- TCP Connection using Port 27531
But how to connect? -> there’s an actitime.config file (“C:\Program Files (x86)\actiTIME\actitime.config”)
(i replaced the DBpasssword with the word REMOVED)
Get access to the database/login
Let’s try to connect using what we just discovered using “C:\Program Files (x86)\actiTIME\database\MySQL\bin\mysql.exe”
to get help about how to use the mysql.exe command, we can simply type:
“C:\Program Files (x86)\actiTIME\database\MySQL\bin\mysql.exe” –help
Let’s switch location to C:\Program Files (x86)\actiTIME\database\MySQL\bin then login to the actiTIME database using mysql.exe
cd "C:\Program Files (x86)\actiTIME\database\MySQL\bin"
mysql -u actitime -p -P 27351
let’s see what databases we have using: show databases;
Capture queries made by the actiTIME application to the database
Now we need to understand a little more about the database structure. we can make use of the general_log_file attribute which can be specified in the mysql.ini file -> in our case, this file is named: C:\Program Files (x86)\actiTIME\database\MySQL\my-medium.ini
Open this file in notepad++ (don’t/never use notepad because of the missing encoding-understanding -> i was reading this will finally be improved with newer windows 10 builds) but for the time being, use notepad++ to make sure you can read the file (visually) and also make sure you’re not corrupting any config files by using notepad and under the [mysqld] section, provide the following attribute and value combination:
general_log_file = ‘c:\\mysqllog.txt’
general_log = 1
You need to double backslash (escape) the path. Of course you can choose another location, this is just for illustration. Because the Windows Service is running as System-Account, it should have the privileges to write almost to any location on the file system…
Now let’s restart the actiTIME mySQL Windows service using PowerShell Command (start PowerShell elevated as Admin):
Restart-Service "actiTIME mySQL" -Force
Login to actiTIME and check the file C:\mysqllog.txt
Now let’s View Time-Track in actiTIME and see, what SQL is being generated/logged.
Clicking in View Time-Track in actiTIME results in the following SQL queries:
select record.actuals, record.comments, task.id, project.id, task.name, project.name, customer.name, task.completion_date, record.record_date from ( select tt_record.task_id, comments, actuals, record_date from tt_record left join user_task_comment on tt_record.task_id = user_task_comment.task_id and tt_record.user_id = user_task_comment.user_id and tt_record.record_date = user_task_comment.comment_date where tt_record.user_id = 2 and record_date >= '2020-05-01' and record_date <= '2020-05-31' union select user_task_comment.task_id, comments, actuals, comment_date from user_task_comment left join tt_record on user_task_comment.task_id = tt_record.task_id and user_task_comment.user_id = tt_record.user_id and user_task_comment.comment_date = tt_record.record_date where user_task_comment.user_id = 2 and comment_date >= '2020-05-01' and comment_date <= '2020-05-31' ) as record, project, customer, task where task.id = record.task_id and project.id = task.project_id and customer.id = project.customer_id order by task.name_lower asc, task.name asc, customer.name_lower asc, customer.name asc, project.name_lower asc, project.name asc
Time for HeidiSQL or MySQL Workbench
Sure mysql.exe is ok to quickly connect and execute some commands but.. a fully fledged GUI will help .. i mean, we’re on Windows 😉
Both applications HeidiSQL (Heidi, me as a swiss i ask myself about Peter ..) and MySQL Workbench can be found and installed using chocolatey:
choco mysql.workbench
choco find heidisql
Install one or both applications using choco install…
Start MySQL Workbench, create a connection
or HeidiSQL – which i prefer
Fill in username, password (can be found in actitime.config file, remember?) and tcp port 27351 in my case – can also be found in the actitime.config file.
Now paste the captured SQL statement in a query window, e voila:
You can use https://sqlformat.org/ to prettyfy your SQL code
SELECT record.actuals,
record.comments,
task.id,
project.id,
task.name,
project.name,
customer.name,
task.completion_date,
record.record_date
FROM
(SELECT tt_record.task_id,
comments,
actuals,
record_date
FROM tt_record
LEFT JOIN user_task_comment ON tt_record.task_id = user_task_comment.task_id
AND tt_record.user_id = user_task_comment.user_id
AND tt_record.record_date = user_task_comment.comment_date
WHERE tt_record.user_id = 2
AND record_date >= '2020-05-01'
AND record_date <= '2020-05-31'
UNION SELECT user_task_comment.task_id,
comments,
actuals,
comment_date
FROM user_task_comment
LEFT JOIN tt_record ON user_task_comment.task_id = tt_record.task_id
AND user_task_comment.user_id = tt_record.user_id
AND user_task_comment.comment_date = tt_record.record_date
WHERE user_task_comment.user_id = 2
AND comment_date >= '2020-05-01'
AND comment_date <= '2020-05-31' ) AS record,
project,
customer,
task
WHERE task.id = record.task_id
AND project.id = task.project_id
AND customer.id = project.customer_id
ORDER BY task.name_lower ASC,
task.name ASC,
customer.name_lower ASC,
customer.name ASC,
project.name_lower ASC,
project.name ASC
Get data directly out of the database into Excel
First we need to install MySQL for Excel
at the time of writing this post, the version was mysql-for-excel-1.3.8.
Trying to install, you might get confrontation with a prerequisite called Microsoft Visual Studio Tools for Office, or short VSTO.
in my case – with MySQL for Excel v 1.3.8 on a Windows 10 with Office 2019, i had to install VSTO 2010
https://www.microsoft.com/en-US/download/confirmation.aspx?id=48217
Once installed, open Excel, switch to Data tab and click on MySQL for Excel
The AddIn will open. Add a New Connection
And click on Test Connection
Then double-click the just created actiTime connection, select Schemas -> actitime -> next
and select the view vw_custprojecttaskcommentv2 -> then Import MySQL Data
The preview will open.. click on Import
there you go:
MySQL Remote connections
If you want to connect to the MySQL database, you’ll need to GRANT the actitime user to do so.
What i did -> stopped the actiTime MySQL Windows Service. In an elevated command prompt, i started mysqld with some parameters like:
cd "C:\Program Files (x86)\actiTIME\database\MySQL\bin"
mysqld --defaults-file="C:\Program Files (x86)\actiTIME\database\MySQL\my-medium.ini" --init-file=c:\MYSQLPWRESET.txt --consoleThe content of the file C:\MYSQLPWRESET.txt:
USE mysql;
UPDATE mysql.user SET Password = PASSWORD('REMOVED') WHERE User = 'root';
FLUSH PRIVILEGES;
Then connected the database instance using HeidiSQL. In a query window i exected the following statements to create a MySQL User named solvia with super admin privileges and the privilege/right to connect from everywhere (the’s the percent sign)
CREATE USER 'solvia'@'localhost' IDENTIFIED BY 'REMOVED';
GRANT ALL PRIVILEGES ON *.* TO 'solvia'@'localhost' WITH GRANT OPTION;
CREATE USER 'solvia'@'%' IDENTIFIED BY 'REMOVED';
GRANT ALL PRIVILEGES ON *.* TO 'solvia'@'%' WITH GRANT OPTION;
SHOW GRANTS FOR solvia;
FLUSH PRIVILEGES;
Alternative way to get root access to the actiTIME MySQL Database
- Stop your MySQL server completely. This can be done from services.msc
- Open your command prompt using cmd (elevated). Then go to your MySQL bin folder, such as C:\Program Files (x86)\actiTIME\database\MySQL\bin.
- Execute the following command in the command prompt: mysqld.exe -u root –skip-grant-tables
- Leave the current command prompt as it is, and open a new command prompt window.
- Go to your MySQL bin folder again.
- Enter “mysql” and press enter.
- You should now have the MySQL command prompt working. Type “use mysql;” so that we switch to the “mysql” database.
- Execute the following command to update the password:
UPDATE user SET Password = PASSWORD(‘your_new_passowrd’) WHERE User = ‘root’;
However, you can now run almost any SQL command that you wish.
After you are finished close the first command prompt, and type “exit;” in the second command prompt.