Getting data from database
ManicTime Server stores a lot of information, which you can use in your existing applications. There are two ways in which you can connect to ManicTime Server:
- 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
SQLite sample queries
PostgreSQL sample queries
MS SQL sample queries
More information about the database structure
Use 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.