📜 ⬆️ ⬇️

How to graze cats. The history of building a system of control and accounting of working time for an IT company


This story is based on a number of successful coincidences, in which we, it seems to us, managed to recognize the correct trend and create a product that can suit many like us. Because ... well, first things first.

So, we are a company that provides high-tech services to its customers - outsourcing IT expertise, remote support, custom software development, etc. etc. As with any other company, for us the issue of compliance with labor discipline and control of the presence of our highly qualified employees in the workplace is topical. Tasks of this kind are relatively successfully solved in cases where employees a) work on 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 the 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 rather in the negative, and here's why.

First, a typical employee of a high-tech company (hereinafter referred to as “expert”) is a person who works not only at work and not only in traditional working hours. It is connected ... it can be a lot with what it is connected - with the actual need to do some work at night, with the difference in time zones with customers, in the end with the fact that the peak performance of our ingenious developer falls on the interval between 23-00 and 4- 00, and at this time he works wonders. Accordingly, it is necessary to take into account not being in the immediate vicinity of the work computer, but actual work on it (possibly, remotely from home).

Secondly, the expert is a freedom-loving creature. The idea that a program deals exclusively with what controls its activities, acts on it demotivatingly, undermines the spirit of innovative teams and, accordingly, negatively affects productivity.
')
Thirdly, an expert is an expert, with his professional ambitions. The existence of software, which he (the expert) opposes, can direct the talents of this person to fight this software. Thus, our expert at his workplace during his working time will spend energy ... to fight the system of accounting of working time! Agree that something is wrong here =).

Well and, fourthly, high-tech companies themselves are suspicious of programs that collect user data. Who knows where then this data floats away ...

In general, we realized the problem for ourselves, but, like a real “shoemaker without shoes,” we were not in a hurry to solve it. So far our Client has not addressed us with the same problem. From here begin previously announced matches.

So, our client is a company that provides high-tech services to its customers. Namely, a startup specializing in the Dedicated Developers Team service (when programmers are leased to a customer). Gradually, the number of programmers at the client has grown, and the question arose of labor discipline and control of working time. And 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 (anticipating at least double the 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, like SharePoint. On an ordinary home laptop, and even more so on the iPad, you will not 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 like ours!

Then it turned out that both we and the customer use Skype for Business (formerly MS Lync) as both a PBX and universal means of communication. This program, in addition to its direct responsibilities, performs another one - it can at any time report the status of the user's presence in the system. So, an employee always uses a work computer for work - Skype for Business is always on it - Skype always knows the user's presence status ... A little mental effort - and the idea of ​​collecting a history of status changes and obtaining a work schedule based on it is firmly established in our collective mind. It remains only to implement it, to which we started.

Naturally, the collection of statistics from the client did not make much sense, because I can't see on which device the employee is currently working. Consequently, it was necessary to look at the contents of the Skype server databases (this, of course, about the corporate Skype4B servers). The corresponding information about the database structure of Skype4B servers, although it was not documented, but access to it did not require any decryption or decompilation operations. Under a cat there are some technical details for interested.

Technical details
Plan:
  1. Databases Skype for Business server.
  2. Rtcdyn base:
    • Concept client in rtcdyn.
    • We retrieve customers. Table [RegistrarEndpoint].
    • We work with changes in customer status. Table [PublishedInstance].
    • Customer statuses. Availability column.
  3. Log of presence logs.

Skype For Business Server Databases


There are two databases we work with when monitoring status changes:

[rtc] - contains static information that does not change over time. This database contains information about users, contact information, 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 database


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". This is primarily a program or a web browser that connects to the Skype For Business server. The “client” to connect requires a username and password 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 we will collect depends on where the user launches the “Client”.

Consider an example:
  1. User A can launch the client inside the Remote Desktop session, and therefore his client will send the computer information within the Remote Desktop session to the Skype For Business server.
  2. User A can run the client on a mobile phone, and therefore the data regarding the mobile phone will be transmitted in the same way.

All information from the "clients" are independent of each other.

We retrieve customers. Table [RegistrarEndpoint]

After connecting to the rtcdyn database, we see several tables. Among them is 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 the information about the source of connection to the Skype For Business server.

For example:

 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 <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 connected.

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 takes place.

Examples of values ​​that ClientApp may contain are as follows:

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 status. Table [PublishedInstance]

The PublishedInstance table is populated automatically when information is received about the status change (Activity) of the “Client”. Among all the columns are particularly interesting:

PublisherId - user account identifier that connects to Skype For Business;

LastPubTime - the date and time at which the change in the status of the "Client" occurred;

Data - the main part of the information on the changed status. It 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 the Enhanced Presence Lync 2010 book.

We extract from Data only information about Availability, TimeZoneBias, Device and PublisherId.

Separately, it should be said about BoundEndpointId. This column indicates the session ID, which is established each time a client connects to the Skype For Business server. Using this identifier, we can determine additional information on the device from which the “client” is connected. This allows for status changes to be made individually for each device.

As a result, after applying the sample from the Data column, grouping and sorting, we get the following result:


Customer statuses. Availability column


All statuses in the database are written only by numbers, and Microsoft does not provide information on what status means. There is only general information on the 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 ranges. Activity:

In this case, on the basis of the directory and the isav column, it is determined whether the time is counted as active or not.

Presence Log


New incoming information on statuses is stored in a separate database, which is not related to the rtc and rtcdyn bases. At the same time, we compute general information for the past days in order to speed up the output of active and inactive “client” reports. Logs are saved using TimerJob, or in the case of using SQL Express using the Windows schedule.


As a result, we have complete information about when and how the user's status changed, and about the device from which this status was obtained (in our particular case, we were interested only in office computers, but no one prevented anyone from turning on the fantasy).

Using this information, you can get information about the work schedule. At the moment, with great certainty, we get:

The invoice is enough to build a full-fledged system of control of working time, but with reference to the real workplace of the worker of the intellectual front - a programmer or a support engineer. Actually, we have implemented such a system, and we are in a hurry to boast.

You can receive, for example, such reports:

As with all such systems, there is a vacation schedule, sick leave, manager permits for working from home and other bonuses.

Another feature is the personal office of the employee, and sending reports to the mail. That is, the system does not position itself as a “spy” (we will secretly collect information about you, and then we deduct it from salary), but as an “assistant” (we’ll show you that statistics are being kept and let us analyze these statistics and draw conclusions for ourselves) . This function turned out to be very useful - even though all our employees and employees of the customer are quite responsible people, after the start of the distribution of reports, the average time of presence of an employee in the workplace has increased.

Implementation with screenshots of the system:

Skypetime
Personal Area:


Employee report for the period:


more screenshots can be seen on our website


We can state - we coped with the task. Our customer is satisfied, we are (using the system itself) satisfied, employees whose activities are controlled ... well, at least, not dissatisfied, and some, as already 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 evaluative information on the work of an employee, it is very effective. She is also an invaluable help employee-administrator, leading information about time off and leave. Such a situation as - an employee on vacation, the head asked him to work out of the house for half a day, and then take this time off time - is now easily monitored. And the possibility of setting 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 a customer at night, you can sleep until lunch, and after working from home” made it possible use the system for IT companies where conventional access control systems are practically not applicable. Well, the fact that there is no “spyware” on the computers of employees has a positive effect on the atmosphere in the team. 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 cheaters are extremely rare, and they still rather quickly reveal themselves to be low productivity or poor quality of work - so the reward still finds heroes.

Yours, Servilon Team

Source: https://habr.com/ru/post/272701/


All Articles