Инвентаризация железа с помощью powershell
04 Dec 2016В этом посте я опишу свой велосипед по сбору сведений об аппаратном обеспечении компьютеров. У меня это организовано с помощью скрипта на powershell, базы данных MySQL и системы мониторинга icinga2. Пока не буду описывать то, что связано с icinga2, так как это тема отдельной статьи.
Скрипт берет данный из wmi как с локальной так и с удаленной машины. Основной плюс моего решения, это то, что в скрипте жестко не прописаны классы и параметры которые нужно собирать, они берутся из базы данных. Добавлять параметры которые, мы хотим собирать можно практически на лету (естественно до запуска скрипта). Это решение было подсмотрено у WSUS, структуру таблиц я так же попытался сделать идентичной WSUS.
Совет дня. В WSUS можно включить инвентаризацию установив CollectClientInventory = True, в таблице tbConfigurationC. После чего клиенты будут отправлять на сервер сведения об аппаратном и программном обеспечении. Правда встроенного интерфейса для просмотра этих данных нет.
Машины идентифицируется по "Win32_ComputerSystemProduct.UUID-Win32_OperatingSystem.SerialNumber". Другого подходящего способа однозначно идентифицировать компьютер пока не нашел (имя компьютера может меняться, операционная система переустанавливаться, а UUID или серийный номер BIOS могут быть пустыми). Можно использовать GUID из реестра, который используется для крипто системы Windows, но с его получением на удаленных машинах могут быть проблемы.
В скрипте используется драйвер для работы с MySQL.
Скриншоты моего интерфейса - iciwebface. Отчет об аппаратных характеристиках:
Скрипт и дамп базы данных:
Скрипт сбора и отправки сведений в базу данных
```powershell
<#
icinga2scripts
Version 1.0
Description: Скрипт для Icinga 2 - Информация о железе рабочей станции
Pavel Satin (c) 2016
pslater.ru@gmail.com
#>
[Console]::OutputEncoding = [System.Text.Encoding]::UTF8
$returnStateOK = 0
$returnStateWarning = 1
$returnStateCritical = 2
$returnStateUnknown = 3
#$ErrorActionPreference = "SilentlyContinue"
$connString = "Server=192.168.0.209;Uid=inventory_user;Pwd=password;database=inventory;charset=utf8"
if ((Get-WmiObject -Class Win32_ComputerSystem).PartOfDomain) {
$myFQDN = (Get-WmiObject win32_computersystem).DNSHostName+"."+(Get-WmiObject win32_computersystem).Domain
} else {
$myFQDN = (Get-WmiObject win32_computersystem).DNSHostName
}
#Проверка аргументов
if ( $args[0] -ne $Null) {
$ComputerName = $args[0]
} else {
$ComputerName = "localhost"
}
if ( $args[1] -ne $Null) {
$myFQDN = $args[1]
}
#Функция выполнения MySQL запроса
function run-MySQLQuery {
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$query,
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$connectionString
)
try {
# load MySQL driver and create connection
Write-Verbose "Create Database Connection"
# You could also could use a direct Link to the DLL File
$mySQLDataDLL = "C:\ProgramData\icinga2\Scripts\icinga2\MySQL.Data.dll"
[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
#[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $ConnectionString
Write-Verbose "Open Database Connection"
$connection.Open()
# Run MySQL Querys
Write-Verbose "Run MySQL Querys"
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connection)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($dataSet, "data")
$dataSet.Tables["data"] # | Format-Table
}
catch {
Write-Host "Could not run MySQL Query" $Error[0]
}
Finally {
Write-Verbose "Close Connection"
$connection.Close()
}
} #Конец функции Run-MySQLQuery
#########################################################################################
#Замер времени исполнения скрипта
$watch = [System.Diagnostics.Stopwatch]::StartNew()
$watch.Start() #Запуск таймера
if ($ComputerName -eq ".") {
$result = $true
$addUUID = $myFQDN
} elseif ( $ComputerName -eq "localhost" ) {
$result = $true
$addUUID = $myFQDN
$ComputerName = $myFQDN
} else {
$addUUID = $ComputerName
$myFQDN = $ComputerName
$result = Test-Connection -ComputerName $ComputerName -Count 2 -Quiet
}
$myFQDN = $myFQDN.ToLower()
if ($result) {
$ComputerUUID = get-wmiobject Win32_ComputerSystemProduct -computername $ComputerName | Select-Object -ExpandProperty UUID
$OSSerial = get-wmiobject Win32_OperatingSystem -computername $ComputerName | Select-Object -ExpandProperty SerialNumber
$ComputerUUID = $ComputerUUID + "-" + $OSSerial
if ( $ComputerUUID -eq "00000000-0000-0000-0000-000000000000") {
$ComputerUUID = $ComputerUUID + "-" + $addUUID
} elseif ( $ComputerUUID -eq $Null ) {
$ComputerUUID = "00000000-0000-0000-0000-000000000000-" + $addUUID
}
#Write-Host "Обновляем данные системы в tbComputerTarget"
############################################################################
$sQLquery = "SELECT Name, ComputerTargetId FROM tbComputerTarget WHERE ComputerTargetId='$ComputerUUID'"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
[string]$LastReportedInventoryTime = get-date -Format "yyyy-MM-dd HH:mm:ss"
if ($rQLquery.ComputerTargetId -ne $Null) {
$sQLquery = "UPDATE tbComputerTarget SET Name='$myFQDN', LastReportedInventoryTime='$LastReportedInventoryTime' WHERE ComputerTargetId='$ComputerUUID'"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
} else {
$sQLquery = "INSERT INTO tbComputerTarget ( ComputerTargetId, Name, LastReportedInventoryTime ) VALUES ( '$ComputerUUID', '$myFQDN', '$LastReportedInventoryTime' )"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
}
##########################################################################
#Выбираем только включенные классы
$sQLquery = "SELECT ClassID, Name, Namespace, Enabled FROM tbInventoryClass WHERE Enabled = 1"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
$recordCount = 0
foreach ($row in $rQLquery) {
[string]$Win32ClassName = $row.Name
$Win32ClassID = $row.ClassID
#Удаляем старые записи этого класса
$sQLqueryDel = "DELETE FROM tbComputerInventory WHERE (ComputerTargetId='$ComputerUUID' AND ClassID = $Win32ClassID)"
$rQLqueryDel = run-MySQLQuery -connectionString $connString -query $sQLqueryDel
##Отладочный вывод названия обрабатываемого класса ###############################################################
#Write-Host $Win32ClassName
[string]$Win32Namespace = $row.Namespace
$computerClassI = Get-WMIObject -Namespace $Win32Namespace -Class $Win32ClassName -Computer $ComputerName
$InstanceId = 0
foreach ($computerClass in $computerClassI) {
$InstanceId = $InstanceId + 1
$sQLqueryProp = "SELECT PropertyID, ClassID, Name FROM tbInventoryProperty WHERE ClassID='$Win32ClassID'"
$rQLqueryProp = run-MySQLQuery -connectionString $connString -query $sQLqueryProp
foreach ($rowProp in $rQLqueryProp) {
$PropertyID = $rowProp.PropertyID
$PropertyName = $rowProp.Name
$Value = $computerClass.$PropertyName
$sQLDings = "INSERT INTO tbComputerInventory ( ComputerTargetId, ClassID, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$Win32ClassID', '$PropertyID', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
}
}
}
Write-Host "Inserted $recordCount entries in the database"
$watch.Stop() #Остановка таймера
Write-Host $watch.Elapsed #Время выполнения
Write-Host (Get-Date)
[System.Environment]::Exit($returnStateOK)
} #End if test-connection result
else {
Write-Host "Host $ComputerName is not available."
[System.Environment]::Exit($returnStateUnknown)
}
```Скрипт сбора и отправки сведений о ПО в базу данных
```powershell
<#
icinga2scripts
Version 1.0
Description: Скрипт для Icinga 2 - Информация о софте рабочей станции
Pavel Satin (c) 2017
pslater.ru@gmail.com
#>
[Console]::OutputEncoding = [System.Text.Encoding]::UTF8
$returnStateOK = 0
$returnStateWarning = 1
$returnStateCritical = 2
$returnStateUnknown = 3
#$ErrorActionPreference = "SilentlyContinue"
$connString = "Server=192.168.0.209;Uid=inventory_user;Pwd=password;database=inventory;charset=utf8"
if ((Get-WmiObject -Class Win32_ComputerSystem).PartOfDomain) {
$myFQDN = (Get-WmiObject win32_computersystem).DNSHostName+"."+(Get-WmiObject win32_computersystem).Domain
} else {
$myFQDN = (Get-WmiObject win32_computersystem).DNSHostName
}
#Проверка аргументов
if ( $args[0] -ne $Null) {
$ComputerName = $args[0]
} else {
$ComputerName = "localhost"
}
if ( $args[1] -ne $Null) {
$myFQDN = $args[1]
}
#Функция выполнения MySQL запроса
function run-MySQLQuery {
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$query,
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$connectionString
)
try {
# load MySQL driver and create connection
Write-Verbose "Create Database Connection"
# You could also could use a direct Link to the DLL File
$mySQLDataDLL = "C:\ProgramData\icinga2\Scripts\icinga2\MySQL.Data.dll"
[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
#[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $ConnectionString
Write-Verbose "Open Database Connection"
$connection.Open()
# Run MySQL Querys
Write-Verbose "Run MySQL Querys"
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connection)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($dataSet, "data")
$dataSet.Tables["data"] # | Format-Table
}
catch {
Write-Host "Could not run MySQL Query" $Error[0]
}
Finally {
Write-Verbose "Close Connection"
$connection.Close()
}
} #Конец функции Run-MySQLQuery
#########################################################################################
#Замер времени исполнения скрипта
$watch = [System.Diagnostics.Stopwatch]::StartNew()
$watch.Start() #Запуск таймера
if ($ComputerName -eq ".") {
$result = $true
$addUUID = $myFQDN
} elseif ( $ComputerName -eq "localhost" ) {
$result = $true
$addUUID = $myFQDN
$ComputerName = $myFQDN
} else {
$addUUID = $ComputerName
$myFQDN = $ComputerName
$result = Test-Connection -ComputerName $ComputerName -Count 2 -Quiet
}
$myFQDN = $myFQDN.ToLower()
if ($result) {
$ComputerUUID = get-wmiobject Win32_ComputerSystemProduct -computername $ComputerName | Select-Object -ExpandProperty UUID
$OSSerial = get-wmiobject Win32_OperatingSystem -computername $ComputerName | Select-Object -ExpandProperty SerialNumber
$ComputerUUID = $ComputerUUID + "-" + $OSSerial
if ( $ComputerUUID -eq "00000000-0000-0000-0000-000000000000") {
$ComputerUUID = $ComputerUUID + "-" + $addUUID
} elseif ( $ComputerUUID -eq $Null ) {
$ComputerUUID = "00000000-0000-0000-0000-000000000000-" + $addUUID
}
#Write-Host "Обновляем данные системы в tbComputerTarget"
############################################################################
$sQLquery = "SELECT Name, ComputerTargetId FROM tbComputerTarget WHERE ComputerTargetId='$ComputerUUID'"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
[string]$LastReportedInventoryTime = get-date -Format "yyyy-MM-dd HH:mm:ss"
$sQLqueryS = "SELECT ClassID, Name, Namespace, Enabled FROM tbInventoryClass WHERE Name = 'Win32_Product'"
$rQLqueryS = run-MySQLQuery -connectionString $connString -query $sQLqueryS
foreach ($row in $rQLqueryS) {
$ClassID = $row.ClassID
[string]$Win32ClassName = $row.Name
}
if ($rQLquery.ComputerTargetId -ne $Null) {
$sQLquery = "UPDATE tbComputerTarget SET Name='$myFQDN', LastReportedSoftInventoryTime='$LastReportedInventoryTime' WHERE ComputerTargetId='$ComputerUUID'"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
$sQLqueryDel = "DELETE FROM tbComputerSoftInventory WHERE ComputerTargetId='$ComputerUUID'"
$rQLqueryDel = run-MySQLQuery -connectionString $connString -query $sQLqueryDel
} else {
$sQLquery = "INSERT INTO tbComputerTarget ( ComputerTargetId, Name, LastReportedSoftInventoryTime ) VALUES ( '$ComputerUUID', '$myFQDN', '$LastReportedInventoryTime' )"
$rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery
}
##########################################################################
$recordCount = 0
$computerClassI = Get-WMIObject -Class $Win32ClassName -Computer $ComputerName | Sort-Object InstallDate –Descending
$InstanceId = 0
#$SnapshotId = 0
$sQLqueryProp = "SELECT PropertyID, ClassID, Name FROM tbInventoryProperty WHERE ClassID='$ClassID'"
$rQLqueryProp = run-MySQLQuery -connectionString $connString -query $sQLqueryProp
foreach ($computerClass in $computerClassI) {
$InstanceId = $InstanceId + 1
foreach ($rowProp in $rQLqueryProp) {
if ($rowProp.Name -eq "Name") {
$PropertyName = $rowProp.PropertyID
$Value = $computerClass.Name
$Value = $Value -replace "[']",""
$sQLDings = "INSERT INTO tbComputerSoftInventory ( ComputerTargetId, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$PropertyName', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
} elseif ($rowProp.Name -eq "Version") {
$PropertyName = $rowProp.PropertyID
$Value = $computerClass.Version
$sQLDings = "INSERT INTO tbComputerSoftInventory ( ComputerTargetId, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$PropertyName', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
} elseif ($rowProp.Name -eq "Vendor") {
$PropertyName = $rowProp.PropertyID
$Value = $computerClass.Vendor
$sQLDings = "INSERT INTO tbComputerSoftInventory ( ComputerTargetId, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$PropertyName', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
} elseif ($rowProp.Name -eq "InstallDate") {
$PropertyName = $rowProp.PropertyID
$Value = $computerClass.InstallDate
$sQLDings = "INSERT INTO tbComputerSoftInventory ( ComputerTargetId, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$PropertyName', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
} elseif ($rowProp.Name -eq "IdentifyingNumber") {
$PropertyName = $rowProp.PropertyID
$Value = $computerClass.IdentifyingNumber
$sQLDings = "INSERT INTO tbComputerSoftInventory ( ComputerTargetId, PropertyID, Value, InstanceId )
VALUES ( '$ComputerUUID', '$PropertyName', '$Value', '$InstanceId' )"
run-MySQLQuery -connectionString $connString -query $sQLDings
$recordCount ++
}
}
}
Write-Host "Inserted $recordCount entries in the database"
$watch.Stop() #Остановка таймера
Write-Host $watch.Elapsed #Время выполнения
Write-Host (Get-Date)
[System.Environment]::Exit($returnStateOK)
} #End if test-connection result
else {
Write-Host "Host $ComputerName is not available."
[System.Environment]::Exit($returnStateUnknown)
}
```Дамп MySQL для создания нужных таблиц
```sql
-- Adminer 4.2.5 MySQL dump
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `tbComputerInventory`;
CREATE TABLE `tbComputerInventory` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`ComputerTargetId` varchar(255) NOT NULL,
`ClassID` int(5) NOT NULL,
`PropertyID` int(5) NOT NULL,
`Value` varchar(256) NOT NULL,
`InstanceId` int(5) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `ClassID` (`ClassID`),
KEY `PropertyID` (`PropertyID`),
KEY `ComputerTargetId` (`ComputerTargetId`),
CONSTRAINT `tbComputerInventory_ibfk_2` FOREIGN KEY (`PropertyID`) REFERENCES `tbInventoryProperty` (`PropertyID`),
CONSTRAINT `tbComputerInventory_ibfk_3` FOREIGN KEY (`ClassID`) REFERENCES `tbInventoryClass` (`ClassID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `tbComputerSoftInventory`;
CREATE TABLE `tbComputerSoftInventory` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`ComputerTargetId` varchar(255) NOT NULL,
`PropertyID` int(5) NOT NULL,
`Value` varchar(256) NOT NULL,
`InstanceId` int(5) NOT NULL,
`SnapshotId` int(5) DEFAULT NULL,
`SnapshotDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ComputerTargetId` (`ComputerTargetId`),
KEY `PropertyID` (`PropertyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `tbComputerTarget`;
CREATE TABLE `tbComputerTarget` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`ComputerTargetId` varchar(255) NOT NULL,
`Name` varchar(256) NOT NULL,
`LastReportedInventoryTime` datetime DEFAULT NULL,
`LastReportedSoftInventoryTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ComputerTargetId` (`ComputerTargetId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `tbInventoryClass`;
CREATE TABLE `tbInventoryClass` (
`ClassID` int(5) NOT NULL,
`Name` varchar(256) NOT NULL,
`Namespace` varchar(256) NOT NULL DEFAULT 'root\\cimv2',
`Enabled` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `tbInventoryProperty`;
CREATE TABLE `tbInventoryProperty` (
`PropertyID` int(5) NOT NULL,
`ClassID` int(5) NOT NULL,
`Name` varchar(256) NOT NULL,
`Type` varchar(10) NOT NULL,
PRIMARY KEY (`PropertyID`),
KEY `ClassID` (`ClassID`),
CONSTRAINT `tbInventoryProperty_ibfk_1` FOREIGN KEY (`ClassID`) REFERENCES `tbInventoryClass` (`ClassID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP VIEW IF EXISTS `vwComputerInventory`;
CREATE TABLE `vwComputerInventory` (`Name` varchar(256), `PropertyID` int(5), `Value` varchar(256), `InstanceId` int(5));
DROP VIEW IF EXISTS `vwComputerSoftInventory`;
CREATE TABLE `vwComputerSoftInventory` (`Name` varchar(256), `PropertyID` int(5), `Value` varchar(256), `InstanceId` int(5));
DROP TABLE IF EXISTS `vwComputerInventory`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `vwComputerInventory` AS select `tbComputerTarget`.`Name` AS `Name`,`tbComputerInventory`.`PropertyID` AS `PropertyID`,`tbComputerInventory`.`Value` AS `Value`,`tbComputerInventory`.`InstanceId` AS `InstanceId` from (`tbComputerInventory` join `tbComputerTarget` on((`tbComputerInventory`.`ComputerTargetId` = `tbComputerTarget`.`ComputerTargetId`)));
DROP TABLE IF EXISTS `vwComputerSoftInventory`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `vwComputerSoftInventory` AS select `tbComputerTarget`.`Name` AS `Name`,`tbComputerSoftInventory`.`PropertyID` AS `PropertyID`,`tbComputerSoftInventory`.`Value` AS `Value`,`tbComputerSoftInventory`.`InstanceId` AS `InstanceId` from (`tbComputerSoftInventory` join `tbComputerTarget` on((`tbComputerSoftInventory`.`ComputerTargetId` = `tbComputerTarget`.`ComputerTargetId`)));
-- 2017-12-23 14:52:36
```