Powershell to CSV Help Please

[tri_94]

Hi there

 

Can someone please give me a quick guide to be able to export data to csv into columns etc

 

So making a basic script to test is a pc is online and give me the ip address. But I would like to time and date stamp in the first two columns and then pc name in the next and ip address in the last.

 

 

Was planning to use something along the lines of

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

$result = Get-WmiObject win32_operatingsystem -ComputerName $ComputerName | select csname, @{LABEL='LastBootUpTime';EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}

#$result1 = (Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').IPAddress

[system.Windows.Forms.MessageBox]::Show("$result")

[system.Windows.Forms.MessageBox]::Show("Saved to Log file on D:")

$result | Export-Csv "D:\RebootPcLog.csv" -append

 

Thanks to anyone who is willing to help

[howartp]

Are you intentionally using VB?

 

$ComputerName = read-host "Enter computer name"

[TomFreeman]

Hi there

$result | Export-Csv "D:\RebootPcLog.csv" -append

 

 

I always use " | Out-File -filepath C:\Temp\Log.csv" on my scripts

[tri_94]

Are you intentionally using VB?

 

Not really just found that command and it seemed to do what I needed. Is there a better way?

[fordea]

This should be fairly straightforward to do. May I ask what the overall goal of the script is as it would be quicker/simpler to have Powershell retrieve host IP address/lease information from your DHCP server?

[tri_94]

it was firstly to find the last time pc was rebooted. Next to make sure its in the correct vlan which is why the ip

 

Thanks

[fordea]

In that case something like this should do the trick:

$Results = Invoke-Command -ComputerName $ComputerName -ErrorAction SilentlyContinue -ScriptBlock {

    $OSInfo = Get-CimInstance -ClassName Win32_OperatingSystem

    $IPInfo = Get-CimInstance -ClassName Win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"'

    $Date = Get-Date

    [PSCustomObject] @{

        Date = $Date.ToString('dd/MM/yyyy')

        Time = $Date.ToString('HH:mm:ss')

        Name = $env:computername

        IPAddress = $IPInfo.IPAddress | Where {$_ -notlike '*:*'}

        LastBootTime = $OSInfo.LastBootUpTime

    }

}

$Results | 

    Select-Object * -ExcludeProperty RunspaceID, PSComputerName |

    Export-Csv D:\RebootPcLog.csv -NoTypeInformation -Append

[/Code]

[howartp]

You'll need to prepend @fordea's code with mine from above, otherwise $ComputerName isn't populated:

 

$ComputerName = read-host "Enter computer name" 
$Results = Invoke-Command -ComputerName $ComputerName -ErrorAction SilentlyContinue -ScriptBlock {
   $OSInfo = Get-CimInstance -ClassName Win32_OperatingSystem
   $IPInfo = Get-CimInstance -ClassName Win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"'
   $Date = Get-Date
   [PSCustomObject] @{
       Date = $Date.ToString('dd/MM/yyyy')
       Time = $Date.ToString('HH:mm:ss')
       Name = $env:computername
       IPAddress = $IPInfo.IPAddress | Where {$_ -notlike '*:*'}
       LastBootTime = $OSInfo.LastBootUpTime
   }
}
$Results | 
   Select-Object * -ExcludeProperty RunspaceID, PSComputerName |
   Export-Csv D:\RebootPcLog.csv -NoTypeInformation -Append

[tri_94]

Thanks a lot for your help.

 

However when I use that I get a blank csv please help or am I doing something wrong?

[fordea]

If you've copied my code you'll need to do as howartp suggests and declare a $ComputerName variable as I forgot put that in. If you want to keep the method you've used to ask for a computer name you could just add these two lines above my code:

 

