使用PowerShell查看Windows 补丁记录并写入数据库

最新推荐文章于 2024-03-04 11:12:46 发布

程序猿小泽

最新推荐文章于 2024-03-04 11:12:46 发布

阅读量1.3k

点赞数 3

分类专栏： Microsoft 数据库文章标签： sqlserver powershell

本文链接：https://blog.csdn.net/lizhengze1117/article/details/118114017

版权

Microsoft 同时被 2 个专栏收录

9 篇文章 1 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

知识储备

在PowerShell 中HashTable 的表示方法如下

#定义
$HashTableArray = @(
    @{
        Description = 'Update ';
        HotFixID = 'KB5003254';
        InstalledBy = 'NT AUTHORITY\SYSTEM';
        InstalledOn= '2021/6/13'
    }
)
#插入
$HashTableArray.score='zz'
#查看
$HashTableArray.score
$HashTableArray["score"]
#更新、删除
$HashTableArray.score='xx'
$HashTableArray.Remove('score')
#遍历
Foreach ($HashTable in $HashTableArray) {
    $HashTable.Description
    $HashTable.HotFixID
    $HashTable.InstalledBy
    $HashTable.InstalledOn
}

更多HashTable的操作可以查看这篇文章

Where-Object
根据对象的属性值从PowerShell庞大的结果集中筛选自己想要的对象

#使用Get-Process返回所有的当前进程,然后对相应属性进行过滤
PS C:\PowerShell> Get-Process | select -First 1 | fl *
Name                       : AGMService
Id                         : 4804
PriorityClass              : Normal
FileVersion                : 7.3.0.157
HandleCount                : 236
WorkingSet                 : 11440128
PagedMemorySize            : 4194304
PrivateMemorySize          : 4194304
VirtualMemorySize          : 100253696
TotalProcessorTime         : 00:00:00.1718750
SI                         : 0
Handles                    : 236
VM                         : 100253696
WS                         : 11440128
PM                         : 4194304
NPM                        : 14608
Path                       : C:\Program Files (x86)\Common Files\Adobe\AdobeGCClient\AGMService.exe
Company                    : Adobe Systems, Incorporated
CPU                        : 0.171875
ProductVersion             : 7.3.0.157 BuildVersion: 7.3; BuildDate: Wed Feb 17 2021 03:10:19
Description                : Adobe Genuine Software Service
Product                    : Adobe Genuine Software Service
__NounName                 : Process
BasePriority               : 8
ExitCode                   :
HasExited                  : False
ExitTime                   :
Handle                     : 3548
SafeHandle                 : Microsoft.Win32.SafeHandles.SafeProcessHandle
MachineName                : .
MainWindowHandle           : 0
MainWindowTitle            :
MainModule                 : System.Diagnostics.ProcessModule (AGMService.exe)
MaxWorkingSet              : 1413120
MinWorkingSet              : 204800
Modules                    : {System.Diagnostics.ProcessModule (AGMService.exe), System.Diagnostics.ProcessModule (ntdll.dll), System.Diagnostics.Pr
                             ocessModule (wow64.dll), System.Diagnostics.ProcessModule (wow64win.dll)...}
NonpagedSystemMemorySize   : 14608
NonpagedSystemMemorySize64 : 14608
PagedMemorySize64          : 4194304
PagedSystemMemorySize      : 129896
PagedSystemMemorySize64    : 129896
PeakPagedMemorySize        : 4726784
PeakPagedMemorySize64      : 4726784
PeakWorkingSet             : 13914112
PeakWorkingSet64           : 13914112
PeakVirtualMemorySize      : 106901504
PeakVirtualMemorySize64    : 106901504
PriorityBoostEnabled       : True
PrivateMemorySize64        : 4194304
PrivilegedProcessorTime    : 00:00:00.1406250
ProcessName                : AGMService
ProcessorAffinity          : 4095
Responding                 : True
SessionId                  : 0
StartInfo                  : System.Diagnostics.ProcessStartInfo
StartTime                  : 2021/6/21 23:41:00
SynchronizingObject        :
Threads                    : {4808, 6272, 7328}
UserProcessorTime          : 00:00:00.0312500
VirtualMemorySize64        : 100253696
EnableRaisingEvents        : False
StandardInput              :
StandardOutput             :
StandardError              :
WorkingSet64               : 11440128
Site                       :
Container                  :

