Change script from Excel object to CSV

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 6 months ago.

  • Author
    Posts
  • #5863

    by syncr0s at 2013-04-29 15:47:11

    Hi Oracles,

    I am fairly new to PS, and only have limited VBS experience, so any scripts I create are cobbled together from web searches and re-hashing my old work.

    I have cobbled together such a script to create an Excel spreadsheet querying servers, as shown below
    $erroractionpreference = “SilentlyContinue”

    $a = New-Object -comobject Excel.Application
    $a.visible = $True

    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = “Machine Name”
    $c.Cells.Item(1,2) = “CPU Processors”
    $c.Cells.Item(1,3) = “CPU LogicalProcessors”
    $c.Cells.Item(1,4) = “CPU Cores”
    $c.Cells.Item(1,5) = “CPU Description”
    $c.Cells.Item(1,6) = “Memory Physical”
    $c.Cells.Item(1,7) = “Memory Free Physical”
    $c.Cells.Item(1,8) = “Memory Virtual”
    $c.Cells.Item(1,9) = “Memory Free Virtual”
    $c.Cells.Item(1,10) = “NICS”

    $d = $c.UsedRange

    $intRow = 2

    $physicallist = Get-Content .\computers.txt
    ForEach ($computer in $physicallist) {
    $os = Get-WMIObject Win32_OperatingSystem -Computer $computer
    $proc = Get-WMIObject Win32_Processor -Computer $computer
    $cs = Get-WMIObject Win32_ComputerSystem -Computer $computer

    $c.Cells.Item($intRow,1) = $proc.SystemName
    $c.Cells.Item($intRow,2) = $cs.NumberofProcessors
    $c.Cells.Item($intRow,3) = $proc.NumberoflogicalProcessors
    $c.Cells.Item($intRow,4) = $proc.NumberofCores
    $c.Cells.Item($intRow,5) = $proc.Description
    $c.Cells.Item($intRow,6) = [int]($os.TotalVisibleMemorySize /1GB)
    $c.Cells.Item($intRow,7) = [int]($os.FreePhysicalMemory /1GB)
    $c.Cells.Item($intRow,8) = [int]($os.TotalVirtualMemorySize /1GB)
    $c.Cells.Item($intRow,9) = [int]($os.FreeVirtualMemory /1GB)

    $nics = Get-WmiObject -Computer $computer -query "Select Name From Win32_NetworkAdapter Where AdapterTypeID = 0 AND Manufacturer != 'Microsoft' AND NOT PNPDeviceID LIKE 'ROOT\\%'"
    $c.Cells.Item($intRow,10) = @($nics).count

    $count = 10
    $disks = Get-WMIObject Win32_LogicalDisk -Computer $computer -filter "DriveType=3"
    ForEach ($disk in $disks){
    $c.Cells.Item(1,$count) = “Disk letter”
    $c.Cells.Item(1,$count+1) = “Disk Size (GB)”
    $c.Cells.Item(1,$count+2) = “Free Space (GB)”
    $c.Cells.Item($intRow,$count) = $disk.DeviceID
    $c.Cells.Item($intRow,$count+1) = ($disk.size /1GB)
    $c.Cells.Item($intRow,$count+2) = ($disk.freespace /1GB)
    $count+=3
    }
    }
    $d.EntireColumn.AutoFit()

    I need to change this is a CSV creation script to have it run on a schedule, and available to be imported into an Oracle database(not owned by me).

    For simplicity's sake, I am working on getting the script functioning on a single host at the moment and will expand to the querying of remote computers which I have worked on above.

    I am fine with doing most of it, except the hard disk section where I write the header column as a new drive is seen an results entered, as shown below with the hard disk part still in the old format.
    $erroractionpreference = “SilentlyContinue”
    $Date = get-date
    $Datefile = ( get-date ).ToString(‘yyyy-MM-dd-hhmmss’)
    $PSumm = "" | Select SystemName,CPUProcessors,CPULogicalProcessors,CoresperProcessor,NICS,MemoryPhysical,MemoryFreePhysical,MemoryVirtual,MemoryFreeVirtual

    $os = Get-WMIObject Win32_OperatingSystem
    $cs = Get-WMIObject Win32_ComputerSystem

    $PSumm.SystemName = $cs.Name
    $PSumm.CPUProcessors = $cs.NumberofProcessors
    $PSumm.CPULogicalProcessors = $cs.NumberoflogicalProcessors
    $PSumm.CoresperProcessor = $cs.NumberoflogicalProcessors/$cs.NumberofProcessors
    $PSumm.MemoryPhysical = [int]($os.TotalVisibleMemorySize /1GB)
    $PSumm.MemoryFreePhysical = [int]($os.FreePhysicalMemory /1GB)
    $PSumm.MemoryVirtual = [int]($os.TotalVirtualMemorySize /1GB)
    $PSumm.MemoryFreeVirtual = [int]($os.FreeVirtualMemory /1GB)

    $nics = Get-WmiObject -query "Select Name From Win32_NetworkAdapter Where AdapterTypeID = 0 AND Manufacturer != 'Microsoft' AND NOT PNPDeviceID LIKE 'ROOT\\%'"
    $PSumm.NICS = @($nics).count

    $count = 10
    $disks = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"
    ForEach ($disk in $disks){
    $PSumm(1,$count) = “Disk letter”
    $PSumm(1,$count+1) = “Disk Size (GB)”
    $PSumm(1,$count+2) = “Free Space (GB)”
    $PSumm($intRow,$count) = $disk.DeviceID
    $PSumm($intRow,$count+1) = ($disk.size /1GB)
    $PSumm($intRow,$count+2) = ($disk.freespace /1GB)
    $count+=3
    }
    $report += $PSumm
    $report |Export-Csv .\Physical_Scan_results_$Datefile.csv -NoTypeInformation

    Can someone please suggest the best way to transform this section as I don't know in advance how many local disks there might be on any of the servers in the final list which is to be read in from the text file? I am very happy to learn by pointers and don't expect to be given the transformed script. How else would I learn more?

    I would assign bonus points (if there was such a thing) for a result which means that all data for the drive letters which are the same on each server will exist in the same column reference in the CSV.

    Thanks in advance.

    Geoff

    by MasterOfTheHat at 2013-05-01 08:26:15

    The only way I can think to put that kind of data into a CSV format is to have 3 columns per disk on each row and verify each row has the same number of columns... For instance, the lines for a server with a C:, D:, L:, and S: and then a server with a C: and L: would look like:
    Name,CPUProcessors,CPULogicalProcessors,CoresPerProcessor,MemoryPhysical,MemoryFreePhysical,MemoryVirtual,MemoryFreeVirtual,C_DiskLetter,C_DiskSize,C_FreeSpace,D_DiskLetter,D_DiskSize,D_FreeSpace,L_DiskLetter,L_DiskSize,L_FreeSpace,S_DiskLetter,S_DiskSize,S_FreeSpace
    Server01,4,16,4,192,50,256,25,C,100,20,D,500,360,L,150,20,S,1024,425
    Server02,2,8,4,192,50,256,25,C,100,46,,,,L,250,285,,,

    (Wow! That's ugly! 🙂 )

    In order to get to that point, though, you're going to have to ensure each server has the same number of columns, which means you need to know the drive letters of all disks on every server before you build the CSV. The only way to get that is to look at each server before creating the CSV...

    2 ways to approach this:
    [list=1][*]If you are going to be running this against the same servers every time, consider creating a template object that includes all of the possible drive letters, etc. That way you could push the data directly to your array of custom objects that will become your CSV. You would have to edit the code each time you added a new drive letter to the server, (if there wasn't already a placeholder for that drive letter), but that may not happen very often. I don't like this way because of the manual intervention required and the limited portability, but it will increase performance.[/*][*]Pull the info for each server, store the data in custom objects, keep track of all the drive letters used, create a new array with all of the columns to be in your CSV, (including each of the possible drive letters), and then write out all of the data to that new array. It's longer, more complicated, slower, and more resource intensive, but it works for any number of servers and any group of servers.[/*][/list]

    Make sense? If the logic makes sense, I'll be glad to help with the syntax...

    by ArtB0514 at 2013-05-01 10:40:11

    An important thing to remember about Export-Csv is that it takes the column headings from the very first element of the array that you are exporting. It WILL NOT include any column headings from array elements that are not present in that first element. If you have a different number of properties in your array elements, you MUST use Select-Object to include all of them. For example, if you have an array $Array with these members:
    $Array[0] has Name,CPU,CPUCount,CoreCount,Memory,FreeMem
    and
    $Array[1] has Name,CPU,CPUCount,CoreCount,Memory,FreeMem,Disks
    Then, if you do $Array | Export-CSV, then only Name,CPU,CPUCount,CoreCount,Memory,FreeMem will be included, and the Disks column will not.

    So, as Charles says, you should include the headings for all possible disks in your object and just add the values where they belong in the foreach clause for the disks.

    by syncr0s at 2013-05-01 16:11:13

    Hi Charles and Art,

    Both of you are absolutely correct and it is going to be ugly. I'm happy to do a C_Size, C_Free, etc columns, and do for all drives C-W, then trim down the columns once I have found out what drives are actually in use (Lord forbid we use standard drive letters for applications and file server functions).

    I guess my problem is that DeviceID is returned as C: so I will need to match the 'first letter of Device_ID' with 'first letter of Device_ID'_Size and 'first letter of Device_ID'_Free in writing the data for the select columns.

    I have used a bit of split, trim and substring, but I think in this instance I would be best served by .substring(1), so I put in something like the below into my existing code

    $currdisk = $disk.DeviceID.substring(1)
    $PSUMM.$currdisk_Free = $disk.Freespace /1GB
    $PSumm.$currdisk_Space = $disk.Size /1GB

    Does that sound a logical application of the situation?

    Geoff

    by syncr0s at 2013-05-01 22:54:34

    Ok, tried out my thoughts and $disks = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"
    ForEach ($disk in $disks){
    $currdisk = $disk.DeviceID.trim(":")
    $PSUMM.$currdisk_Free = $disk.Freespace/1024/1024
    $PSumm.$currdisk_Space = $disk.Size/1024/1024
    }
    Gives me the letter itself, but the columns are not being populated, and I think this is because they are strings surrounded by "".

    Any thoughts?

    Geoff

    edit: For the carers

    $dfree = "_Free"
    $dsize = "_Size"
    $disks = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"
    ForEach ($disk in $disks){
    $currdisk = $disk.DeviceID.trim(":")
    $currfree = [string]::join('',($currdisk,$dfree))
    $currsize = [string]::join('',($currdisk,$dsize))
    $PSUMM.$currfree = $disk.Freespace/1024/1024
    $PSumm.$currsize = $disk.Size/1024/1024
    }

    Think I'll place and [int] in there, but I've got the values coming out

    by MasterOfTheHat at 2013-05-02 09:13:48

    First off, you just taught me something! I had no idea you could get away with referencing a property on an object like this!
    $PSumm.$currsize
    Very handy!

    So, the biggest problem I see with your script is the first line:
    $erroractionpreference = “SilentlyContinue”
    That is generally a very bad practice... If you comment that line out, you'll see what your real problem is:
    Exception setting "C_Free": "Property 'C_Free' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:30 char:5
    + $PSUMM.$currfree = $disk.Freespace/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Exception setting "C_Size": "Property 'C_Size' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:31 char:5
    + $PSumm.$currsize = $disk.Size/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Exception setting "D_Free": "Property 'D_Free' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:30 char:5
    + $PSUMM.$currfree = $disk.Freespace/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Exception setting "D_Size": "Property 'D_Size' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:31 char:5
    + $PSumm.$currsize = $disk.Size/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Exception setting "F_Free": "Property 'F_Free' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:30 char:5
    + $PSUMM.$currfree = $disk.Freespace/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Exception setting "F_Size": "Property 'F_Size' cannot be found on this object; make sure it exists and is settable."
    At F:\Storage\Scripts\Windows\stuff.ps1:31 char:5
    + $PSumm.$currsize = $disk.Size/1024/1024
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    You're trying to assign values to properties that you haven't created yet! 🙂

    This is what I was going for... It's definitely incomplete, but I hope it gives you a foundation:
    # simple input array for this example
    $input = @("localhost")

    # create an array to hold all computer information
    $computers = @()
    # create an array to hold the drive letters you find
    $allDisks = @()

    # loop through each computer in input
    foreach ($machine in $input) {
    $cs = Get-WMIObject -ComputerName $machine -Class Win32_ComputerSystem
    # create a custom object to represent each computer and pull computer name
    $PSumm = New-Object -TypeName PSObject -Property @{
    SystemName=$cs.Name
    # ----- you would add your other properties from the Win32_ComputerSystem class here -----
    # create empty hash table for all disks on this computer
    Disks=@{}
    }
    # pull all disk information for the current computer
    $disks = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -ComputerName $machine
    ForEach ($disk in $disks){
    # create custom object to represent each disk information
    $diskInfo = New-Object -TypeName PSObject -Property @{
    FreeSpace=$disk.Freespace/1024/1024
    Size=$disk.Size/1024/1024
    }
    $currdisk = $disk.DeviceID.trim(":")
    # add current disk to hash table in the computer custom object
    $PSumm.Disks.Add($currdisk,$diskInfo)

    # if the disk letter isn't already in the list of drive letters you've found, add it
    if(-not ($allDisks -contains $currdisk))
    {
    $allDisks += $currdisk
    }
    }

    # add this computer to the array of computers
    $computers += $PSumm
    }

    # create a hash table that represents the list of columns in your CSV
    $columns = @{
    Name=""
    CPUProcessors=""
    CPULogicalProcessors=""
    # ----- add all of your non-disk-related columns here ----
    }
    # create 3 columns for each of the drive letters you found across all the computers
    foreach($row in $allDisks) {
    $columns.Add("$($row)_DiskLetter", "")
    $columns.Add("$($row)_DiskSize", "")
    $columns.Add("$($row)_DiskFreeSpace", "")
    }

    # create an array that will be your final output
    $report = @()
    # loop through all of the computers you got data from
    foreach ($pc in $computers)
    {
    # create a new row with all of the columns you created above
    $reportRow = New-Object -TypeName PSObject -Property $columns
    # populate all of the non-disk columns
    $reportRow.Name = $pc.SystemName
    # ----- populate all of the other non-disk-related columns you created -----
    # loop through all of the disks for the current computer
    foreach($disk in $pc.Disks.GetEnumerator())
    {
    # since you added all of your drive letters to the $allDisks array and then created the columns array based on the $allDisks array,
    # all of the columns SHOULD exist...
    $diskValues = $disk.Value
    $reportRow."$($disk.Name)_DiskLetter" = $disk.Name
    $reportRow."$($disk.Name)_DiskSize" = $diskValues.Size
    $reportRow."$($disk.Name)_DiskFreeSpace" = $diskValues.FreeSpace
    }
    $report += $reportRow
    }

    #"`n===== computers: ====="
    #$computers
    #"`n===== alldisks: ====="
    #$allDisks
    #"`n===== columns: ====="
    #$columns.Keys | Sort
    "`n===== report: ====="
    $report

    by syncr0s at 2013-05-02 15:27:14

    [quote="MasterOfTheHat"]First off, you just taught me something! I had no idea you could get away with referencing a property on an object like this!
    $PSumm.$currsize
    Very handy!

    So, the biggest problem I see with your script is the first line:
    $erroractionpreference = “SilentlyContinue”
    That is generally a very bad practice... If you comment that line out, you'll see what your real problem is:
    [/quote]

    Hi Charles,

    Glad I was able to assist. My script actually works, but I didn't post the whole thing. It definitely isn't as eloquent as your one and I'll will give it a go to see if I can get everything in, but what it actually looks like is

    $erroractionpreference = “SilentlyContinue”
    $Date = get-date
    $Datefile = ( get-date ).ToString(‘yyyy-MM-dd-hhmmss’)
    $PSumm = "" | Select SystemName,CPUProcessors,CPULogicalProcessors,CoresperProcessor,NICS,MemoryPhysical,MemoryFreePhysical,MemoryVirtual,MemoryFreeVirtual,C_Free,C_Size,D_Free,D_Size,E_Free,E_Size,F_Free,F_Size,G_Free,G_Size,H_Free,H_Size,I_Free,I_Size,J_Free,J_Size,K_Free,K_Size,J_Free,J_Size,L_Free,L_Size,M_Free,M_Size,N_Free,N_Size,O_Free,O_Size,P_Free,P_Size,Q_Free,Q_Size,R_Free,R_Size,S_Free,S_Size,T_Free,T_Size,U_Free,U_Size,V_Free,V_Size,V_Free,V_Size,W_Free,W_Size

    $os = Get-WMIObject Win32_OperatingSystem
    $proc = Get-WMIObject Win32_Processor
    $cs = Get-WMIObject Win32_ComputerSystem

    $PSumm.SystemName = $cs.Name
    $PSumm.CPUProcessors = $cs.NumberofProcessors
    $PSumm.CPULogicalProcessors = $cs.NumberoflogicalProcessors
    $PSumm.CoresperProcessor = $cs.NumberoflogicalProcessors/$cs.NumberofProcessors
    $PSumm.MemoryPhysical = [int]($os.TotalVisibleMemorySize/1024/1024)
    $PSumm.MemoryFreePhysical = [int]($os.FreePhysicalMemory/1024/1024)
    $PSumm.MemoryVirtual = [int]($os.TotalVirtualMemorySize/1024/1024)
    $PSumm.MemoryFreeVirtual = [int]($os.FreeVirtualMemory/1024/1024)

    $nics = Get-WmiObject -query "Select Name From Win32_NetworkAdapter Where AdapterTypeID = 0 AND Manufacturer != 'Microsoft' AND NOT PNPDeviceID LIKE 'ROOT\\%'"
    $PSumm.NICS = @($nics).count

    $dfree = "_Free"
    $dsize = "_Size"
    $disks = Get-WMIObject -query "Select DeviceID,FreeSpace,Size from Win32_LogicalDisk where DriveType = 3 and Size != '0'"
    ForEach ($disk in $disks){
    $currdisk = $disk.DeviceID.trim(":")
    $currfree = [string]::join('',($currdisk,$dfree))
    $currsize = [string]::join('',($currdisk,$dsize))
    $PSUMM.$currfree = [int]($disk.Freespace/1024/1024/1024)
    $PSumm.$currsize = [int]($disk.Size/1024/1024/1024)
    }

    $PSumm | Export-Csv .\Physical_Scan_results_$Datefile.csv -NoTypeInformation

    This is for the single host, and I'll sort out the loop for the list of hosts in the text document later.

    At a cursory glance I guess the main difference is I am predefining the array drive letters and eliminate the third column for the drive letter.

    Many ways to skin a cat?

    I'll remove the silently continue variable too as per your suggestion.

    Geoff

You must be logged in to reply to this topic.