Author Posts

May 4, 2018 at 1:12 pm

I have a script that is quite long, too long to post here. It does a test-connection on a list of machines and if the machine is online it will then grab information from the WMI. Once it gets that information it writes it to Excel in real time. So Excel will be opened automatically and data gets written to it. The problem is it takes forever for this script to run since it reads the list of machines one at a time. I wanted to use a Workflow but I can't get it to work with the whole Excel part. I've tried using an inline script and I still can't figure it out. Is it possible to to have your inline script section read a file (or OU from AD) and then write to Excel and do it in blocks of 100 computers....and wrap all of this within a Workflow? My goal is to be able to run my WMI commands against thousands of machines and have it run in minutes, not hours.

May 4, 2018 at 1:52 pm

I wouldn't use a workflow for this. And nor would I try to run thousands of connection commands in parallel. That seems liable to be almost a self-DDoS act right there.

I would look into implementing runspaces for this. You'll be able to manage and do, say ten or twenty connection requests at a time, picking up the next when one is complete, which should help keep things sane and not take forever.

If you find the runspace code hard to work with, and I don't blame you — you gotta delve into .NET for it, and it's not super plain to use, I'd recommend the PoshRSJob module to simplify things for you.

May 4, 2018 at 2:02 pm

This code works fine. where I struggle is making this workflow run and write to Excel at the same time. each time I try to add the Excel part PowerShell indicates I need an inline script. Where in this Workflow would I add the Inline script part?

workflow Get-PCData {

$computers = (Get-ADComputer -Filter * -SearchBase "ou=remote;ou=workstations,dc=acme,dc=org").Name

ForEach -Parallel -ThrottleLimit 100 ($machine in $computers) {

if (Test-Connection -ComputerName $machine -Count 1 ) {

#$results = foreach ($machine in $computers) {
$ComputerInfo = Get-WMIObject -class Win32_ComputerSystem -PSComputerName $machine -Property * | Select-Object name, model
$computerName = $ComputerInfo.name
$computerModel = $ComputerInfo.Model
$osInfo= Get-WMIObject -Class Win32_Operatingsystem -PSComputerName $machine
$getip = ([version](Test-Connection $machine -Count 1).IPV4Address.IPAddressToString).Build;
$desc = (Get-ADComputer $machine -Properties Description).description | Select-Object Description
$BiosInfo = Get-WmiObject -Class Win32_BIOS -PSComputerName $machine -Property * | Select-Object SMSBIOSBIOSVersion, Manufacturer
$BIOSVer = $BiosInfo.SMBIOSBIOSVersion
$BIOSMan = $BiosInfo.Manufacturer
#$OSInstall = (get-wmiobject win32_operatingsystem -ComputerName $machine).converttodatetime((get-wmiobject win32_operatingsystem -ComputerName $machine).installdate)

[PSCustomObject]@{
PCName=$computerName
'OS Version'=$osInfo.Version
'OS Type'=$osInfo.Caption
'IP vLan'=$getip
'Description'=$desc
'Manufacturer'=$BIOSMan
'Model'=$computerModel
'BIOS Version'=$BIOSVer
#'Date OS was Installed' = $OSInstall
}
}}}

Get-PCData

May 4, 2018 at 2:07 pm

Writing to Excel is going to be bottleneck, it typically uses COM. I would personally avoid it like the plague. To start I would concentrate on getting the data from the remote systems with WMI. Get-CIMInstance should be leveraged versus Get-WMIObject. Maybe start with a foreach -parallel and test performance. Once you have all of the data, then you work on putting it somewhere. Even if you wanted it in Excel, I would put it in SQL and then use it as a data source rather than direct injection into Excel or export the data as a CSV.

May 4, 2018 at 7:17 pm

It's my understanding that there's a hard limit to Workflow and the concurrent processes: 5
I'm not in anyway contradicting anything said in comments above but I do something similar to what you describe and use Workflow, and export to Excel.
I run a set of tsql series across about 400 databases hosted on 11 different servers, using Workflow. In my case the it runs the query simultaneously on 5 servers (the limit of Workflow) and on each server in 5 databases simultaneously.
I use the importexcel module from the gallery: https://www.powershellgallery.com/packages/ImportExcel/4.0.9.
In my case, there used to be a BA that sat up most the night once a month running this manually, it's now done in under 3 mins from start to emailed xls received.

I have been looking into runspace to see if this would be a better option for my task.