Powershell to CSV Help Please

tri_94 · December 3, 2018

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 · December 3, 2018

Are you intentionally using VB?

$ComputerName = read-host "Enter computer name"

TomFreeman · December 3, 2018

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

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

tri_94 · December 3, 2018

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 · December 4, 2018

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 · December 4, 2018

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 · December 4, 2018

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 · December 4, 2018

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 · December 5, 2018

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 · December 5, 2018

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 · December 5, 2018

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 · December 5, 2018

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 · December 6, 2018

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 · December 6, 2018

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 · December 6, 2018

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 · December 6, 2018

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 · December 6, 2018

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 · December 6, 2018

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

Powershell to CSV Help Please

Recommended Posts

tri_94

howartp

TomFreeman

tri_94

fordea

tri_94

fordea

howartp

tri_94

fordea

tri_94

tri_94

fordea

tri_94

fordea

tri_94

fordea

tri_94

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Moving MIS

Cloud RADIUS and Cloud PKI designed for Intune 802.1X

video Glitch in the Matrix?: A woman "disappeared" during a report on the Fragata Libertad

Home

Forums

What's New