Exporting non VMware computer from AD Online computer list as .CSV?

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Post Reply
User avatar
ITEngineer
Posts: 187
Joined: Wed Oct 12, 2011 10:52 am

Exporting non VMware computer from AD Online computer list as .CSV?

Post by ITEngineer » Sun Feb 02, 2020 10:47 pm

Hello All,

I'm trying to get all Physical server using Powershell but somehow it is not returning any result as .CSV using my script below:

Code: Select all

Get-ADComputer -Properties OperatingSystem, OperatingSystemVersion, lastLogonTimestamp, lastLogon -Filter {Enabled -eq $True -and OperatingSystem -like "*Server*" } -SearchBase "DC=Domain,DC=com" |
  Where-Object {(Test-Connection $_.Name -Count 1 -Quiet) -and ((Get-WmiObject -ComputerName $_.Name -Class Win32_BIOS).SerialNumber -notlike "*VMware*") } |
  Select-Object -Property Name, 
    OperatingSystem, 
    OperatingSystemVersion,
    @{N="LastLogonTimeStamp";E={[datetime]::FromFileTime($_.LastLogonTimeStamp)}}, 
    @{N="LastLogon";E={[datetime]::FromFileTime($_.lastLogon)}} |
  ForEach-Object {
      Write-Host "Processing $($_.Name) ..." -ForegroundColor Green -BackgroundColor Black
        $cs = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $_.Name
        Select-Object -InputObject $_ -Property *, 
        @{Name="Model"; Expression={$cs.Model}}, 
        @{Name="UserName"; Expression={$cs.UserName}}, 
        @{Name="IPAddress"; Expression={(Resolve-DnsName -Name $cs.Name).IPAddress}}
  } | Export-Csv -Path 'C:\Result\OnlinePhysicalServers.csv' -NoTypeInformation -UseCulture
The error is shown on my screen:
Processing DCSVR-EX01 ... Get-WmiObject : The RPC server is unavailable.

At line:10 char:19
+ ... $cs = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], COMException
+ FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

Get-ADComputer : The server has returned the following error: invalid enumeration context.
At line:1 char:1
+ Get-ADComputer -Properties OperatingSystem, OperatingSystemVersion, l ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-ADComputer], ADException
+ FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.GetADComputer
I am executing this script under my DOMAIN\Administrator account in my DC, so not sure why it does not work with the above error and no .CSV file at all?

Thanks in advance.
/* IT Engineer */

jvierra
Posts: 14145
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by jvierra » Sun Feb 02, 2020 10:51 pm

You have issue with your network. That is not something that we can fix with a script. Have your network techs look at the error and check the server.

" The RPC server is unavailable."

This usually means either firewall issues or issues with the server. Restarting the server is the first easiest step.

User avatar
ITEngineer
Posts: 187
Joined: Wed Oct 12, 2011 10:52 am

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by ITEngineer » Mon Feb 03, 2020 5:10 pm

Yes, that does make sense.

Now I got some results by modifying and tidying the script like below:

Code: Select all

