In our small office environment, we are using Windows 2008 R2 Active Directory for user management/authentication and control purpose. Dueto some standard operating procedure I was asked to log User Account Creation / Removal events in Linux base mySQL DB. Since windows doesn’t provide option to directly export event into linux base mysql, therefore I made an workaround for it using specific windows events tagged with task scheduler approach. Not to mention , this approach of using task scheduler with events is not a new thing, but it was definitely a bit confusing for a numbnuts like ME on how to acquire only the very specific fields trimmed according to our taste and get it logged in remote linux mysql db. but Alhamdulillah I managed to get it in few hours struggling.
z@iB
Items I used in this post are …
- Windows 2008 R2 server with Active Directory
- c:\temp folder to hold temporary information for the triggered event
- e:\userlog\ folder to hold all logs
- Event ID which will be logged in local log file and mySQL DB [as required] :
4720
New User Account Created
4726
User Account Deleted - Two batch files which will be executed when specific event will occur.
- Mysql (I used mysql-5.7.17-winx64.zip) package to add entries in mySQL DB name
events
You can download mysql-5.7.17-winx64.zip from fmy Google Drive at
mysql-5.7.17-winx64 by Syed Jahanzaib
New Account Batch File for LOG [ac-new-log.bat]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | @echo offset MYSQL_HOST=10.0.0.1set MYSQL_ID=your_mysqlidset MYSQL_PASS=your_passwordset MYSQL_DB=your_eventsset MYSQL_TB=your_tableset ACTION=Account Createdset HOLDER=c:\temp\acnew-temp.txtset LOGFILE=e:\userlog\users-created-log.logtype nul > %HOLDER%wevtutil qe security /rd:true /f:text /c:1 /q:"*[System/EventID=4720]" > %HOLDER%for /f "tokens=4" %%a in ('type %HOLDER% ^| find /i "Account Name"') do set accname=%%afor /f "tokens=3" %%a in ('type %HOLDER% ^| find /i "Event ID"') do set eventid=%%afor /f "tokens=2" %%a in ('type %HOLDER% ^| find /i "Date"') do set dt=%%aset HEADER=%eventid% : %accname% / %ACTION% @ ... %dt%echo %HEADER%echo %HEADER% >> %LOGFILE%c:\mysql\bin\mysql -h %MYSQL_HOST% -u%MYSQL_ID% -p%MYSQL_PASS% -e "use %MYSQL_DB%; INSERT INTO %MYSQL_TB% (eventid,type,account,msg) VALUES ('%eventid%','%ACTION%','%accname%','%HEADER%');" |
Account Delete Batch File for LOG [ac-del-log.bat]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | @echo offset MYSQL_HOST=10.0.0.1set MYSQL_ID=MY_IDset MYSQL_PASS=MY_PASSset MYSQL_DB=DBset MYSQL_TB=TABLEset ACTION=Account Deletedset HOLDER=c:\temp\acdel-temp.txtset LOGFILE=e:\userlog\users-deleted-log.logtype nul > %HOLDER%wevtutil qe security /rd:true /f:text /c:1 /q:"*[System/EventID=4726]" > %HOLDER%for /f "tokens=3" %%a in ('type %HOLDER% ^| find /i "Account Name"') do set accname=%%afor /f "tokens=3" %%a in ('type %HOLDER% ^| find /i "Event ID"') do set eventid=%%afor /f "tokens=2" %%a in ('type %HOLDER% ^| find /i "Date"') do set dt=%%aset HEADER=%eventid% : %accname% / %ACTION% @ ... %dt%echo %HEADER%echo %HEADER% >> %LOGFILE%c:\mysql\bin\mysql -h %MYSQL_HOST% -u%MYSQL_ID% -p%MYSQL_PASS% -e "use %MYSQL_DB%; INSERT INTO %MYSQL_TB% (eventid,type,account,msg) VALUES ('%eventid%','%ACTION%','%accname%','%HEADER%');" |
Attaching Batch files with Specific Event ID
On Domain Controller, open event viewer, goto 4720 event, right click and select ‘Attach Task to This Event‘ and in trigger select your batch file. (for account creation)
As showed in the image below …
Repeat same for event id 4726.
Ok to finish it.
Creating DB in mySQL
Now create a new DB with required name and tables in mySQL …
One example is as follows.
mydb.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | ;-- MySQL dump 10.13 Distrib 5.5.54, for debian-linux-gnu (i686)---- Host: localhost Database: events-- -------------------------------------------------------- Server version 5.5.54-0ubuntu0.12.04.1/*!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 utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `mymaindb`--DROP TABLE IF EXISTS `mymaindb`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `mymaindb` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`eventid` varchar(40) DEFAULT NULL,`type` varchar(255) NOT NULL,`account` varchar(255) NOT NULL,`msg` varchar(10000) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=462 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `mymaindb`--LOCK TABLES `mymaindb` WRITE;/*!40000 ALTER TABLE `mymaindb` DISABLE KEYS */;INSERT INTO `mymaindb` VALUES (459,'2017-02-13 08:39:45','4720','Account Created','testing.act','4720 : testing.act / Account Created @ ... 2017-02-13T12:02:05.777'),(461,'2017-02-13 08:49:46','4726','Account Deleted','testing.act','4726 : testing.act / Account Deleted @ ... 2017-02-13T12:02:38.521');/*!40000 ALTER TABLE `mymaindb` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!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 */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2017-02-13 14:47:11 |
to import above DB , use following command …
mysql -uroot -pROOTPASS < mydb.sqlScript Result in CMD:
Now try to create/delete a user account in active directory, and you will see the result in mysql db.
I recommend to debug first, to make sure things are working ok, execute the bat file manually to see the results
ac-new-log.bat
.
Script Result in mySQL DB :
[all above fuss was made just to acquire only specific data trimmed as per out taste, and get it logged in in remote linux mySQL otherwise task was very easy in general]
phpmyadmin snapshot
mysql cmd line snapshot
1 2 3 4 5 6 7 8 | mysql> select * from MY_DB;+-----+---------------------+---------+-----------------+-------------+--------------------------------------------------------------------+| id | datetime | eventid | type | account | msg |+-----+---------------------+---------+-----------------+-------------+--------------------------------------------------------------------+| 459 | 2017-02-13 13:39:45 | 4720 | Account Created | testing.act | 4720 : testing.act / Account Created @ ... 2017-02-13 13:39:45 || 461 | 2017-02-13 13:49:46 | 4726 | Account Deleted | testing.act | 4726 : testing.act / Account Deleted @ ... 2017-02-13 13:39:45 |+-----+---------------------+---------+-----------------+-------------+--------------------------------------------------------------------+2 rows in set (0.00 sec) |
