Page 1 of 1

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

Posted: Sun Feb 02, 2020 10:47 pm
by ITEngineer
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.

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

Posted: Sun Feb 02, 2020 10:51 pm
by jvierra
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.

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

Posted: Mon Feb 03, 2020 5:10 pm
by ITEngineer
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?

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

Posted: Mon Feb 03, 2020 8:09 pm
by jvierra
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

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

Posted: Mon Feb 03, 2020 8:38 pm
by ITEngineer
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

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

Posted: Mon Feb 03, 2020 8:41 pm
by jvierra
Add them to the properties of the select.

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

Posted: Mon Feb 03, 2020 8:56 pm
by ITEngineer
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

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

Posted: Mon Feb 03, 2020 9:02 pm
by jvierra
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

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

Posted: Wed Feb 05, 2020 9:30 pm
by ITEngineer
that is awesome.

thanks, Mr Vierra.