$servers = Get-ADComputer -Properties OperatingSystem, OperatingSystemVersion, lastLogonTimestamp, lastLogon -Filter {Enabled -eq $True -and OperatingSystem -like "*Server*"} -SearchBase "DC=Domain,DC=com"
$servers |
	Where-Object {(Test-Connection $_.Name -Count 1 -Quiet) -and ((Get-CimInstance -ComputerName $_.Name -Class Win32_BIOS).SerialNumber -notlike "*VMware*") } |
	ForEach-Object {
		Write-Host "Processing $($_.Name) ..." -ForegroundColor Green -BackgroundColor Black
		$cs = Get-CimInstance -Class Win32_ComputerSystem -ComputerName $_.Name
		$_ | Select-Object -Property `
			Name,
			OperatingSystem,
			OperatingSystemVersion,
			@{N="LastLogonTimeStamp";E={[datetime]::FromFileTime($_.LastLogonTimeStamp)}},
			@{N="LastLogon"; E={[datetime]::FromFileTime($_.lastLogon)}},
			@{Name="Model"; E={$cs.Model}},
			@{Name="UserName"; E={$cs.UserName}},
			@{Name="IPAddress"; E={(Resolve-DnsName -Name $cs.Name).IPAddress -join ', '}}
	} | Export-Csv -Path C:\Result\OnlinePhysicalServers.csv -NoTypeInformation -UseCulture
Remove-Variable servers
However, the IP address column and UserName is not showing for some online server that I can ping?
/* IT Engineer */

jvierra
Posts: 14145
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by jvierra » Mon Feb 03, 2020 8:09 pm

That is normal.
This should make it easier for you to understand what is happening here:

Code: Select all

$adprops = @(
    'IPv4Address',
    'OperatingSystem',
    'OperatingSystemVersion',
    'lastLogodate'
)
$properties = @(
    'Name',
    'OperatingSystem',
    'OperatingSystemVersion',
    'LastLogonDate',
    'Ipv4Address'
)
$filter = {
    Enabled -eq $true -and 
    OperatingSystem -like '*Server*'
}
Get-ADComputer -Properties $adprops -Filter $filter |
	Where-Object {
        Test-Connection $_.Name -Count 1 -Quiet -and 
        (Get-CimInstance -ComputerName $_.Name -Class Win32_BIOS).SerialNumber -notmatch 'VMware'
    } |
    Select-Object $properties |
    Export-Csv -Path C:\Result\OnlinePhysicalServers.csv -NoTypeInformation

User avatar
ITEngineer
Posts: 187
Joined: Wed Oct 12, 2011 10:52 am

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by ITEngineer » Mon Feb 03, 2020 8:38 pm

Mr Vierra, thank you for the pointer in this matter.

How do I include the column to show the ComputerModel & the LoggedOn user account?

Code: Select all

$adprops = @(
    'IPv4Address',
    'OperatingSystem',
    'OperatingSystemVersion',
    'lastLogondate'
)
$properties = @(
    'Name',
    'OperatingSystem',
    'OperatingSystemVersion',
    'LastLogonDate',
    'Ipv4Address'
)
$filter = {
    (Enabled -eq $true) -and 
    (OperatingSystem -like '*Server*')
}

Get-ADComputer -Properties $adprops -Filter $filter |
	Where-Object {
        (Test-Connection $_.Name -Count 1 -Quiet) -and 
        ((Get-CimInstance -ComputerName $_.Name -Class Win32_BIOS).SerialNumber -notmatch 'VMware')
    } |
    ForEach-Object {
        Write-Host "Processing $($_.Name) ..." -ForegroundColor Green -BackgroundColor Black
		$cs = Get-CimInstance -Class Win32_ComputerSystem -ComputerName $_.Name
		$_ | Select-Object $properties,
            @{Name="Model"; E={$cs.Model}},
			@{Name="UserName"; E={$cs.UserName}},
			@{Name="IPAddress"; E={(Resolve-DnsName -Name $cs.Name).IPAddress -join ', '}}
    } |
    Export-Csv -Path C:\TEMP\OnlinePhysicalServers.csv -NoTypeInformation
Last edited by ITEngineer on Mon Feb 03, 2020 8:42 pm, edited 1 time in total.
/* IT Engineer */

jvierra
Posts: 14145
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by jvierra » Mon Feb 03, 2020 8:41 pm

Add them to the properties of the select.

User avatar
ITEngineer
Posts: 187
Joined: Wed Oct 12, 2011 10:52 am

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by ITEngineer » Mon Feb 03, 2020 8:56 pm

jvierra wrote:
Mon Feb 03, 2020 8:41 pm
Add them to the properties of the select.
Already change it like this:

Code: Select all

$adprops = @(
    'IPv4Address',
    'OperatingSystem',
    'OperatingSystemVersion',
    'lastLogondate'
)
$properties = @(
    'Name',
    'OperatingSystem',
    'OperatingSystemVersion',
    'LastLogonDate',
    'Ipv4Address'
)
$filter = {
    (Enabled -eq $true) -and 
    (OperatingSystem -like '*Server*')
}

Get-ADComputer -Properties $adprops -Filter $filter |
	Where-Object {
        (Test-Connection $_.Name -Count 1 -Quiet) -and 
        ((Get-CimInstance -ComputerName $_.Name -Class Win32_BIOS).SerialNumber -notmatch 'VMware')
    } |
    ForEach-Object {
        Write-Host "Processing $($_.Name) ..." -ForegroundColor Green -BackgroundColor Black
		$cs = Get-CimInstance -Class Win32_ComputerSystem -ComputerName $_.Name
		$_ | Select-Object $properties,
            @{Name="Model"; E={$cs.Model}},
			@{Name="UserName"; E={$cs.UserName}},
			@{Name="IPAddress"; E={(Resolve-DnsName -Name $cs.Name).IPAddress -join ', '}}
    } |
    Export-Csv -Path C:\Result\OnlinePhysicalServers.csv -NoTypeInformation
However, I got this error:
Select-Object : Cannot convert System.Object[] to one of the following types {System.String, System.Management.Automation.ScriptBlock}.
At line:27 char:8
+ $_ | Select-Object $properties,
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Select-Object], NotSupportedException
+ FullyQualifiedErrorId : DictionaryKeyUnknownType,Microsoft.PowerShell.Commands.SelectObjectCommand
Get-ADComputer : The server has returned the following error: invalid enumeration context.
At line:19 char:1
+ Get-ADComputer -Properties $adprops -Filter $filter ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-ADComputer], ADException
+ FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.GetADComputer
/* IT Engineer */

jvierra
Posts: 14145
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by jvierra » Mon Feb 03, 2020 9:02 pm

Or you an just do this:

Code: Select all

$adprops = @(
    'IPv4Address',
    'OperatingSystem',
    'OperatingSystemVersion',
    'lastLogodate'
)
$filter = {
    Enabled -eq $true -and 
    OperatingSystem -like '*Server*'
}
Get-ADComputer -Properties $adprops -Filter $filter |
	ForEach-Object {
        if(
            (Test-Connection $_.Name -Count 1 -Quiet) -and
            (Get-CimInstance Win32_BIOS -ComputerName $_.Name).SerialNumber -notmatch 'VMware'
        ){
            $cs = Get-CimInstance Win32_ComputerSystem -ComputerName $_.Name
            [pscustomobject]@{
                Name = $_.Name
                OperatingSystem = $_.OperatingSystem
                OperatingSystemVersion = $_.OperatingSystemVersion
                LastLogonDate = $_.LastLogonDate
                Ipv4Address = $_.Ipv4Address
                Model = $cs.Model
                UserName = $cs.Username
            }
        }
    } |
    Export-Csv -Path C:\Result\OnlinePhysicalServers.csv -NoTypeInformation

User avatar
ITEngineer
Posts: 187
Joined: Wed Oct 12, 2011 10:52 am

Re: Exporting non VMware computer from AD Online computer list as .CSV?

Post by ITEngineer » Wed Feb 05, 2020 9:30 pm

that is awesome.

thanks, Mr Vierra.
/* IT Engineer */

Post Reply