#根据进程名过滤所有记事本进程
PS C:\PowerShell>  Get-Process | Where-Object {$_.Name -eq "teams"}

Handles  NPM(K)    PM(K)      WS(K)     CPU(s)     Id  SI ProcessName
-------  ------    -----      -----     ------     --  -- -----------
    341      20    30312      60608       0.72   2156   1 Teams
   1541     101   197404     341468     430.92   4624   1 Teams
    411      19    19252      42760      16.72   7032   1 Teams
    970      38   189744     144120      45.78   8228   1 Teams
   1389      56   134716     150292   1,363.42  12996   1 Teams
    340      28   168324     206376     179.33  14624   1 Teams
    296      18    11192      58436       1.05  15720   1 Teams
    447      36   238968     233248     122.34  15852   1 Teams
    262      16    22336      79912       0.06  24644   1 Teams

#根据company过滤所有产品发布者以”Microsoft”打头的进程：
PS C:\WINDOWS\system32> Get-Process | Where-Object {$_.company -like '*Microsoft*' }|
>> select Name,Description,Company

Name                 Description            Company
----                 -----------            -------
ApplicationFrameHost Application Frame Host Microsoft Corporation
BingWallpaperApp     Bing Wallpaper         Microsoft Corporation
Calculator           Calculator.exe         Microsoft Corporation
ChsIME               Microsoft IME          Microsoft Corporation
cmd                  Windows 命令处理程序   Microsoft Corporation
Code                 Visual Studio Code     Microsoft Corporation
Code                 Visual Studio Code     Microsoft Corporation
Code                 Visual Studio Code     Microsoft Corporation
CodeHelper           CodeHelper             Microsoft Corporation
dasHost              Device Association ... Microsoft Corporation

#使用别名
#因为Where-Object的使用概率比较高，所以有一个很形象的别名 ？ 可以使用：

PS C:\PowerShell> Get-Process | ? {$_.Name -like "google*"}

Handles  NPM(K)    PM(K)      WS(K)     CPU(s)     Id  SI ProcessName
-------  ------    -----      -----     ------     --  -- -----------
    172      10     1620       1116       0.08  11164   0 GoogleCrashHandler
    155       9     1668       1060       0.02  11260   0 GoogleCrashHandler64

更详细的说明可以查看官方文档

Selete-Object
与Where-Object 大同小异，具体用法查看官方文档
Get-Date
获取一个表示当前日期的DateTime对象

#列出了在过去100天内安装的所有更新。
Get-Hotfix|Where-Object {$_.Installedon -gt ((Get-Date).Adddays(-100))}
Source        Description      HotFixID      InstalledBy          InstalledOn
------        -----------      --------      -----------          -----------
DESKTOP-NI... Update           KB5003254     NT AUTHORITY\SYSTEM  2021/6/13 0:00:00
DESKTOP-NI... Update           KB4562830     NT AUTHORITY\SYSTEM  2021/4/16 0:00:00
DESKTOP-NI... Update           KB4577586     NT AUTHORITY\SYSTEM  2021/4/27 0:00:00
DESKTOP-NI... Security Update  KB4580325     NT AUTHORITY\SYSTEM  2021/4/19 0:00:00
DESKTOP-NI... Update           KB4589212     NT AUTHORITY\SYSTEM  2021/4/27 0:00:00
DESKTOP-NI... Security Update  KB5003637     NT AUTHORITY\SYSTEM  2021/6/13 0:00:00
DESKTOP-NI... Update           KB5003503     NT AUTHORITY\SYSTEM  2021/6/13 0:00:00

更多使用方法查看官方教程

使用正则表达式提取日期格式为 2012\6\21 0:00:00

\d{4}\\\d{1,}\\\d{1,}\s\d:\d{2}:\d{2}

获取windows 补丁记录

 方法一：Get-HotFix

Source         Description      HotFixID      InstalledBy          InstalledOn
------         -----------      --------      -----------          -----------
Server01       Update           KB4495590     NT AUTHORITY\SYSTEM  5/16/2019 00:00:00
Server01       Security Update  KB4470788     NT AUTHORITY\SYSTEM  1/22/2019 00:00:00
Server01       Update           KB4480056     NT AUTHORITY\SYSTEM  1/24/2019 00:00:00

#方法二：
wmic qfe list

在这里插入图片描述

#方法三：
gwmi win32_quickfixengineering

在这里插入图片描述

PowerShell 连接 SQLServer 数据库，并执行 sql 语句

#Configuration of the SQL Server
$Database	= 'SQL'
$Server		= '"SQLServerName"'
$UserName	= 'Username'
$Password	= 'Passward'

#New A Connection Object
$SqlConn = New-Object System.Data.SqlClient.SqlConnection

#Log Into SQL Server By Account
$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"

#Open SQL Connection 
$SqlConn.open()


$SqlConn = $SqlConn
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

#Define the InsertSQL Statement
$Description=$Patch.Description
$HotFixID=$Patch.HotFixID
$InstalledBy=$Patch.InstalledBy
$InstalledOn=$Patch.InstalledOn
$InsertSQL = "INSERT INTO PatchTable (Description,HotFixID,InstalledBy,InstalledOn) VALUES ('$Description','$HotFixID','$InstalledBy','$InstalledOn')"

$SqlCmd.CommandText = $InsertSQL
$SqlCmd.Connection = $SqlConn
$Reader= $SqlCmd.ExecuteReader()
$DataTable = New-Object System.Data.DataTable
$DataTable.Load($Reader)

#Close SQL Connection 
$SqlConn.close()

更多数据库操作请看官方教程

完整代码

数据库建表语句为：

CREATE TABLE PatchTable(
	PatchId int PRIMARY KEY IDENTITY,
	Description NVARCHAR(255) ,
	HotFixID NVARCHAR(255) ,
	InstalledBy NVARCHAR(255) ,
	InstalledOn datetime,
	InsertTime datetime DEFAULT GETDATE() 
);

PowerShell 语句为：

##Query The Patches By 2021/4/* And Insert Into The SQL Server

#Configuration of the SQL Server
$Database	= 'SQL'
$Server		= '"SQLServerName"'
$UserName	= 'Username'
$Password	= 'Passward'

#New A Connection Object
$SqlConn = New-Object System.Data.SqlClient.SqlConnection

#Log Into SQL Server By Account
$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"

#Open SQL Connection 
$SqlConn.open()

#Filter Patches By 2021/6/* 
$PatchArrays=gwmi win32_quickfixengineering | Where{ $_.InstalledOn.year -eq 2021 } | Where {$_.InstalledOn.month -eq 4 } 

Foreach ($Patch in $PatchArrays) {

    $SqlConn = $SqlConn
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
    #Define the InsertSQL Statement
    $Description=$Patch.Description
    $HotFixID=$Patch.HotFixID
    $InstalledBy=$Patch.InstalledBy
    $InstalledOn=$Patch.InstalledOn
    $InsertSQL = "INSERT INTO PatchTable (Description,HotFixID,InstalledBy,InstalledOn) VALUES ('$Description','$HotFixID','$InstalledBy','$InstalledOn')"

    $SqlCmd.CommandText = $InsertSQL
    $SqlCmd.Connection = $SqlConn
    $Reader= $SqlCmd.ExecuteReader()
    $DataTable = New-Object System.Data.DataTable
    $DataTable.Load($Reader)
}  

#Close SQL Connection 
$SqlConn.close()

运行之后，查看对应的数据库，发现插入成功，开心！！！
在这里插入图片描述

参考文献

Using PowerShell insert string values in SQL Server DB.
windows比cmd更强大的 WMIC命令使用详解
 PowerShell 脚本必备命令

程序猿小泽

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
使用PowerShell查看Windows 补丁记录并写入数据库

目录知识储备完整代码参考文献知识储备在PowerShell 中HashTable 的表示方法如下#定义$HashTableArray = @( @{ Description = 'Update '; HotFixID = 'KB5003254'; InstalledBy = 'NT AUTHORITY\SYSTEM'; InstalledOn= '2021/6/13' })#插入$HashTableArray.
复制链接

扫一扫

专栏目录