\n
-- -- Database: `logon` -- -- -------------------------------------------------------- -- -- Table structure for table `bcs` -- CREATE TABLE IF NOT EXISTS `bcs` ( `id` int(11) NOT NULL, `bc` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `computers` -- CREATE TABLE IF NOT EXISTS `computers` ( `id` int(11) NOT NULL, `domain_id` int(11) DEFAULT NULL, `samaccountname` varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `domains` -- CREATE TABLE IF NOT EXISTS `domains` ( `id` int(11) NOT NULL, `name` varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `events` -- CREATE TABLE IF NOT EXISTS `events` ( `id` int(11) NOT NULL, `event` varchar(8) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `groups` -- CREATE TABLE IF NOT EXISTS `groups` ( `id` int(11) NOT NULL, `name` varchar(64) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `ipnets` -- CREATE TABLE IF NOT EXISTS `ipnets` ( `id` int(11) NOT NULL, `bc_id` int(11) NOT NULL, `ipnet1` varchar(3) NOT NULL DEFAULT '172', `ipnet2` varchar(3) NOT NULL DEFAULT '16', `ipnet3` varchar(3) NOT NULL, `mask` int(11) NOT NULL DEFAULT '24', `vid` mediumint(4) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `description` varchar(50) DEFAULT NULL, `tplname` varchar(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `ips` -- CREATE TABLE IF NOT EXISTS `ips` ( `id` int(11) NOT NULL, `ip` varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `latestdata` -- CREATE TABLE IF NOT EXISTS `latestdata` ( `domain_id` int(11) NOT NULL, `computer_id` int(11) NOT NULL, `computer_event_id` int(11) DEFAULT NULL, `computer_dt` datetime DEFAULT NULL, `ip_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `user_event_id` int(11) DEFAULT NULL, `user_dt` datetime DEFAULT NULL, `logonid` tinyint(4) DEFAULT NULL, `logon_dt` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `latestlogons` -- CREATE TABLE IF NOT EXISTS `latestlogons` ( `domain_id` int(11) NOT NULL, `computer_id` int(11) NOT NULL, `ip_id` int(11) DEFAULT NULL, `user_id` int(11) NOT NULL, `user_event_id` int(11) DEFAULT NULL, `user_dt` datetime DEFAULT NULL, `processlogoff` tinyint(4) DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `logoncomputers` -- CREATE TABLE IF NOT EXISTS `logoncomputers` ( `id` int(11) NOT NULL, `domain_id` int(11) NOT NULL, `computer_id` int(11) NOT NULL, `event_id` int(11) NOT NULL, `ip_id` int(11) NOT NULL, `dt` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `logonusers` -- CREATE TABLE IF NOT EXISTS `logonusers` ( `id` int(11) NOT NULL, `domain_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `computer_id` int(11) NOT NULL, `event_id` int(11) NOT NULL, `ip_id` int(11) NOT NULL, `dt` datetime DEFAULT '0000-00-00 00:00:00', `dtnow` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `membership` -- CREATE TABLE IF NOT EXISTS `membership` ( `uid` int(11) NOT NULL, `gid` int(11) NOT NULL, `used` tinyint(4) NOT NULL, `user_dt` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL, `domain_id` int(11) NOT NULL, `samaccountname` varchar(20) NOT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Indexes for dumped tables -- -- -- Indexes for table `bcs` -- ALTER TABLE `bcs` ADD PRIMARY KEY (`id`); -- -- Indexes for table `computers` -- ALTER TABLE `computers` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `samaccountname` (`samaccountname`); -- -- Indexes for table `domains` -- ALTER TABLE `domains` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`); -- -- Indexes for table `events` -- ALTER TABLE `events` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `event` (`event`); -- -- Indexes for table `groups` -- ALTER TABLE `groups` ADD PRIMARY KEY (`id`,`name`), ADD UNIQUE KEY `name` (`name`); -- -- Indexes for table `ipnets` -- ALTER TABLE `ipnets` ADD PRIMARY KEY (`id`); -- -- Indexes for table `ips` -- ALTER TABLE `ips` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `ip` (`ip`); -- -- Indexes for table `latestdata` -- ALTER TABLE `latestdata` ADD PRIMARY KEY (`computer_id`); -- -- Indexes for table `latestlogons` -- ALTER TABLE `latestlogons` ADD PRIMARY KEY (`computer_id`,`user_id`); -- -- Indexes for table `logoncomputers` -- ALTER TABLE `logoncomputers` ADD PRIMARY KEY (`id`); -- -- Indexes for table `logonusers` -- ALTER TABLE `logonusers` ADD PRIMARY KEY (`id`); -- -- Indexes for table `membership` -- ALTER TABLE `membership` ADD PRIMARY KEY (`uid`,`gid`); -- -- Indexes for table `templatedata` -- ALTER TABLE `templatedata` ADD PRIMARY KEY (`template_id`,`row_id`); -- -- Indexes for table `templates` -- ALTER TABLE `templates` ADD PRIMARY KEY (`id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `bcs` -- ALTER TABLE `bcs` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `computers` -- ALTER TABLE `computers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `domains` -- ALTER TABLE `domains` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `events` -- ALTER TABLE `events` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `groups` -- ALTER TABLE `groups` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `ipnets` -- ALTER TABLE `ipnets` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `ips` -- ALTER TABLE `ips` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `logoncomputers` -- ALTER TABLE `logoncomputers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `logonusers` -- ALTER TABLE `logonusers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `templates` -- ALTER TABLE `templates` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; <\/code><\/pre>\n <\/div>\n<\/div>\n
<\/div>\n<\/div>\n
\u0414\u0430\u043b\u0435\u0435 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043f\u0438\u0441\u0430\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u043e \u0432\u0445\u043e\u0434\u0430\u0445 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439.
\u0421\u0434\u0435\u043b\u0430\u0435\u043c 2 \u0441\u043a\u0440\u0438\u043f\u0442\u0430 (\u0432 \u043a\u0430\u0436\u0434\u043e\u043c \u0434\u043e\u043c\u0435\u043d\u0435)<\/p>\n
\u0414\u043b\u044f \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f: <\/p>\n
set t=%1
ipconfig | find \u00abIPv4\u00bb > %temp%\\tempip.txt
for \/f \u00abtokens=1* delims=.: \u00bb %%a in (%temp%\\tempip.txt) do (echo %USERNAME%;%COMPUTERNAME%;%%b;%t%;%DATE%;%TIME% >> \\\\nas\\log$\\users\\%USERNAME%.domain1.ru.txt)
del %temp%\\tempip.txt<\/p><\/blockquote>\n
usage \\\\domain\\netlogon\\logon.cmd logon\/logoff<\/p>\n
\u0414\u043b\u044f \u041f\u041a: <\/p>\n
set t=%1
ipconfig | find \u00abIPv4\u00bb > %temp%\\tempip.txt
for \/f \u00abtokens=1* delims=.: \u00bb %%a in (%temp%\\tempip.txt) do (echo %COMPUTERNAME%;%%b;%t%;%DATE%;%TIME% >> \\\\nas\\log$\\computers\\%COMPUTERNAME%.domain1.ru.txt)
del %temp%\\tempip.txt<\/p><\/blockquote>\n
\u041e\u0441\u0442\u0430\u0435\u0442\u0441\u044f \u0440\u0430\u0441\u043f\u0430\u0440\u0441\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435 \u0438 \u043a\u0443\u0434\u0430-\u043d\u0438\u0431\u0443\u0434\u044c \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c.
(2 \u043c\u043e\u0434\u0443\u043b\u044f: \u043f\u043a \u0438 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438. \u041a\u043e\u0434 \u0431\u0435\u0437 \u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u0435\u0432.) <\/p>\n
\u0418 \u043f\u0438\u0441\u0430\u0442\u044c \u0432 \u0431\u0430\u0437\u0443 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e powershell<\/b><\/p>\n\n
import-module activedirectory [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") $server= "localhost" $username= "logon" $password= "" $database= "logon" Set-Variable SqlConnection (New-Object MySql.Data.MySqlClient.MySqlConnection) -Scope Global -Option AllScope -Description "Personal variable for Sql Query functions" $SqlConnection.ConnectionString = "server=$server;user id=$username;password=$password;database=$database;pooling=false;Allow Zero Datetime=True;" if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } function global:Get-SqlDataTable( $Query = $(if (-not ($Query -gt $null)) {Read-Host "Query to run"}) ) { #\tif (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } \t$SqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand $Query, $SqlConnection \t$SqlAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter \t$SqlAdapter.SelectCommand = $SqlCmd \t$DataSet = New-Object System.Data.DataSet \t$SqlAdapter.Fill($DataSet) | Out-Null \treturn $DataSet.Tables[0] } function global:Insert-SqlDataTable( $Query = $(if (-not ($Query -gt $null)) {Read-Host "Query to run"}) ) { #\tif (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() } \t$SqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand $Query, $SqlConnection \t$SqlAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter $SqlAdapter.InsertCommand = $SqlCmd $SqlAdapter.InsertCommand.ExecuteNonQuery() } $events = Get-SqlDataTable 'select * from events' $domain = 1; $fusers = $null if ((get-childitem \\\\nas\\log$\\users | measure).count -gt 0){ $fusers += get-childitem \\\\nas\\log$\\users } if ((get-childitem \\\\nas.d2.local\\auth_log$\\users | measure).count -gt 0){ $fusers += get-childitem \\\\nas.d2.local\\auth_log$\\users } foreach ($fuser in $fusers){ if ($fuser.name -match "domain1"){ $domain = 1 } if ($fuser.name -match "domain2"){ $domain = 2 } write-host domain $domain -foregroundcolor blue write-host "\u041f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c:" $fuser.BaseName -foregroundcolor green if (!(test-path $fuser.fullname)){continue} $ustr = get-content $fuser.fullname $error_code = $null $res_sql_user = $null $res_sql_latestdata = $null $res_sql_latestlogons = $null foreach ($line in $ustr){ $line1 = $line -replace "['|()]" $arr = $line1.split(";") if ($arr[0]){ if ($domain -eq 1){ $uname = (get-aduser $arr[0]).name } if ($domain -eq 2){ $uname = (get-aduser $arr[0] -server dc.domain2).name } $sql_str = "select id from users where lower(samaccountname) = lower('" + $arr[0] + "') and domain_id = $domain order by id limit 1;"; $userid = (Get-SqlDataTable $sql_str).id if (!$userid){ $sql_str = "insert into users (domain_id, samaccountname, name) values ($domain, '" + $arr[0] + "', '$uname'); select id from users where lower(samaccountname) = lower('" + $arr[0] + "') and domain_id = $domain order by id limit 1;"; $userid = (Get-SqlDataTable $sql_str).id } } if ($arr[1]){ $compname = $arr[1] $cdomain = 1 if ($compname -match "cn1"){ $cdomain = 1 } if ($compname -match "cn2"){ $cdomain = 2 } $sql_str = "select id from computers where lower(samaccountname) = lower('" + $arr[1] + "') order by id limit 1;"; $computerid = (Get-SqlDataTable $sql_str).id if (!$computerid){ $sql_str = "insert into computers (domain_id, samaccountname) values ($cdomain, '" + $arr[1] + "'); select id from computers where lower(samaccountname) = lower('" + $arr[1] + "') order by id limit 1;"; $computerid = (Get-SqlDataTable $sql_str).id write-host $sql_str -foregroundcolor yellow } } $ev = $events | ?{$_.event -eq $arr[3]} switch(($ev | measure).count){ 0 { \twrite-host $arr[3] "error" -foregroundcolor red \texit \t write-host $arr[3] "will be added" -foregroundcolor yellow $sql_str = "insert into events (event) values ('" + $arr[3] + "')" Insert-SqlDataTable $sql_str $events = Get-SqlDataTable 'select * from events' \t$sql_str | out-file sql.txt -append } 1 { #write-host $arr[3] "select as current" -foregroundcolor white } default: {$error_code = 3; write-host $arr[3] "error" -foregroundcolor red; exit} } $curip = $arr[2] -replace "Address. . . . . . . . . . . :","" -replace "IPv4 Address. . : ","" -replace "IPv4-\u00a0\u00a4\u0430\u0490\u0431\u00a0 . . . . : ","" if ($curip){ $sql_str = "select id from ips where lower(ip) = lower('$curip') order by id limit 1;" $ipid = (Get-SqlDataTable $sql_str).id if (!$ipid){ $sql_str = "insert into ips (ip) values ('$curip'); select id from ips where lower(ip) = lower('$curip') order by id limit 1;"; write-host $sql_str -foregroundcolor yellow $ipid = (Get-SqlDataTable $sql_str).id } } if (!$userid){write-host u userid null; exit;} if (!$computerid){write-host u computerid null; exit;} if (!$ipid){write-host u ipid null; exit;} $eventid = ($events | ?{$_.event -eq $arr[3]}).id | select -first 1 #$ip = $arr[2] -replace "Address. . . . . . . . . . . :","" -replace "IPv4 Address. . : ","" #$dt = "{0:yyyy-MM-dd HH:mm:ss}" -f [datetime](($arr[4] + " " + $arr[5]) -replace '^(.*),.*$','$1') #$dt = get-date(($arr[4] + " " + $arr[5]) -replace '^(.*),.*$','$1') -format "yyyy-MM-dd hh:mm:ss zzz" #$dt = get-date(($arr[4] + " " + $arr[5])) -format "yyyy-MM-dd hh:mm:ss zzz" $t = ($arr[5] -replace '^(.*),.*$','$1').split(":") $dt = get-date((get-date($arr[4])).Addhours($t[0]).AddMinutes($t[1]).Addseconds($t[2])) -format "yyyy-MM-dd HH:mm:ss" if ($arr[0]){ if ($domain -eq 1){ $groups = ((Get-ADuser $arr[0] -Property memberof).memberof | %{Get-ADGroup -Identity $_} | sort name).name } if ($domain -eq 2){ $groups = ((Get-ADuser $arr[0] -server dc.domain2 -Property memberof).memberof | %{Get-ADGroup -Identity $_ -server dc.domain2 } | sort name).name } #$groups = ((Get-ADuser $arr[0] -Property memberof).memberof | %{Get-ADGroup -Identity $_} | sort name).name $val1 = "" $val2 = "" $groupscnt = ($groups | measure).count #write-host $groupscnt -foregroundcolor red for ($i = 0; $i -lt $groupscnt; $i++){ $gr = $groups[$i] -replace "'","\\'" $sql_str = "select id from groups where name = '$gr'" $groupid = (Get-SqlDataTable $sql_str).id if (!$groupid){ $sql_str = "insert into groups (name) values ('$gr');" $res = Get-SqlDataTable $sql_str } $val2 = $val2 + "'" + $gr + "'" if ($i -lt $groupscnt - 1){$val2 = $val2 + ","} } $sql_str = "select id from groups where name in ($val2);" $groupsid = (Get-SqlDataTable $sql_str).id $groupscnt = ($groupsid | measure).count $val3 = "" for ($i = 0; $i -lt $groupscnt; $i++){ $val3 = $val3 + "($userid, " + $groupsid[$i] + ", 1, '$dt')" if ($i -lt $groupscnt - 1){$val3 = $val3 + ","} } $sql_str = "insert into membership (uid, gid, used, user_dt) values $val3 on duplicate key update used=values(used), user_dt=values(user_dt); update membership SET used=0 WHERE uid = $userid and user_dt != '$dt'" #$sql_str $res = Get-SqlDataTable $sql_str #$res } # $datetime = "{0:yyyy-MM-dd hh:mm:ss}" -f [datetime]$dt #::ParseExact($dt, "dd.MM.yyyy hh:mm:ss",$null) #$datetime = get-date($dt) # $datetime $res_sql_user += "insert into logonusers (domain_id, user_id, computer_id, event_id, ip_id, dt) values ($domain, $userid, $computerid, $eventid, $ipid, '$dt');" $res_sql_latestdata += "insert into latestdata (domain_id, computer_id, user_id, user_event_id, ip_id, user_dt) values ($domain, $computerid, $userid, $eventid, $ipid, '$dt') on duplicate key update computer_id=values(computer_id), user_id=values(user_id), user_event_id=values(user_event_id), user_dt=values(user_dt), ip_id=values(ip_id);" if ($eventid -eq 2){ $res_sql_latestlogons += "insert into latestlogons (domain_id, computer_id, user_id, user_event_id, ip_id, user_dt, processlogoff) values ($domain, $computerid, $userid, $eventid, $ipid, '$dt', 0) on duplicate key update computer_id=values(computer_id), user_id=values(user_id), user_event_id=values(user_event_id), user_dt=values(user_dt), ip_id=values(ip_id), processlogoff=values(processlogoff);" } if ($eventid -eq 1){ $res_sql_latestlogons += "delete from latestlogons where computer_id = $computerid and user_id = $userid limit 1;" } # $sql = "insert into logon () values"; } if (!$error_code){ $out = Insert-SqlDataTable $res_sql_user $res_sql_user $out = Insert-SqlDataTable $res_sql_latestdata $res_sql_latestdata $out = Insert-SqlDataTable $res_sql_latestlogons $res_sql_latestlogons remove-item $fuser.fullname -force } } ###################################################################### ###################################################################### ###################################################################### ###################################################################### ###################################################################### $fcomputers = $null if ((get-childitem \\\\nas\\log$\\computers | measure).count -gt 0){ $fcomputers += get-childitem \\\\nas\\log$\\computers } if ((get-childitem \\\\nas.d2.local\\auth_log$\\computers | measure).count -gt 0){ $fcomputers += get-childitem \\\\nas.d2.local\\auth_log$\\computers } foreach ($fcomputer in $fcomputers){ if ($fcomputer.name -match "domain1"){ $domain = 1 } if ($fcomputer.name -match "domain2"){ $domain = 2 } write-host domain $domain -foregroundcolor blue write-host "\u041f\u041a:" $fcomputer.BaseName -foregroundcolor green $cstr = get-content $fcomputer.fullname $error_code = $null $res_sql_computer = $null $res_sql_latestdata = $null foreach ($line in $cstr){ $arr = $line.split(";") if ($arr[0]){ $sql_str = "select id from computers where lower(samaccountname) = lower('" + $arr[0] + "') order by id limit 1;"; $computerid = (Get-SqlDataTable $sql_str).id if (!$computerid){ $sql_str = "insert into computers (domain_id, samaccountname) values ($domain, '" + $arr[0] + "'); select id from computers where lower(samaccountname) = lower('" + $arr[0] + "') order by id limit 1;"; $computerid = (Get-SqlDataTable $sql_str).id } } $ev = $events | ?{$_.event -eq $arr[2]} switch(($ev | measure).count){ 0 { write-host $arr[2] "will be added" -foregroundcolor yellow #$sql_str = "insert into events (event) values ('" + $arr[2] + "')" Insert-SqlDataTable $sql_str $events = Get-SqlDataTable 'select * from events' $sql_str | out-file sql.txt -append } 1 { #write-host $arr[3] "select as current" -foregroundcolor white } default: {$error_code = 3; write-host $arr[2] "error" -foregroundcolor red} } $curip = $arr[1] -replace "Address. . . . . . . . . . . :","" -replace "IPv4 Address. . : ","" -replace "IPv4-\u00a0\u00a4\u0430\u0490\u0431\u00a0 . . . . : ","" #write-host $arr[1] $curip #$error_code = 4 if ($curip){ $sql_str = "select id from ips where lower(ip) = lower('$curip') order by id limit 1;" $ipid = (Get-SqlDataTable $sql_str).id if (!$ipid){ $sql_str = "insert into ips (ip) values ('$curip'); select id from ips where lower(ip) = lower('$curip') order by id limit 1;"; $ipid = (Get-SqlDataTable $sql_str).id } } if (!$error_code){ $eventid = ($events | ?{$_.event -eq $arr[2]}).id | select -first 1 if (!$computerid){write-host c computerid null; exit;} if (!$ipid){write-host c ipid null; exit;} $t = ($arr[4] -replace '^(.*),.*$','$1').split(":") $dt = get-date((get-date($arr[3])).Addhours($t[0]).AddMinutes($t[1]).Addseconds($t[2])) -format "yyyy-MM-dd HH:mm:ss" $res_sql_computer += "insert into logoncomputers (domain_id, computer_id, event_id, ip_id, dt) values ($domain, $computerid, $eventid, $ipid, '$dt');" $res_sql_latestdata += "insert into latestdata (domain_id, computer_id, computer_event_id, computer_dt, ip_id) values ($domain, $computerid, $eventid, '$dt', $ipid) on duplicate key update computer_id=values(computer_id), computer_event_id=values(computer_event_id), computer_dt=values(computer_dt), ip_id=values(ip_id);" } } if (!$error_code){ $res_sql_computer $out = Insert-SqlDataTable $res_sql_computer $res_sql_latestdata $out = Insert-SqlDataTable $res_sql_latestdata remove-item $fcomputer.fullname -force } } $SqlConnection.Close() <\/code><\/pre>\n <\/div>\n<\/div>\n
\u0422\u0435\u043f\u0435\u0440\u044c \u0443 \u043d\u0430\u0441 \u0434\u0430\u043d\u043d\u044b\u0435 \u0441\u043e\u0445\u0440\u0430\u043d\u044f\u044e\u0442\u0441\u044f \u0432 \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445. \u0414\u0430\u043b\u0435\u0435 \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u0434\u0435\u043b\u0430\u0442\u044c \u0441 \u043d\u0438\u043c\u0438 \u0432\u0441\u0435 \u0447\u0442\u043e \u0443\u0433\u043e\u0434\u043d\u043e: <\/p>\n
\u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440 \u043d\u0430\u0440\u0438\u0441\u043e\u0432\u0430\u0442\u044c \u043f\u043b\u0430\u043d \u044d\u0442\u0430\u0436\u0430 \u0438 \u0432\u044b\u0432\u043e\u0434\u0438\u0442\u044c \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443<\/b><\/p>\n![](\"\/\/habrastorage.org\/files\/4fe\/c6f\/24c\/4fec6f24cf5e471a8a3b220b865dfed3.jpg\"\/)
\u0414\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438 \u0444\u043e\u0440\u043c\u0438\u0440\u0443\u0435\u0442\u0441\u044f \u0438\u0445 Excel \u0444\u0430\u0439\u043b\u043e\u0432 \u043d\u0430 \u0444\u0430\u0439\u043b\u043e\u0432\u043e\u0439 \u0448\u0430\u0440\u0435.
\u041f\u043e\u0438\u0441\u043a \u0432\u043e\u0437\u043c\u043e\u0436\u0435\u043d \u043f\u043e: <\/p>\n
\n- \u0418\u043c\u0435\u043d\u0438 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f<\/li>\n
- IP<\/li>\n
- \u0418\u043c\u0435\u043d\u0438 \u041f\u041a<\/li>\n
- \u0413\u0440\u0443\u043f\u043f\u0435<\/li>\n<\/ul>\n
<\/div>\n<\/div>\n
<\/p>\n
\u041f\u0440\u0438\u043c\u0435\u0440 \u043f\u043e\u0440\u0442\u0430\u043b\u0430<\/b><\/p>\n![](\"\/\/habrastorage.org\/files\/581\/09a\/90b\/58109a90bad74f8287749705cc082563.jpg\"\/)
<\/div>\n<\/div>\n
<\/p>\n
\u041e\u0442\u0447\u0435\u0442\u044b \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c<\/b><\/p>\n\n
\u0421\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u0438, \u0438\u043c\u0435\u044e\u0449\u0438\u0435 \u0432\u0445\u043e\u0434\u044b \u043d\u0430 \u0440\u0430\u0437\u043d\u044b\u0445 \u041f\u041a \u0437\u0430 \u0438\u0441\u043a\u043b\u044e\u0447\u0435\u043d\u0438\u0435\u043c \u0430\u0434\u043c\u0438\u043d\u0438\u0441\u0442\u0440\u0430\u0442\u043e\u0440\u043e\u0432<\/b><\/p>\n\n
SELECT (SELECT COUNT(*) FROM latestlogons t WHERE t.user_id = ll.user_id) AS cnt, u.samaccountname, ll.user_id, u.name, c.samaccountname, ll.computer_id, i.ip, ll.user_dt, bc.bc, ipn.tplname FROM latestlogons ll JOIN (SELECT * FROM users WHERE id NOT IN (SELECT DISTINCT u.id FROM users u JOIN membership m ON m.uid = u.id JOIN groups gr ON gr.id = m.gid WHERE gr.name IN ('Domain Admins'))) u ON u.id = ll.user_id JOIN computers c ON c.id = ll.computer_id JOIN ips i ON i.id = ll.ip_id ORDER BY cnt DESC , u.samaccountname <\/code><\/pre>\n <\/div>\n<\/div>\n
<\/p>\n
\u0438\u043b\u0438 \u043f\u0440\u043e\u0441\u0442\u043e \u0441\u0443\u043c\u043c\u0430\u0440\u043d\u043e \u043f\u043e \u0430\u043a\u0442\u0438\u0432\u043d\u044b\u043c \u0441\u0435\u0430\u043d\u0441\u0430\u043c<\/b><\/p>\n\n
SELECT COUNT(*) AS cnt, u.samaccountname, ll.user_id, u.name, c.samaccountname, ll.computer_id, i.ip FROM latestlogons ll JOIN users u ON u.id = ll.user_id JOIN computers c ON c.id = ll.computer_id JOIN ips i ON i.id = ll.ip_id GROUP BY user_id ORDER BY cnt DESC <\/code><\/pre>\n <\/div>\n<\/div>\n
<\/p>\n
\u0412\u0445\u043e\u0434\/\u0412\u044b\u0445\u043e\u0434 \u0441\u043e\u0442\u0440\u0443\u0434\u043d\u0438\u043a\u043e\u0432 \u0437\u0430 \u043f\u0440\u043e\u0448\u043b\u044b\u0435 \u0441\u0443\u0442\u043a\u0438 (\u0441 00:00:00 \u043f\u043e 23:59:59)<\/b><\/p>\n\n
SELECT d.name, u.samaccountname, u.name, c.samaccountname, e.event, lu.dt FROM logonusers lu JOIN users u ON u.id = lu.user_id JOIN computers c ON c.id = lu.computer_id JOIN ips i ON i.id = lu.ip_id JOIN events e ON e.id = lu.event_id JOIN domains d ON d.id = lu.domain_id WHERE (i.ip LIKE '172.16.16.%' OR i.ip LIKE '172.16.32%' OR i.ip LIKE '172.16.33%' OR i.ip LIKE '172.16.34%' OR i.ip LIKE '172.16.35%') AND lu.dt >= CURDATE() - INTERVAL 1 DAY AND lu.dt <= CURDATE() ORDER BY lu.dt <\/code><\/pre>\n <\/div>\n<\/div>\n
<\/p>\n
\u041d\u0430\u0439\u0442\u0438 \u0432\u0441\u0435\u0445 \u0441 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u043e\u0439 \u0433\u0440\u0443\u043f\u043f\u043e\u0439<\/b><\/p>\n\n
SELECT DISTINCT c.samaccountname, u.samaccountname, u.name, i.ip FROM `latestdata` ld LEFT JOIN computers c ON c.id = ld.computer_id LEFT JOIN users u ON u.id = ld.user_id LEFT JOIN `events` e ON e.id = ld.computer_event_id LEFT JOIN `events` e2 ON e2.id = ld.user_event_id LEFT JOIN `ips` i ON ld.ip_id = i.id LEFT JOIN membership m ON m.uid = u.id LEFT JOIN groups g ON g.id = m.gid WHERE LOWER(g.name) = LOWER('$key') <\/code><\/pre>\n <\/div>\n<\/div>\n
<\/div>\n<\/div>\n
\u041a\u0430\u043a \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u0437\u0430\u043b\u043e\u0447\u0435\u043d\u043d\u044b\u0439 \u044d\u043a\u0440\u0430\u043d \u0443 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f
\u0411\u0435\u0437 \u0430\u0443\u0434\u0438\u0442\u0430 \u0441\u043e\u0431\u044b\u0442\u0438\u0439 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0438 \u044d\u043a\u0440\u0430\u043d\u0430 \u043c\u043e\u0436\u043d\u043e \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u043e\u043c (\u043f\u0438\u0448\u0435\u043c \u0432 \u0444\u0430\u0439\u043b \u0438\u043b\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c \u0432\u0435\u0431\u0441\u0435\u0440\u0432\u0438\u0441) <\/p>\n
![](\"\/\/habrastorage.org\/files\/de1\/0a6\/06c\/de10a606cbce4e6ab3c0b155e16e01bc.jpg\"\/)
<\/p>\n![](\"\/\/habrastorage.org\/files\/a6a\/69b\/9d3\/a6a69b9d36924a45a4faa038f89ea87d.jpg\"\/)
<\/p>\n![](\"\/\/habrastorage.org\/files\/d4b\/b46\/7f2\/d4bb467f2b3d4d3c98dcf8c1f6239dca.jpg\"\/)
<\/p>\n