Convenient search of the user's computer in the Windows domain
Hello to all!
I work in the company. 1500+ active users.
Helpdesk is organized, employees register tickets in the system, but somehow it turns out that some of the users forget to specify the name of the PC for which they are located.
To find out this information (by phone / email / extrasensory abilities), girls from support spend precious time that they could spend on a fresh cup of coffee or talking about a new dress (joke). Our employees are constantly busy with their immediate work.
The idea (not an innovation of course) is to write data about the user's logons into a single database, and then quickly search for the necessary information through the web.
')
Windows environment (XP / 7/2003/2008), accounts are stored in AD, all devices are working on the local network as a DBMS using MSSQL 2005 / 2008R2.
Information retrieval via web interface:
Information should be displayed:
Since the “solution” will be used by several people (from 5 to 35 people), we must somehow call it for convenience. We called it "SuperVisor".
How are we going to write in the database?
The first thing that came to mind was to create a bat file with sqlcmd, but for each version of Windows you need your own version of the utility sqlcmd. Uncomfortable, and we did not dig deeper.
The second solution was to create a small exe client, which at startup will collect data about the domain account name, pc name, ip pc address and after receiving this information, data will be inserted into the database. We place the program, for example, in the NETLOGON folder on the domain controller and distribute it by group policies to PCs / Servers in the company's local network.
Further the algorithm is as follows:
Tables are created and populated with data. I forgot to write that the database server is virtual and works under Windows Server 2003R2, DBMS MSSQL 2008R2. Application server virtual, Windows 2003R2, Apache 2.2 + PHP 5.2
The next step is to create a web interface so that system administrators and helpdesk staff can comfortably work with the service. In internal development, to save time, I use the Bootstrap framework. The main page, before starting to enter data into the search box, looks like this:
In the search box, you can enter the employee's last name / account (login) or computer name:
Moreover, it is not necessary to enter the full name and name of the employee / login / pc name, the search for matches in the database is performed using the LIKE operator (example ... LIKE 'iva%'), queries are executed asynchronously, the search result is updated before the employee’s eyes without reloading the page after entering a new character in the search bar.
The “supervisor” has been working with us in the company for about a year, and copes well with its main task. Tickets are now taken to work more quickly, employees do not need to spend time clarifying additional information in order to proceed to the decision of the application.
This solution to determine the name of the computer turned out to be modern and convenient, in my opinion.
Good luck in job!
Install Apache2.2 and PHP-5 ;
Connecting MS SQL to PHP 5.3 and higher .
I work in the company. 1500+ active users.
Helpdesk is organized, employees register tickets in the system, but somehow it turns out that some of the users forget to specify the name of the PC for which they are located.
To find out this information (by phone / email / extrasensory abilities), girls from support spend precious time that they could spend on a fresh cup of coffee or talking about a new dress (joke). Our employees are constantly busy with their immediate work.
The idea (not an innovation of course) is to write data about the user's logons into a single database, and then quickly search for the necessary information through the web.
')
What do we have?
Windows environment (XP / 7/2003/2008), accounts are stored in AD, all devices are working on the local network as a DBMS using MSSQL 2005 / 2008R2.
What do we want?
Information retrieval via web interface:
- Where did the employee log in with the last name, for example, " Ivanov ";
- Who logged in to the device, for example, " PC34 ";
- Where were logons under the account, for example, " hr.ivanov ".
Information should be displayed:
- name pc;
- login;
- Full name;
- ip address;
- logon date.
Title?
Since the “solution” will be used by several people (from 5 to 35 people), we must somehow call it for convenience. We called it "SuperVisor".
Let's start the implementation
How are we going to write in the database?
The first thing that came to mind was to create a bat file with sqlcmd, but for each version of Windows you need your own version of the utility sqlcmd. Uncomfortable, and we did not dig deeper.
The second solution was to create a small exe client, which at startup will collect data about the domain account name, pc name, ip pc address and after receiving this information, data will be inserted into the database. We place the program, for example, in the NETLOGON folder on the domain controller and distribute it by group policies to PCs / Servers in the company's local network.
Delphi program
unit Unit1; interface uses Windows,SysUtils, Variants, Classes, Forms,WinSock, Dialogs, DB, ADODB,StdCtrls,Messages, ExtCtrls; type TForm1 = class(TForm) boston: TADOConnection; qryinsert: TADOQuery; timer: TTimer; procedure FormCreate(Sender: TObject); procedure timerTimer(Sender: TObject); private { Private declarations } public { Public declarations } end; var Form1: TForm1; implementation {$R *.dfm} // 0.0.4 // 27-02-2014 function GetCurrentUserName: string; const cnMaxUserNameLen = 254; var sUserName: string; dwUserNameLen: DWORD; begin dwUserNameLen := cnMaxUserNameLen - 1; SetLength(sUserName, cnMaxUserNameLen); GetUserName(PChar(sUserName), dwUserNameLen); SetLength(sUserName, dwUserNameLen); Result := string(sUserName); end; function GetCurrentComputerName: string; const cnMaxComputerNameLen = 254; var sComputerName: string; dwComputerNameLen: DWORD; begin dwComputerNameLen := cnMaxComputerNameLen - 1; SetLength(sComputerName, cnMaxComputerNameLen); GetComputerName(PChar(sComputerName), dwComputerNameLen); SetLength(sComputerName, dwComputerNameLen); Result := string(sComputerName); end; function GetLocalIP: String; const WSVer = $101; var wsaData: TWSAData; P: PHostEnt; Buf: array [0..127] of Char; begin Result := ''; if WSAStartup(WSVer, wsaData) = 0 then begin if GetHostName(@Buf, 128) = 0 then begin P := GetHostByName(@Buf); if P <> nil then Result := iNet_ntoa(PInAddr(p^.h_addr_list^)^); end; WSACleanup; end; end; procedure TForm1.FormCreate(Sender: TObject); begin try qryinsert.SQL.Text := 'begin TRANSACTION insertdata' +#13#10 + 'insert into [supervisor].[user].[info_host](host,login,last_ip) values (:host,:login,:ip)'+#13#10 + 'COMMIT TRANSACTION insertdata'; qryinsert.Parameters.ParamByname('login').Value:= GetCurrentUserName ; qryinsert.Parameters.ParamByname('host').Value:= GetCurrentComputerName ; qryinsert.Parameters.ParamByname('ip').Value:= GetLocalIP ; qryinsert.ExecSQL; timer.Enabled:=True; form1.timer.Interval:=1500; except Application.Terminate; end end; procedure TForm1.timerTimer(Sender: TObject); begin ExitProcess(0); end; end. Further the algorithm is as follows:
- User logs in on PC;
- SuperVisor.exe runs stealthily;
- An INSERT attempt is made to the specified database; if successful, the program ends its work;
- If INSERT fails (the laptop is not on the local network, the database server is unavailable, etc.), the timeout is 15 seconds and the process is “killed” without frightening the user with scary messages and windows.
Ok, now let's create the tables where the data will be stored
/* , ----------------------------------------------------------- host - login - last_ip - ip createdate - createdate_unix - unix , ------------------------------------------------------------ */ CREATE TABLE [user].[info_host]( [id] [int] IDENTITY(1,1) NOT NULL, [host] [nvarchar](128) NULL, [login] [nvarchar](128) NULL, [createdate] [datetime] NOT NULL, [createdate_unix] [int] NOT NULL, [last_ip] [nvarchar](20) NULL ) ON [supervisor] GO ALTER TABLE [user].[info_host] ADD DEFAULT (getdate()) FOR [createdate] GO ALTER TABLE [user].[info_host] ADD DEFAULT (datediff(second,'1970-01-01 00:00:00',getutcdate())) FOR [createdate_unix] /* , . ------------------------------------------------------------ login - displayName - displayname createdate - createdate_unix - unix , ------------------------------------------------------------ */ CREATE TABLE [user].[info_login_card]( [id] [int] IDENTITY(1,1) NOT NULL, [login] [nvarchar](128) NULL, [createdate] [datetime] NOT NULL, [createdate_unix] [int] NOT NULL, [displayName] [nvarchar](256) NULL ) ON [supervisor] GO ALTER TABLE [user].[info_login_card] ADD DEFAULT (getdate()) FOR [createdate] GO ALTER TABLE [user].[info_login_card] ADD DEFAULT (datediff(second,'1970-01-01 00:00:00',getutcdate())) FOR [createdate_unix] GO Tables are created and populated with data. I forgot to write that the database server is virtual and works under Windows Server 2003R2, DBMS MSSQL 2008R2. Application server virtual, Windows 2003R2, Apache 2.2 + PHP 5.2
The next step is to create a web interface so that system administrators and helpdesk staff can comfortably work with the service. In internal development, to save time, I use the Bootstrap framework. The main page, before starting to enter data into the search box, looks like this:
In the search box, you can enter the employee's last name / account (login) or computer name:
Moreover, it is not necessary to enter the full name and name of the employee / login / pc name, the search for matches in the database is performed using the LIKE operator (example ... LIKE 'iva%'), queries are executed asynchronously, the search result is updated before the employee’s eyes without reloading the page after entering a new character in the search bar.
query to database to search by name
set rowcount $limit SELECT a.[id] ,a.[host] ,a.[login] ,a.[createdate] ,a.[createdate_unix] ,a.[last_ip] ,b.[displayName] FROM [user].[info_host] a left join [user].[info_login_card] b on a.login = b.login where b.displayName like ('$ihostlogin') order by a.[createdate_unix] desc set rowcount 0 query to db to search by pc name
set rowcount $limit SELECT a.[id] ,a.[host] ,a.[login] ,a.[createdate] ,a.[createdate_unix] ,a.[last_ip] ,b.[displayName] FROM [user].[info_host] a left join [user].[info_login_card] b on a.login = b.login where a.host like ('$hostlogin') order by a.[createdate_unix] desc set rowcount 0 login search
set rowcount $limit SELECT a.[id] ,a.[host] ,a.[login] ,a.[createdate] ,a.[createdate_unix] ,a.[last_ip] ,b.[displayName] FROM [user].[info_host] a left join [user].[info_login_card] b on a.login = b.login where a.login like ('$hostlogin') order by a.[createdate_unix] desc set rowcount 0 The “supervisor” has been working with us in the company for about a year, and copes well with its main task. Tickets are now taken to work more quickly, employees do not need to spend time clarifying additional information in order to proceed to the decision of the application.
This solution to determine the name of the computer turned out to be modern and convenient, in my opinion.
Good luck in job!
useful links
Install Apache2.2 and PHP-5 ;
Connecting MS SQL to PHP 5.3 and higher .
Source: https://habr.com/ru/post/240635/
All Articles