Sample SQL queries to query and export data from the database
- you can use SQL and get the data directly from the database
- or you can get it via web service. To get started, take a look at a sample project
We prepared a few SQL sample queries to get you started. Run the queries in ManicTimeReports database.
In attached files you will find four sample queries:
- Get application usage for all users with start and end time
- Get application usage by day for all users
- Get tags with start and end time
- Get tags total by day for all users
PostgreSQL sample queries
MS SQL sample queries
More information about the database structureUse only tables which start with Ar_.
Ar_Activity - a list of activities from all timelines (similar to what you see in the bottom left part when you open ManicTime client)
Ar_CommonGroup - a list of all groups from all timelines. Here you will find applications, web sites, documents... (similar to what you see in bottom right of ManicTime)
Ar_Group - Same as common group, only that some application or web site (for example Firefox) on Machine 1 will have a different id than Firefox on Machine 2. So unless you know exactly what you want, ignore this table and always use Ar_CommonGroup
Ar_Timeline - Timelines are strips of data you see in ManicTime client. By default each client has four timelines: Tags, Computer usage, Applications and Documents.
Each activity belongs to a timeline, same as in ManicTime. When you click on different timelines you get different data below. So this tells you to which timeline things belong to.
Ar_User - List of all users, you can use it to see which timeline belongs to which user.
Link productivity and custom categories to groups (use ar_CommonGroup)
Ar_Environment - device information, which computer is sending the data
Ar_Folder - In ManicTime client, you can join multiple groups into a folder.
Used for tags, each tag for example "Tag 1, Tag 2" can be from two groups (in this case group "Tag 1" and group "Tag 2")
Ar_CommonGroupList - use Ar_CommonGroupList, only use Ar_GroupList and Ar_GroupListItem if you really need to.
Map teams to users
While you can use ar_Activity for everything, we suggest you use these tables when doing reports for longer periods.
They already contain summation for Applications, Documents and Websites and will be much quicker.