Link to home
Start Free TrialLog in
Avatar of michalek19
michalek19Flag for United States of America

asked on

Can you put together all of the scripts as onePS script and export to Powershell GUI and CSV

Hi

Would you please put these scripts together as one and export result to PowerShell GUI and CSV file?

Thx, Michal

group it, and display the count

$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12 | ConvertFrom-CSV
$Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending | Format-Table –AutoSize


$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending | Format-Table -AutoSize
                                         

Installed Date
$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
}
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$Totals | Group Name | Select Name,Count | Format-Table -AutoSize

                                         


how many printers are on the generated spread
Import-Csv FileName.csv | Select-Object *, @{n='PingResponds';e={ Test-Connection $_.'IP Hostname' -Quiet -Count 1 }} | Export-Csv FIleName-Updated.csv -NoTypeInformation
                                         

Device Groups
$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV
$Software | Group 'Device Groups' | Select Name,Count | Format-Table -AutoSize


$InputFile = "C:\Temp\Scripts\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV

$Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name |
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}} | Out-GridView
Devinv.csv
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Eliminated duplicate import-csv's

#Import-Csv FileName.csv | Select-Object *, @{n='PingResponds';e={ Test-Connection $_.'IP Hostname' -Quiet -Count 1 }} | Export-Csv FIleName-Updated.csv -NoTypeInformation
$InputFile = "C:\Test\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12 | ConvertFrom-CSV
$Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending | Format-Table -AutoSize
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
    }
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$Totals | Group Name | Select Name,Count | Format-Table -AutoSize

#Device Groups
$Software | Group 'Device Groups' | Select Name,Count | Format-Table -AutoSize
$output = $Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name |
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}} 
$output | Export-Csv -NoTypeInformation -Path c:\test\output.csv
$output  Out-GridView

Open in new window

Avatar of michalek19

ASKER

Hi

I am getting this error when i run this script
Unexpected token 'Out-GridView' in expression or statement.
At C:\temp\Scripts\combained.ps1:42 char:22
+ $output  Out-GridView <<<<
    + CategoryInfo          : ParserError: (Out-GridView:String) [], ParseExce
   ption
    + FullyQualifiedErrorId : UnexpectedToken
somehow the posting process omitted an "|"
$output | out-gridview
more errors after adding "|"

ConvertFrom-Csv : Cannot process argument because the value of argument "name"
is invalid. Change the value of the "name" argument and run the operation again
.
At C:\temp\Scripts\combained.ps1:3 char:71
+ $Software = Get-Content $InputFile | Select -Skip 12 | ConvertFrom-CSV <<<<
    + CategoryInfo          : InvalidArgument: (:) [ConvertFrom-Csv], PSArgume
   ntException
    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.ConvertFr
   omCsvCommand

Unable to find type [ordered]: make sure that the assembly containing this type
 is loaded.
At C:\temp\Scripts\combained.ps1:5 char:23
+ $arrRanges = [ordered] <<<< @{
    + CategoryInfo          : InvalidOperation: (ordered:String) [], RuntimeEx
   ception
    + FullyQualifiedErrorId : TypeNotFound

You cannot call a method on a null-valued expression.
At C:\temp\Scripts\combained.ps1:15 char:25
+ $arrRanges.GetEnumerator <<<< () | ForEach {
    + CategoryInfo          : InvalidOperation: (GetEnumerator:String) [], Run
   timeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null
.
At C:\temp\Scripts\combained.ps1:41 char:21
+ $output | Export-Csv <<<<  -NoTypeInformation -Path c:\test\output.csv
    + CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingV
   alidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M
   icrosoft.PowerShell.Commands.ExportCsvCommand
did the original scripts you supplied work properly on your machine?
what is your powershell version
get-host  will return the powershell version
the scripts work with powershell 3 +
yes, scripts worked on powershell v.3
Hi, try this version of the scripts combined.

Regards,

Rob.

$InputFile = "C:\Test\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV

# Group and display count of printer models
$output = $Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending #| Format-Table -AutoSize
$output | Out-GridView -Title "Printer Model Count"

# List pages groups
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
    }
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$output = $Totals | Group Name | Select Name,Count #| Format-Table -AutoSize
$output | Out-GridView -Title "Page Count Groups"

#Device Groups by installed date
$Software | Group 'Device Groups' | Select Name,Count | Format-Table -AutoSize
$output = $Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name |
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}} 
#$output | Export-Csv -NoTypeInformation -Path c:\test\output.csv
$output | Out-GridView -Title "Device Groups by Installed Date"

Open in new window

This part doesn't work in the script. Doesn't generate numbers

# List pages groups
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
    }
Hi, I am seeing the page counts based on the sample file you posted.

Can you make sure you have the same column names as the sample file, or change the script to the updated column names?

Regards,

Rob.

$InputFile = "C:\Temp\devinv.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV

# Group and display count of printer models
$output = $Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending #| Format-Table -AutoSize
$output | Out-GridView -Title "Printer Model Count"

# List pages groups
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
    }
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$output = $Totals | Group Name | Select Name,Count #| Format-Table -AutoSize
$output | Out-GridView -Title "Page Count Groups"

#Count of Device Groups
$Software | Group 'Device Groups' | Select Name,Count | Out-GridView -Title "Count of Device Groups" #| Format-Table -AutoSize

