How to graze cats. The history of the construction of a control and accounting system for working hours for an IT company
This story is built on a series of successful coincidences in which, as it seems to us, we were able to recognize the right trend and create a product that could suit many people like us. Because ... however, first things first.
So, we are a company that provides its customers with high-tech services - outsourcing of IT expertise, remote support, custom software development, etc. etc. As for any other company, the issue of compliance with labor discipline and control of the presence of our highly qualified employees in the workplace is relevant for us. Tasks of this kind are relatively successfully solved in cases where employees a) work according to a strict schedule in the office and b) their attitude to what is being followed, can be neglected. That is, you can install traditional ACS at the entrance to the office, put the tracking software on office computers and quietly implement the functions of Big Brother. But in the case of working with an expert team, all these measures will work more likely in the red, and that's why.
Firstly, a typical employee of a high-tech company (hereinafter referred to as his “expert”) is a person who works not only at work and not only during traditional working hours. This is connected ... it can be a lot connected with something - with the actual need to carry out some work at night, with the difference of time zones with clients, in the end, with the fact that the peak of productivity of our ingenious developer falls on the interval between 23-00 and 4- 00, and at that time he works miracles. Accordingly, it is necessary to consider not the stay in the immediate vicinity of the working computer, but the actual work on it (possibly remotely from home).
Secondly, an expert is a freedom-loving creature. The very idea that a program is engaged solely in controlling its activities, acts on it demotivating, undermines the spirit of innovative teams and, accordingly, negatively affects labor productivity.
Thirdly, an expert is an expert with his professional ambitions. The existence of software that opposes him (the expert) can direct the talents of this person to the fight against this software. Thus, our expert at his workplace in his working time will spend energy ... on the struggle with the system of accounting for working time! Agree, something is wrong here =).
And, fourthly, high-tech companies themselves are suspicious of programs that collect user data. Who knows where then these data float away ...
In general, we were aware of the problem for ourselves, but, like a real “shoemaker without boots,” we were in no hurry to solve it. So far, our Client has not contacted us with the same problem. This is where the previously announced coincidences begin.
So, our client is a company that provides high-tech services to its clients. Namely, a startup specializing in the Dedicated Developers Team service (when programmers are leased to a customer). Little by little, the number of programmers in the client grew, and the question arose about labor discipline and control of working time. But the client did not want to use traditional means, for reasons completely coinciding with those described above. So the coincidence of needs was one hundred percent. With a proposal to solve this problem for him, the client turned to us, and we enthusiastically (foreseeing at least double benefit =)) began to investigate the issue.
It turned out that our client, like us, works mainly under Windows. And the applications with which he (like us!) Works are rather heavyweight, such as SharePoint. On a regular home laptop, and even more so on an iPad, you won’t work. Therefore, employees use their work machine even when they work from home - they connect to it through the Remote Desktop Gateway (RDG) service. Everything is like ours!
It turned out that both we and the customer use Skype for Business (formerly MS Lync) as an office telephone exchange and universal communication tool. This program, in addition to its immediate responsibilities, performs one more - it can at any time tell the status of the user's presence in the system. So, an employee always uses a working computer for work - Skype for Business always stands on it - Skype always knows the status of user presence ... A little mental effort - and the idea of collecting a history of status changes and obtaining a work schedule based on it was firmly established in our collective mind. It remains only to realize it, which we have begun.
Naturally, collecting statistics from the client did not make much sense, because it is not visible on which device the employee is currently working on. Therefore, it was necessary to look at the contents of the Skype server databases (we are, of course, talking about Skype4B corporate servers). The relevant information about the database structure of the Skype4B servers, although it was not documented, but access to it did not require any decryption or decompilation operations. Under cat there are some technical details for those interested.
Technical details
Plan:
There are two databases that we work with when monitoring status changes:
[rtc] - contains static information that does not change over time. This database contains information about users, contact details, profile photos, status messages, etc.
[rtcdyn] - contains dynamic information regarding changes in the status of clients (programs) that are connected to the Skype For Business server.
As mentioned above, this database contains dynamic information regarding changes in the status of clients (programs) that are connected to the Skype For Business server.
First of all, it should be agreed with the concept of “client”. First of all, this is the program or web browser from which the connection to the Skype For Business server takes place. “Client” requires a username and password for the connection and provides additional information regarding the device, time zone. It is important to understand that the information collected on the "Client" applies only to the "Client" and not the real user. Those. the information that we will collect depends on where the user launches the “Client”.
Consider an example:
All information from the "customers" are independent of each other.
After connecting to the rtcdyn database, we see several tables. Among them, you can find the RegistrarEndpoint table. Let's try to extract some information from this table.
After converting the received information, we can see information about the source of the connection to the Skype For Business server.
For instance:
From this line, you can extract the IP address of the device from which the “client” is connecting.
Similarly, you can extract information from the ClientApp column. This column contains information about the application from which the connection to the Skype For Business server itself occurs.
Examples of values that may be contained in ClientApp are given below:
UCCAPI / 15.0.4753.1000 OC / 15.0.4753.1000 (Skype for Business)
RTCC / 5.0.0.0 OWA / 15.00.1076.009
Based on ClientApp, you can determine the name, type of client and its version.
The PublishedInstance table is automatically filled in when information about the change in the status (Activity) of the “Client” is received. Among all the columns, they are especially interesting:
PublisherId - user account identifier that connects to Skype For Business;
LastPubTime - date and time at which the status of the “Client” changed;
Data - the main part of information on the changed status. Allows you to understand what exactly happened with the status in a specific period of time. A more detailed description of the information that may be in the column can be found in Chapter 6 of Enhanced Presence Lync 2010.
We only extract information from Data about Availability, TimeZoneBias, Device, and PublisherId from Data.
Separately, it should be said about BoundEndpointId. This column indicates the session identifier that is set each time the client is connected to the Skype For Business server. Using this identifier, we can determine additional information on the device from which the “client” connects. This allows you to make status changes individually for each device.
As a result, after applying the selection from the Data column, grouping and sorting, we get this result:
All statuses in the database are written only by numbers, and Microsoft does not give information what status means what. There is only general information on ranges that are reserved for various Activities.
For example 15500 - away, mobile. Or 6700 - busy, presenting.
I had to spend time researching and collecting statistics to compile a table of reference books for ranges of Activity:
In this case, based on the reference book and the isav column, it is determined whether the time is considered active or not.
New incoming status information is stored in a separate database, which is in no way connected with the rtc and rtcdyn databases. At the same time, we are compiling general information for the past days to expedite the output of reports of active and inactive time of the “client”. Logs are saved using TimerJob, or in the case of using SQL Express using Windows schedules.
- Databases Skype For Business Server.
- Rtcdyn base:
- The concept of client in rtcdyn.
- We get customers. Table [RegistrarEndpoint].
- We work with changes in customer statuses. Table [PublishedInstance].
- Customer statuses. Availability column.
- Journal of presence logs.
Skype For Business Server Databases
There are two databases that we work with when monitoring status changes:
[rtc] - contains static information that does not change over time. This database contains information about users, contact details, profile photos, status messages, etc.
[rtcdyn] - contains dynamic information regarding changes in the status of clients (programs) that are connected to the Skype For Business server.
Rtcdyn base
As mentioned above, this database contains dynamic information regarding changes in the status of clients (programs) that are connected to the Skype For Business server.
Concept of client in rtcdyn
First of all, it should be agreed with the concept of “client”. First of all, this is the program or web browser from which the connection to the Skype For Business server takes place. “Client” requires a username and password for the connection and provides additional information regarding the device, time zone. It is important to understand that the information collected on the "Client" applies only to the "Client" and not the real user. Those. the information that we will collect depends on where the user launches the “Client”.
Consider an example:
- User A can start the client inside the Remote Desktop session, and therefore his client will transmit information about the computer inside the Remote Desktop session to the Skype For Business server.
- User A can start the client on a mobile phone, and therefore, data regarding the mobile phone will likewise be transmitted.
All information from the "customers" are independent of each other.
We get customers. Table [RegistrarEndpoint]
After connecting to the rtcdyn database, we see several tables. Among them, you can find the RegistrarEndpoint table. Let's try to extract some information from this table.
select top 5 ContactInfo
from [rtcdyn].[dbo].[RegistrarEndpoint];
After converting the received information, we can see information about the source of the connection to the Skype For Business server.
For instance:
P;sip:212.3.112.68:51024;transport=tls;ms-opaque=b68b30d990;ms-received-cid=608500;] ;;sip:lyncedge1-uk.point.local:5061;transport=tls;opaque=state:Ee.ag8rxSddCvwU5-K8q7K742RAAA;lrbwbyztosJ7NpwKGhwBHTHlngAA70f2c5f712
From this line, you can extract the IP address of the device from which the “client” is connecting.
Similarly, you can extract information from the ClientApp column. This column contains information about the application from which the connection to the Skype For Business server itself occurs.
Examples of values that may be contained in ClientApp are given below:
UCCAPI / 15.0.4753.1000 OC / 15.0.4753.1000 (Skype for Business)
RTCC / 5.0.0.0 OWA / 15.00.1076.009
Based on ClientApp, you can determine the name, type of client and its version.
We work with changes in customer statuses. Table [PublishedInstance]
The PublishedInstance table is automatically filled in when information about the change in the status (Activity) of the “Client” is received. Among all the columns, they are especially interesting:
PublisherId - user account identifier that connects to Skype For Business;
LastPubTime - date and time at which the status of the “Client” changed;
Data - the main part of information on the changed status. Allows you to understand what exactly happened with the status in a specific period of time. A more detailed description of the information that may be in the column can be found in Chapter 6 of Enhanced Presence Lync 2010.
We only extract information from Data about Availability, TimeZoneBias, Device, and PublisherId from Data.
Separately, it should be said about BoundEndpointId. This column indicates the session identifier that is set each time the client is connected to the Skype For Business server. Using this identifier, we can determine additional information on the device from which the “client” connects. This allows you to make status changes individually for each device.
As a result, after applying the selection from the Data column, grouping and sorting, we get this result:
Customer statuses. Availability Column
All statuses in the database are written only by numbers, and Microsoft does not give information what status means what. There is only general information on ranges that are reserved for various Activities.
For example 15500 - away, mobile. Or 6700 - busy, presenting.
I had to spend time researching and collecting statistics to compile a table of reference books for ranges of Activity:
In this case, based on the reference book and the isav column, it is determined whether the time is considered active or not.
Journal of presence logs
New incoming status information is stored in a separate database, which is in no way connected with the rtc and rtcdyn databases. At the same time, we are compiling general information for the past days to expedite the output of reports of active and inactive time of the “client”. Logs are saved using TimerJob, or in the case of using SQL Express using Windows schedules.
As a result, we have complete information about when and how the status of the user changed, and about the device from which this status was obtained (in our particular case we were only interested in office computers, but no one bothered to turn on the fantasy).
Using this information, you can get information about the work schedule. At the moment, with great certainty, we get:
- Arrival time at the office.
- Time for the start and end of the lunch break.
- Leaving time.
- How many hours have been worked out.
- Did the employee work from home in the evening.
The texture is enough to build a full-fledged system for monitoring working time, but with reference to the real workplace of a worker of the intellectual front - a programmer or support engineer. Actually, we implemented such a system, which we hasten to boast of.
You can receive, for example, such reports:
- Average and maximum latency for the period.
- The entire history of absences indicating the presence of the consent of the head.
- Extracurricular time.
- Work from home - the system receives information from RDG gateways and you can see if an employee logged on to the computer locally or from home.
As in all such systems, there is a vacation schedule, sick leave, managers permission to work from home and other goodies.
Another feature is the personal account of the employee, and mailing reports. That is, the system does not position itself as a “spy” (we will secretly collect information about you, and then subtract it from your salary), but as a “helper” (we will show you that statistics are being kept, and we will give the opportunity to analyze these statistics and draw conclusions for ourselves) . This function turned out to be very useful - even despite the fact that all our employees and customer’s employees are quite responsible people, after the start of sending reports, the average time the employee was present at the workplace increased.
Implementation with system screenshots:
SkypeTime
We can state that we coped with the task. Our customer is satisfied, we (using this system ourselves) are satisfied, employees whose activities are controlled ... well, at least, are not dissatisfied, and some, as mentioned above, are grateful to the system for structured work schedule reports and other employee-oriented features . Perhaps the system should not be used to maintain a time sheet or payroll, but as a tool that provides the manager with estimated information about the employee’s work, it is very effective. It is also an invaluable help for the administrator employee, who keeps information about holidays and vacations. A situation such as - an employee on vacation, the boss asked him to work from home for half a day, and then take this time off for free - is now easily tracked. And the ability to configure flexible conditions such as “if at 9: 15 not at work means absenteeism ”or even“ you can be late no more than twice a week and if you worked with the customer at night, you can sleep until lunch, and then work from home ”made it possible to use the system for IT companies, where conventional ACSs are practically not applicable. Well, the fact of the absence of “spyware” software on the computers of employees favorably affects the atmosphere in the team. And a good working atmosphere is, "according to British scientists," up to 80% of work success =)
PS Yes, of course, it would be nice to look at the statistics of work on the Internet and collect a list of installed programs, but this is a completely different level of espionage, fraught with a loss of team loyalty. In addition, in our practice, such sophisticated deceivers are extremely rare, and yet they quickly reveal themselves with low productivity or low quality work - so the reward still finds heroes.
Sincerely, the team of the company Servilon.ru Servilon.com