Monitorando logоn de usuários do Active Directory com servidor MySQL

Criando auditoria de logon dos usuários para rastrear atividades de logins.

srvAD	192.168.0.x
srvMySQL	192.168.0.x

ATENÇÃO Você deve instalar o odbc mysql via GPO nas estações

MySQL

shell> mysql -uroot -p
mysql> create database logons character set utf8 collate utf8_bin;
mysql> CREATE USER 'DBuser'@'%' IDENTIFIED BY 'P@ssW0rd';
mysql> GRANT INSERT ON `logons` . * TO 'DBuser'@'%';
mysql> CREATE USER 'grafana' IDENTIFIED BY 'P@ssW0rd';
mysql> GRANT SELECT ON logons.logons TO 'grafana';
mysql> flush privileges;
mysql> quit;
shell> wget https://blog.joserodriguesfilho.com/wp-content/uploads/2021/08/logons.zip
shell> sudo apt install unzip && unzip logons.zip
shell> mysql -uroot -p logons < logons.sql
shell> sudo rm logons.sql logons.zip

MySQL Dump

-- phpMyAdmin SQL Dump
-- version 4.5.4.1deb2ubuntu2.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: 02-Ago-2021 às 19:22
-- Versão do servidor: 5.7.33-0ubuntu0.16.04.1
-- PHP Version: 7.0.33-0ubuntu0.16.04.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `logons`
--

-- --------------------------------------------------------

--
-- Estrutura da tabela `logons`
--

CREATE TABLE `logons` (
  `id` int(10) UNSIGNED NOT NULL,
  `hostname` varchar(100) DEFAULT NULL,
  `nome` varchar(255) DEFAULT NULL,
  `winver` varchar(255) DEFAULT NULL,
  `login` varchar(100) NOT NULL DEFAULT '',
  `mac` varchar(100) DEFAULT NULL,
  `ip` varchar(100) DEFAULT NULL,
  `teamviewer` varchar(100) DEFAULT NULL,
  `data` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `logons`
--
ALTER TABLE `logons`
  ADD PRIMARY KEY (`id`,`login`,`data`),
  ADD KEY `idx_data` (`data`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `logons`
--
ALTER TABLE `logons`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

VBScript

Altere os campos em negrito conforme sua necessidade. Efetue o download do arquivo e crie uma GPO aplicando em todos os computadores

strComputer = "."
Set objSysInfo = CreateObject("ADSystemInfo")
Set objCurrentUser = GetObject("LDAP://" & objSysInfo.UserName & "")
strFullName = objCurrentUser.Fullname

Dim timeStamp
timeStamp = Year(Date) & "-" & Month(Date) & "-" & Day(Date) & " " & FormatDateTime(Now, vbLongTime)
'run = msgbox(timeStamp)

Set WshNetwork = WScript.CreateObject("WScript.Network")
sComputer = WshNetwork.ComputerName
sUser = WshNetwork.UserName
wmiQuery = "Select * from Win32_NetworkAdapterConfiguration " & "Where IPEnabled = 'True'"
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colItems = objWMIService.ExecQuery(wmiQuery)
Set objShell = WScript.CreateObject("WScript.Shell")

For Each objItem In colItems
    For Each IPAddr In objItem.IPAddress
        If IPAddr <> "0.0.0.0" Then
            sMacAddress = objItem.MACAddress
            sIPAddress = ""
            For j = 0 To UBound(objItem.IPAddress)
                If (sIPAddress = "") Then
                    sIPAddress = objItem.IPAddress(j)
                Else
                    sIPAddress = sIPAddress & """,""" & objItem.IPAddress(j)
                End If
            Next
        End If
    Next
Next

'
On Error Resume Next

' TeamViewer 13
strTeamV = "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\TeamViewer\ClientID"
sTeamV = varMsg & objShell.RegRead(strTeamV)
' Windows Version
strWinVer = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProductName"
sWinVer = varMsg & objShell.RegRead(strWinVer)

' msgbox for debug
'WScript.Echo """" & sComputer _
' & """,""" & strFullName _
' & """,""" & sUser _
' & """,""" & sMacAddress _
' & """,""" & sIPAddress _
' & """,""" & sTeamV _
' & """,""" & sWinVer _
' & """,""" & timeStamp & """"

'for debug
'On Error Resume Next

' MySQL
Dim Connection, ConnectString
ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=192.168.0.x;" & _
"PORT=3306;" & _
"DATABASE=logons;" & _
"USER=DBuser;" & _
"PASSWORD=P@ssW0rd;"

'On Error Resume Next

Set Connection = CreateObject("ADODB.Connection")
Connection.Open ConnectString

Sql = "INSERT INTO logons (hostname, nome, login, mac, ip, winver, teamviewer, data) VALUES ('" & sComputer & "', '" & strFullName & "', '" & sUser & "', '" & sMacAddress & "', '" & sIPAddress & "', '" & sWinVer & "', '" & sTeamV & "', '" & timeStamp & "')"

Set Result = Connection.Execute(Sql)
Connection.Close

Visualização WEB

Download do código fonte do script acima.

Visualizando os logons (Grafana) Importe via grafana.com -> 14827