#Device Groups by installed date
$output = $Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name |
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}} 
#$output | Export-Csv -NoTypeInformation -Path c:\test\output.csv
$output | Out-GridView -Title "Device Groups by Installed Date"

Open in new window

I am getting this error. Still no output for

 '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..1000

Unable to find type [ordered]: make sure that the assembly containing this type
 is loaded.
At C:\temp\scripts\combained.ps1:11 char:23
+ $arrRanges = [ordered] <<<< @{
    + CategoryInfo          : InvalidOperation: (ordered:String) [], RuntimeEx
   ception
    + FullyQualifiedErrorId : TypeNotFound

You cannot call a method on a null-valued expression.
At C:\temp\scripts\combained.ps1:21 char:25
+ $arrRanges.GetEnumerator <<<< () | ForEach {
    + CategoryInfo          : InvalidOperation: (GetEnumerator:String) [], Run
   timeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
something is wonky with your system. As Rob's and my code work perfectly on our systems.

From the ISE (editor) load the file go to line 20 and press F9 to create a breakpoint
run the code it will break and you will see [DBG]
enter "$arrRanges" you should see something like the following:

[DBG]: PS C:\Windows\system32>> $arrRanges

Name                           Value                                                
----                           -----                                                
0 Pages                        0                                                    
1-50 Pages                     1..50                                                
51-100 Pages                   5..100                                              
101-1000 Pages                 101..1000                                            
1001-5000 Pages                1001..5000                                          
5001-10000 Pages               5001..10000                                          
** Pages                       **                                                  
you can press f11 to step into the code
You must be using PS 2.0 as the [Ordered] type was introduced in PS 3.0.

Either install PS 3.0 or remove the [Ordered] keyword from the code.

Rob.
Go it , let me try
i ran it again using PS 3 and this time works but output.csv wasn't created

#$output | Export-Csv -NoTypeInformation -Path C:\Temp\scripts\output.csv
$output | Out-GridView -Title "Device Groups by Installed Date
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rob is a genius.
Very knowledge engineer with great scripting skills .

Thank you, Mike
what is your powershell version
get-host  will return the powershell version
the scripts work with powershell 3 +


You must be using PS 2.0 as the [Ordered] type was introduced in PS 3.0.
i ran it again using PS 3 and this time works
Hi Rob

I am getting incorrect output when i am running this script. Script generates correct numbers.
The out put should looks like this  but please check attachment.
Please advice

    '0 Pages' = 32
    '1-50 Pages' = 55
    '51-100 Pages' = 49
    '101-1000 Pages' = 995
    '1001-5000 Pages' = 193
    '5001-10000 Pages' = 41
    '10001-50000 Pages'= 9
    '** Pages' = '**'

==========================================================================
$InputFile = "C:\Temp\Total page count for March41.csv"
$Software = Get-Content $InputFile | Select -Skip 12
$Software[0] = "NullCol" + $Software[0]
$Software = $Software | ConvertFrom-CSV

# Group and display count of printer models
$output = $Software | Group-Object "Device Model" | Where {$_.Name.Trim() -ne ''} | Select Name, Count | Sort Count -Descending #| Format-Table -AutoSize
$output | Out-GridView -Title "Printer Model Count"

# List pages groups
$arrRanges = [ordered]@{
    '0 Pages' = '0'
    '1-50 Pages' = '1..50'
    '51-100 Pages' = '5..100'
    '101-1000 Pages' = '101..1000'
    '1001-5000 Pages' = '1001..5000'
    '5001-10000 Pages' = '5001..10000'
    '** Pages' = '**'
    }
$Totals = $null
$arrRanges.GetEnumerator() | ForEach {
    $Name = $_.Name
    If ($_.Value -eq '**') {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -eq '$($_.Value)'} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    } Else {
        $Expression = "`$Totals += `$Software | Where {`$_.Total -ne '**' -and [int]`$_.Total -in $($_.Value)} | Select @{n='Name';e={'$Name'}},@{n='PageCount';e={`$_.Total}}"
    }
    Invoke-Expression $Expression
}
$output = $Totals | Group Name | Select Name,Count #| Format-Table -AutoSize
$output | Out-GridView -Title "Page Count Groups"

#Count of Device Groups
$Software | Group 'Device Groups' | Select Name,Count | Out-GridView -Title "Count of Device Groups" #| Format-Table -AutoSize

#Device Groups by installed date
$output = $Software | Group {
    If ("($_.'Install Date')") {
        $Date = $_.'Install Date'.Split('/')
        If (($Date.Length)-1 -eq 0) {
            $Date
        } else {
            $Date[2].Split(' ')[0]
        }
    }
} | Sort Name |
    Select Count,Name,@{n='Hostname';e={($_.Group.'IP Hostname' | Get-Unique) -join ","}},
    @{n='IP Address';e={($_.Group.'IP Address' | Get-Unique) -join ","}},
    @{n='Device Model';e={($_.Group.'Device Model' | Get-Unique) -join ","}}
#$output | Export-Csv -NoTypeInformation -Path c:\test\output.csv
$output | Out-GridView -Title "Device Groups by Installed Date"
Total-page-count-for-March41.csv
Page-Count-by-pages.png.txt
In your Page count CSV file, you have 1051 lines, but only 643 lines have meaningful data.  Delete everything below line 643 from that file, and try the script again.

Rob.
you correct, that fix the issue