[color=#333333][system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null[/color]

[color=#333333]$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

[/color][/Code]
[color=#333333]
 

If you've already declared a $ComputerName variable then you may be getting a blank csv if the computer name is unreachable. If that still doesn't work then you might be using an older version of PowerShell. You can type $PSVersionTable into a PowerShell window to see what version you're using.[/color]

[tri_94]

using the code below and it generates a 0kb file with nothing in. Am I missing something?

 

 

 

 

 

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

 

$Results = Invoke-Command -ComputerName $ComputerName -ErrorAction SilentlyContinue -ScriptBlock {

$OSInfo = Get-CimInstance -ClassName Win32_OperatingSystem

$IPInfo = Get-CimInstance -ClassName Win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"'

$Date = Get-Date

[PSCustomObject] @{

Date = $Date.ToString('dd/MM/yyyy')

Time = $Date.ToString('HH:mm:ss')

Name = $env:computername

IPAddress = $IPInfo.IPAddress | Where {$_ -notlike '*:*'}

LastBootTime = $OSInfo.LastBootUpTime

}

}

$Results |

Select-Object * -ExcludeProperty RunspaceID, PSComputerName |

Export-Csv D:\RebootPcLog.csv -NoTypeInformation -Append

 

- - - Updated - - -

 

PSVersion 5.1.14393.2248

[tri_94]

running the code below for the same pc returns status and writes to the csv for machine name and last boot time. Its just i'd like to format and include ip and date of request.

 

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

$result = Get-WmiObject win32_operatingsystem -ComputerName $ComputerName | select csname, @{LABEL='LastBootUpTime';EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}

#$result1 = (Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter 'ipenabled = "true"').IPAddress

[system.Windows.Forms.MessageBox]::Show("$result")

[system.Windows.Forms.MessageBox]::Show("Saved to Log file on D:")

$result | Export-Csv "D:\RebootPcLog.csv" -append

[fordea]

So you probably don't have WinRM enabled in your environment which is why my code isn't working for you. The simplest way to modify your code would be by making use of your $result1 variable and using the Add-Member cmdlet:

 

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

$result = Get-WmiObject win32_operatingsystem -ComputerName $ComputerName | select csname, @{LABEL='LastBootUpTime';EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}

$result1 = (Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $ComputerName -Filter 'ipenabled = "true"').IPAddress

$result | Add-Member -MemberType NoteProperty -Name 'IP Address' -Value $result1

[system.Windows.Forms.MessageBox]::Show("$result")

[system.Windows.Forms.MessageBox]::Show("Saved to Log file on D:")

$result | Export-Csv "D:\RebootPcLog.csv" -Append -NoTypeInformation

[/Code]
 

If the above code ends up with 'System.Object' entries in your IP Address column in the csv then it's because your query to Win32_NetworkAdapterConfiguration is returning multiple IP addresses (probably an IPv4 and IPv6 address). If you only want the IPv4 address then you can modify the $result1 line:
 

[Code]



$result1 = ((Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $ComputerName -Filter 'ipenabled = "true"').IPAddress | Where {$_ -notlike '*:*'}) -join ';'



[/Code]

Edited December 6, 2018 by fordea

[tri_94]

thank you so much using the code below works great now how can I add date\time of the request to the csv? many thanks for all your help

 

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null

$ComputerName = [Microsoft.VisualBasic.Interaction]::InputBox("Enter computer name", "ComputerName", "$env:ComputerName")

$result = Get-WmiObject win32_operatingsystem -ComputerName $ComputerName | select csname, @{LABEL='LastBootUpTime';EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}

$result1 = ((Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $ComputerName -Filter 'ipenabled = "true"').IPAddress | Where {$_ -notlike '*:*'}) -join ';'

$result | Add-Member -MemberType NoteProperty -Name 'IP Address' -Value $result1

[system.Windows.Forms.MessageBox]::Show("$result")

[system.Windows.Forms.MessageBox]::Show("Saved to Log file on D:")

$result | Export-Csv "D:\RebootPcLog.csv" -Append -NoTypeInformation

[fordea]

Try add the following lines below where you use Add-Member to add the IP Address to $result:

 

$Date = Get-Date

$Result | Add-Member -MemberType NoteProperty -Name Date -Value ($Date.ToString('dd/MM/yyyy'))

$Result | Add-Member -MemberType NoteProperty -Name Time -Value ($Date.ToString('HH:mm:ss'))



[/Code]

[tri_94]

You are a star that works brilliant.

 

Could you tell me how I could change the first column from "csname" to Pc Name

 

Thanks again for all your help

[fordea]

You can change the initial $result assignment to include a calculated property for csname in Select-Object:

 

[color=#333333]$result = Get-WmiObject win32_operatingsystem -ComputerName $ComputerName |

    select @{LABEL='PC Name';EXPRESSION={$_.csname}},

           @{LABEL='LastBootUpTime';EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}

[/color][/Code]

[tri_94]

Awesome that's perfect. Many thanks again for your help