Syed Jahanzaib Personal Blog to Share Knowledge !

February 13, 2017

Windows Event-Viewer Logging to MYSQL

Filed under: Uncategorized — Syed Jahanzaib / Pinochio~:) @ 2:10 PM

out-of-the-box

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]

@echo off
set MYSQL_HOST=10.0.0.1
set MYSQL_ID=your_mysqlid
set MYSQL_PASS=your_password
set MYSQL_DB=your_events
set MYSQL_TB=your_table
set ACTION=Account Created
set HOLDER=c:\temp\acnew-temp.txt
set LOGFILE=e:\userlog\users-created-log.log
type 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=%%a
for /f "tokens=3" %%a in ('type %HOLDER% ^| find /i "Event ID"') do set eventid=%%a
for /f "tokens=2" %%a in ('type %HOLDER% ^| find /i "Date"') do set dt=%%a
set 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]

@echo off
set MYSQL_HOST=10.0.0.1
set MYSQL_ID=MY_ID
set MYSQL_PASS=MY_PASS
set MYSQL_DB=DB
set MYSQL_TB=TABLE
set ACTION=Account Deleted
set HOLDER=c:\temp\acdel-temp.txt
set LOGFILE=e:\userlog\users-deleted-log.log
type 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=%%a
for /f "tokens=3" %%a in ('type %HOLDER% ^| find /i "Event ID"') do set eventid=%%a
for /f "tokens=2" %%a in ('type %HOLDER% ^| find /i "Date"') do set dt=%%a
set 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

1- accoutn creation - attach batch file via event viewer.png

1.5 - triggers.PNG

2- trigger action.PNG

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


;-- 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.sql

Script 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.PNG

.

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

php-result

mysql cmd line snapshot


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)


This is a itty-bitty example only, on how you can build your own customized solution using out of the box approach !

Syed Jahanzaib

Create a free website or blog at WordPress.com.

%d bloggers like this: