从powershell插入值到Access数据库
问题描述:
我试图将我从端点收集的值插入到使用Powershell的访问数据库中。我对许多SQL语法不熟悉,目前难以将我收集的值插入到现有表中。下面是我的代码,完整地说明了除了进入数据库之外的所有工作。任何人都可以请我的意见,我做错了什么,我可能会去做这个工作?从powershell插入值到Access数据库
#import necessary modules
Import-Module ActiveDirectory
#Get the Hostname of the Compuer
$Hostname = $env:COMPUTERNAME
#Get The Model of The Computer
$SystemModel = (systeminfo | Select-String 'System Model:').ToString().Split(':')[1].Trim()
#Determine The Form Factor of the Computer
If ($SystemModel -like 'Optiplex*'){
$FormFactor = 'Desktop'
}
ElseIf ($SystemModel -like 'Latitude*'){
$FormFactor = 'Laptop'
}
Write-Host $FormFactor
#Get The manufacturer of The Computer
$SystemManufacturer = (Systeminfo |Select-String 'System Manufacturer:').ToString().Split(':')[1].Trim()
#Get Serial number of Computer
$ServiceTag = (wmic bios get serialnumber).Split("\n")[2].Trim()
#Get Total memory Installed on the Machine.
$TotalPhysicalMemory = (systeminfo | Select-String 'Total Physical Memory:').ToString().Split(':')[1].Trim()
#Get The Processor Installed in the Machine.
$ProcessorInstalled = Get-wmiobject win32_processor |Select -expand name
function VerifyMachineAssignment($Hostname){
#STEP 1: Get The User's Given name From Active Directory to Compare Against Computer Description
$RawUserName = (Get-ADUser $env:USERNAME | Select-Object -ExpandProperty name).ToString()
#STEP 2: Clean Up the Username you Found in AD So that The Filter understands it.
$UserDescribed = $RawUserName + '*'
#STEP 3: Search the Users OU for an Object Whose Description Matches the Variable from Step 3
$MachineName = (Get-AdComputer -filter {(Description -Like $UserDescribed)} -properties CN | Select-String 'CN').ToString().Split(',')[0].Trim().Split('=')[1]
#STEP 4: Compare Localhost to AD Machine Name To Determine Proper Assignment
If($MachineName -eq $Hostname){
$Global:MachineVerification = 'yes'
}
ELSE{
$Global:MachineVerification = 'no'
}
}
function WriteToDatabase($Hostname, $FormFactor, $SystemManufacturer,$SystemModel, $ServiceTag, $TotalPhysicalMemory, $ProcessorInstalled, $MachineVerification){
# Open a connection To The Database Containing our Inventory Data
if ($FormFactor -like 'Laptop'){
$query = "Select * from Laptops"
$cursor = 2
$lock = 3
$recordset = New-Object -ComObject ADODB.Recordset
$Ado = New-Object -ComObject ADODB.Connection
$Ado.open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=Documents\CorpInventory.accdb")
$recordset.open($query,$ado,$cursor,$lock)
$recordset.Addnew()
$recordset.Fields.Item("Hostname") = $Hostname
$recordset.Fields.Item("FormFactor") = $FormFactor
$recordset.Fields.Item("Make") = $SystemManufacturer
$recordset.Fields.Item("Model") = $SystemModel
$recordset.Fields.Item("ServiceTag") = $ServiceTag
$recordset.Fields.Item("MemoryInstalled") = $TotalPhysicalMemory
$recordset.Fields.Item("ProcessorInstalled") = $ProcessorInstalled
$recordset.Fields.Item("AssignmentVerified") = $MachineVerification
$recordset.Update()
$recordset.close()
$ado.close()
echo "Hit the DB Add for Laptops"
}
Elseif ($FormFactor -like 'Laptop'){
$query = "Select * from Desktops"
$cursor = 2
$lock = 3
$recordset = New-Object -ComObject ADODB.Recordset
$Ado = New-Object -ComObject ADODB.Connection
$Ado.open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=Documents\CorpInventory.accdb")
$recordset.open($query,$ado,$cursor,$lock)
$recordset.Addnew()
$recordset.Fields.Item("Hostname") = $Hostname
$recordset.Fields.Item("FormFactor") = $FormFactor
$recordset.Fields.Item("Make") = $SystemManufacturer
$recordset.Fields.Item("Model") = $SystemModel
$recordset.Fields.Item("ServiceTag") = $ServiceTag
$recordset.Fields.Item("MemoryInstalled") = $TotalPhysicalMemory
$recordset.Fields.Item("ProcessorInstalled") = $ProcessorInstalled
$recordset.Fields.Item("AssignmentVerified") = $MachineVerification
$recordset.Update()
$recordset.close()
$ado.close()
echo "Hit the DB Add for Desktops"
}
}
VerifyMachineAssignment
WriteToDatabase($Hostname, $FormFactor, $SystemManufacturer,$SystemModel, $ServiceTag, $TotalPhysicalMemory, $ProcessorInstalled, $MachineVerification)
Write-Host "The Name of the computer is: $Hostname "
Write-Host "The Type of Computer is: $FormFactor"
Write-Host "The Manufacturer of the computer is $SystemManufacturer"
Write-Host "The System model Is: $SystemModel "
Write-Host "The Service Tag for This Machine Is: $ServiceTag "
Write-Host "The amount of System Memory is: $TotalPhysicalMemory"
Write-Host "The Processor Installed Is: $ProcessorInstalled"
Write-Host "Does The Machine Assignment Match AD? $MachineVerification"
答
我的解决方案最终被创造与价值,我跟踪,然后附加这些值到CSV文件的数组的自定义对象。
$Info = [pscustomobject][ordered]@{
"Hostname" = $Hostname
"FormFactor" = $FormFactor
"SystemManufacturer" = $SystemManufacturer
"SystemModel" = $SystemModel
"ServiceTag" = $ServiceTag
"Memory" = $TotalPhysicalMemory
"Processor" = $ProcessorInstalled
"OS" = $OperatingSystem
}
$Info | Export-Csv -Path "Inventory.csv" -NoTypeInformation -Append