locked
PowerShell: Combine output from multiple cmdlets in a CSV file RRS feed

  • Question

  • I have a working PowerShell script that exports the full list of public folders with selected headings to a CSV file:

    -------

    Get-PublicFolder -Identity "\" -Recurse -ResultSize $OutputLimit | Select-Object Name, ParentPath, Identity, HasSubFolders, MailEnabled, HiddenFromAddressListsEnabled, HasRules | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    
    

    -------

    I would like to modify it to also include output from Get-PublicFolderStatistics.  Specifically I would like to add ItemCount, TotalItemSize, LastAccessTime, and LastModifyTime for each public folder.  How do I grab output from both Get-PublicFolder and Get-PublicFolderStatistics and output to a single CSV file?


    Tuesday, September 27, 2011 2:30 PM

Answers

  • Here's mine:

    $props = {
        @{
            Name = $pf.name
            ParentPath = $pf.parentpath
            Identity = $pf.identity
            ItemCount = $stats.itemcount
            MB = $stats.totalitemsize.value.tomb()
            LastAccess = $stats.lastaccesstime
            LastModified = $stats.lastmodificationtime
            HasSubFolders = $pf.hassubfolders
            MailEnabled = $pf.mailenabled
            HiddenFromAB = $pf.hiddenfromaddresslistsenabled
            HasRules = $pf.hasrules
            }
        }
       
     $proplist = $props.tostring().split("`n") -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$",'$1'  
     $col = @()
     
    foreach ($stats in get-publicfolderstatistics -resultsize $outputlimit) {
        $pf = get-publicfolder $stats.identity
        $col += new-object psobject -property (&$props) | select $proplist
        }
     $col | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    • Marked as answer by Scott W. Sander Wednesday, September 28, 2011 8:31 PM
    Tuesday, September 27, 2011 11:05 PM

All replies

  • Hi,

    You can collect the data from the objects output from both cmdlets and then output a custom object containing the properties you want.

    For example (untested):

    $props1 = get-publicfolder ... | select-object ...
    $props2 = get-publicfolderstatistics | select-object ...
    "" | select-object @{N="Name"; E={$props1.Name}},...,
    @{N="LastModifyTime"; E={$props2.LastModifyTime}} | export-csv ...

    HTH,

    Bill

    Tuesday, September 27, 2011 2:54 PM
  • I remembered doing it an easier way in the past and I was able to find the script for which I used it.  I have shown the example script below:

    -------

    get-mailbox -server <server> -ResultSize unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | ft DisplayName,IssueWarningQuota,ProhibitSendQuota,@{label="TotalItemSize(MB)";expression={(get-mailboxstatistics $_).TotalItemSize.Value.ToMB()}}
    

    -------

    So I tried to adapt that to my script:

    -------

    Get-PublicFolder -Identity "\" -Recurse -ResultSize $OutputLimit | Select-Object Name, ParentPath, Identity, @{label="ItemCount";expression={(Get-PublicFolderStatistics $_).ItemCount}}, @{label="TotalItemSize";expression={(Get-PublicFolderStatistics $_).TotalItemSize}}, @{label="LastAccessTime";expression={(Get-PublicFolderStatistics $_).LastAccessTime}}, @{label="LastModifiedTime";expression={(Get-PublicFolderStatistics $_).LastModifiedTime}}, HasSubFolders, MailEnabled, HiddenFromAddressListsEnabled, HasRules | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    

    -------

    The script runs, but the new columns are all blank.  Why isn't this working when it works for the first script?

    Tuesday, September 27, 2011 5:41 PM
  • Sorry, I don't have Exchange, so I can't guess what's happening in your specific scenario. The general suggestion I posted before should work, though.

    Bill

    Tuesday, September 27, 2011 6:00 PM
  • I remembered doing it an easier way in the past and I was able to find the script for which I used it.  I have shown the example script below:

    -------

    get-mailbox -server <server> -ResultSize unlimited | Where {$_.UseDatabaseQuotaDefaults -eq $false} | ft DisplayName,IssueWarningQuota,ProhibitSendQuota,@{label="TotalItemSize(MB)";expression={(get-mailboxstatistics $_).TotalItemSize.Value.ToMB()}}
    
    

    -------

    So I tried to adapt that to my script:

    -------

    Get-PublicFolder -Identity "\" -Recurse -ResultSize $OutputLimit | Select-Object Name, ParentPath, Identity, @{label="ItemCount";expression={(Get-PublicFolderStatistics $_).ItemCount}}, @{label="TotalItemSize";expression={(Get-PublicFolderStatistics $_).TotalItemSize}}, @{label="LastAccessTime";expression={(Get-PublicFolderStatistics $_).LastAccessTime}}, @{label="LastModifiedTime";expression={(Get-PublicFolderStatistics $_).LastModifiedTime}}, HasSubFolders, MailEnabled, HiddenFromAddressListsEnabled, HasRules | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    
    

    -------

    The script runs, but the new columns are all blank.  Why isn't this working when it works for the first script?

    Try it like this.  If it still returns empty columsn then we cn simplify.

     

    Get-PublicFolder -Identity "\" -Recurse | 
            Select-Object Name,
                        ParentPath,  
                        Identity, 
                      @{N='ItemCount'                ;E={(Get-PublicFolderStatistics $_).ItemCount}}, 
                      @{N='TotalItemSize'           ;E={(Get-PublicFolderStatistics $_).TotalItemSize}}, 
                      @{N='LastAccessTime'       ;E={(Get-PublicFolderStatistics $_).LastAccessTime}}, 
                      @{N='LastModifiedTime'   ;E={(Get-PublicFolderStatistics $_).LastModifiedTime}}, 
                      HasSubFolders, 
                      MailEnabled, 
                     HiddenFromAddressListsEnabled, 
                     HasRules | 
               Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    
    

     


    jv
    Tuesday, September 27, 2011 8:29 PM
  • Aren't you making it run get-publicfolderstatistics 4x on each folder if you do it that way?


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Tuesday, September 27, 2011 9:09 PM
  • Aren't you making it run get-publicfolderstatistics 4x on each folder if you do it that way?


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "


    Yes -  I was going after that aafter we see if the output is available.

    Feel free to post you code.  I just wanted to get teh code to be a little more user friendly to see if there were more mistakes. See any?

     


    jv
    Tuesday, September 27, 2011 10:07 PM
  • Here's mine:

    $props = {
        @{
            Name = $pf.name
            ParentPath = $pf.parentpath
            Identity = $pf.identity
            ItemCount = $stats.itemcount
            MB = $stats.totalitemsize.value.tomb()
            LastAccess = $stats.lastaccesstime
            LastModified = $stats.lastmodificationtime
            HasSubFolders = $pf.hassubfolders
            MailEnabled = $pf.mailenabled
            HiddenFromAB = $pf.hiddenfromaddresslistsenabled
            HasRules = $pf.hasrules
            }
        }
       
     $proplist = $props.tostring().split("`n") -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$",'$1'  
     $col = @()
     
    foreach ($stats in get-publicfolderstatistics -resultsize $outputlimit) {
        $pf = get-publicfolder $stats.identity
        $col += new-object psobject -property (&$props) | select $proplist
        }
     $col | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    • Marked as answer by Scott W. Sander Wednesday, September 28, 2011 8:31 PM
    Tuesday, September 27, 2011 11:05 PM
  • Very tricky.  I like it.

    It is a good way to compact the working code down to a clear statement of process.  Good redundant use of the objects. The metadata from one drives the other bits.

     


    jv
    Tuesday, September 27, 2011 11:56 PM
  • I had to re-arrange the original logic a bit because get-publicfolderstatistics automatically recurses, and there doesn't appear to be any way to stop it.
    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, September 28, 2011 12:11 AM
  • It seems folder statistixs in all things are the summary from top down. Each level summarizes all lower levels. I don't know how to deal with that very easily.


    jv
    Wednesday, September 28, 2011 12:34 AM
  • Here's mine:

    $props = {
        @{
            Name = $pf.name
            ParentPath = $pf.parentpath
            Identity = $pf.identity
            ItemCount = $stats.itemcount
            MB = $stats.totalitemsize.value.tomb()
            LastAccess = $stats.lastaccesstime
            LastModified = $stats.lastmodificationtime
            HasSubFolders = $pf.hassubfolders
            MailEnabled = $pf.mailenabled
            HiddenFromAB = $pf.hiddenfromaddresslistsenabled
            HasRules = $pf.hasrules
            }
        }
       
     $proplist = $props.tostring().split("`n") -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$",'$1'  
     $col = @()
     
    foreach ($stats in get-publicfolderstatistics -resultsize $outputlimit) {
        $pf = get-publicfolder $stats.identity
        $col += new-object psobject -property (&$props) | select $proplist
        }
     $col | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "


    I ended up writing a script that worked:

    ------

    # SET VARIABLES
    $ExportFileName = "public_folders.csv"
    $ExportPath = "H:\Output"
    $OutputLimit = 500
    
    # RUN
    Get-PublicFolderStatistics -ResultSize $OutputLimit | Select-Object Name, @{label="ParentPath";expression={(Get-PublicFolder -Identity $_).ParentPath}}, @{label="Identity";expression={(Get-PublicFolder -Identity $_).Identity}}, ItemCount, TotalItemSize, LastAccessTime, LastModificationTime, @{label="HasSubFolders";expression={(Get-PublicFolder -Identity $_).HasSubFolders}}, @{label="MailEnabled";expression={(Get-PublicFolder -Identity $_).MailEnabled}}, @{label="HiddenFromAddressListsEnabled";expression={(Get-PublicFolder -Identity $_).HiddenFromAddressListsEnabled}}, @{label="HasRules";expression={(Get-PublicFolder -Identity $_).HasRules}} | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    

    ------

    It wasn't very efficient.  It took a couple of hours to run.  I'll try yours.  I do have a question about this part:

    $proplist = $props.tostring().split("`n") -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$",'$1'

    What does that do?

    Wednesday, September 28, 2011 2:01 PM
  • It wasn't very efficient.  It took a couple of hours to run.  I'll try yours.  I do have a question about this part:

    $proplist = $props.tostring().split("`n") -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$",'$1'

    What does that do?

    It parses the property names out of the scriptblock that contains the hash table. 

    Hash tables don't enumerate in a predictable order, so when you create an object using the -property parameter and a hash table, the properties of the object, and as a result, the order of the columns in the resulting exported csv file can end up a random order. 

    You can specify the order you want them to appear in by piping the object through select-object and listing all the properties in the correct order.  The $proplist expression parses the property names out of the hash table script block into an array and then that array is used as the argument list for the select-object.  This forces the properties to appear in the same order they are defined in the hash table without having to re-enter them in another list.  If you want them in a different order, just re-arrange the hash table and they'll automatically get re-ordered in the output objects and in the .csv file.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, September 28, 2011 2:44 PM
  • It parses the property names out of the scriptblock that contains the hash table. 

    Hash tables don't enumerate in a predictable order, so when you create an object using the -property parameter and a hash table, the properties of the object, and as a result, the order of the columns in the resulting exported csv file can end up a random order. 

    You can specify the order you want them to appear in by piping the object through select-object and listing all the properties in the correct order.  The $proplist expression parses the property names out of the hash table script block into an array and then that array is used as the argument list for the select-object.  This forces the properties to appear in the same order they are defined in the hash table without having to re-enter them in another list.  If you want them in a different order, just re-arrange the hash table and they'll automatically get re-ordered in the output objects and in the .csv file.


    I get it now.  Basically $PropList is just a hash table of labels.  The regular expression isolates the label portion.  Ok, makes sense.

    Have another question.  You refer to a Property (&$Props).  What does the ampersand do in front of $Props?  I am not familiar with that syntax.

    Also, I tried to run your script and received the following error:

    -------

    Unexpected token 'in' in expression or statement.
    At D:\Scripts\get_public_folders.ps1:41 char:26
    + ForEach-Object ($Stats in <<<<  Get-PublicFolderStatistics -ResultSize $OutputLimit) {
        + CategoryInfo          : ParserError: (in:String) [], ParseException
        + FullyQualifiedErrorId : UnexpectedToken
    

    -------

    As you can see, I modified your script slightly to suit my preferences (capitalization and no aliases).  As such, I'll go ahead an post the modified script below so it can be verified there are no syntax errors created by my modifications.

    -------

    $ExportFileName = "public_folders.csv"
    $ExportPath = "H:\Output"
    $OutputLimit = 5000
    $Props = {
        @{
            Name = $PF.Name
            ParentPath = $PF.ParentPath
            Identity = $PF.Identity
            ItemCount = $stats.ItemCount
            Size = $stats.TotalItemSize.Value.ToMB() 
            LastAccess = $stats.LastUserAccessTime
            LastModified = $stats.LastUserModificationTime
            HasSubFolders = $PF.HasSubFolders
            MailEnabled = $PF.MailEnabled
            HiddenFromAB = $PF.HiddenFromAddressListsEnabled
            HasRules = $PF.HasRules
            }
    }
    $PropList = $Props.ToString().Split("`n") -Match "^\s*(\S+)\s*=.+$" -Replace "^\s*(\S+)\s*=.+$",'$1'   
    $Col = @()
    ForEach-Object ($Stats in Get-PublicFolderStatistics -ResultSize $OutputLimit)
    {
        $PF = Get-Publicfolder $Stats.Identity
        $Col += New-Object PSObject -Property (&$Props) | Select-Object $PropList
    }
    $Col | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    

     




    Wednesday, September 28, 2011 4:22 PM
  • It appears you're getting the error because you changed a foreach to foreach-object.  These are not the same thing.

    The & is the invoke operator.  If you look at the hash table definition, you'll notice that it's enclosed in curly braces and assigned to a variable ($props).  This makes it a scripblock.  The scriptblock is kind of like a function - it defines a block of code, but does not execute the code. 

    Try this from a ps console as a demonstration:

    $test = {gci}

    That defined a scriptblock that has in it the gci cmdlet.  When you enter that, nothing apparent happens.

    Now do this:

    &$test

    That invokes (runs) the script block assigned to the $test variable.

    If you look at the script, you'll notice that it's using variables in the hash table that haven't been defined yet (you haven't done the gets on the public folders or statistics).  With the hash table in the scripblock, that doesn't matter - the hash table isn't created using those variables until the script block that it's in is invoked, after we've done the gets, and are ready to create the output object.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, September 28, 2011 4:45 PM
  • It appears you're getting the error because you changed a foreach to foreach-object.  These are not the same thing.


    Gotcha. PowerGUI gives help for ForEach-Object if you look at ForEach.   It worked after I changed it back.

    The & is the invoke operator.  If you look at the hash table definition, you'll notice that it's enclosed in curly braces and assigned to a variable ($props).  This makes it a scripblock.  The scriptblock is kind of like a function - it defines a block of code, but does not execute the code. 

    Try this from a ps console as a demonstration:

    $test = {gci}

    That defined a scriptblock that has in it the gci cmdlet.  When you enter that, nothing apparent happens.

    Now do this:

    &$test

    That invokes (runs) the script block assigned to the $test variable.

    If you look at the script, you'll notice that it's using variables in the hash table that haven't been defined yet (you haven't done the gets on the public folders or statistics).  With the hash table in the scripblock, that doesn't matter - the hash table isn't created using those variables until the script block that it's in is invoked, after we've done the gets, and are ready to create the output object.


    How would I go about scaling up the script to use properties from Get-PublicFolderItemStatistics and Get-PublicFolderClientPermission?

    I tried the script below, but received errors (follows the script):

    -------

    $Props = {
    
        @{
    
            Name = $PF.Name
    
            ParentPath = $PF.ParentPath
    
            Identity = $PF.Identity
    
            ItemCount = $Stats.ItemCount
    
            Size = $Stats.TotalItemSize.Value.ToMB() 
    
            LastAccess = $Stats.LastUserAccessTime
    
            LastModified = $Stats.LastUserModificationTime
    
    		OldestItem = $ItemStats.CreationTime[0].ToShortDateString()
    
    		NewestItem = $ItemStats.CreationTime[{$Stats.ItemCount} - 1].ToShortDateString()
    
            HasSubFolders = $PF.HasSubFolders
    
            MailEnabled = $PF.MailEnabled
    
            HiddenFromAB = $PF.HiddenFromAddressListsEnabled
    
            HasRules = $PF.HasRules
    
         }
    
    }
    
        
    
    $PropList = $Props.ToString().Split("`n") -Match "^\s*(\S+)\s*=.+$" -Replace "^\s*(\S+)\s*=.+$",'$1'   
    
    $Col = @()
    
      
    
    ForEach ($Stats in Get-PublicFolderStatistics -ResultSize $OutputLimit)
    
    {
    
        $PF = Get-PublicFolder $Stats.Identity
    
    	$ItemStats = Get-PublicFolderItemStatistics $Stats.Identity
    
        $Col += New-Object PSObject -Property (&$Props) | Select-Object $PropList
    
    }
    
    $Col | Export-Csv -NoTypeInformation -Path $ExportPath\$ExportFileName
    
    
    
    

    -------

    Cannot index into a null array.
    
    At D:\Scripts\get_public_folders.ps1:31 char:40
    
    +         OldestItem = $ItemStats.CreationTime[ <<<< 0].ToShortDateString()
    
        + CategoryInfo          : InvalidOperation: (0:Int32) [], RuntimeException
    
        + FullyQualifiedErrorId : NullArray
    
    
    
    New-Object : Cannot validate argument on parameter 'Property'. The argument is null or empty. Supply an argument that i
    
    s not null or empty and then try the command again.
    
    At D:\Scripts\get_public_folders.ps1:47 char:42
    
    +     $Col += New-Object PSObject -Property <<<<  (&$Props) | Select-Object $PropList
    
        + CategoryInfo          : InvalidData: (:) [New-Object], ParameterBindingValidationException
    
        + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.NewObjectCommand

     


    Wednesday, September 28, 2011 6:00 PM
  • How would I go about scaling up the script to use properties from Get-PublicFolderItemStatistics and Get-PublicFolderClientPermission?

    I tried the script below, but received errors (follows the script):

    If you really want to pursue that, I'd open a new question.  This thread is getting pretty long already, and hopefully we're getting at least close to having your original questions answered.

    Adding another level of detail at the item level is going to involve iterating through every item of every folder, and creating an output object for each one.  The run time would probably prohibitive and the and same public folder information would be repeated over and over again in the spreadsheet for every item in the folder.   

    Adding the perimssions creates a different kind of problem, since that's a multi-valued property with an arbitrary number of values.  It just isn't going to fit into a .csv format very well. 

    Edit: - I went back and looked at the code you're wanting to add for the publicfolderitemstatistics, and I think that's do-able, but I'd still ask that you start another question, just because of the length of this thread.
    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    • Edited by mjolinor Wednesday, September 28, 2011 6:53 PM
    Wednesday, September 28, 2011 6:42 PM
  • Ok, that's fair.  Thank you.  My original question is indeed answered.

    I actually have working code that generates a much more detailed public folder report, but it takes a very long time to run (about four hours).  I adapted it from some other code I found and I was hoping to use your principles to convert what I have to something that runs with greater efficiency, or to build from what your suggestion was here with the same result.  The things I like about the other script is that in addition to the stuff that the script discussed in this thread does...

    • It shows the date the oldest and the newest items in the folder were created.  This is a useful part of determining if the folder is abandoned.
    • If the folder contains nothing, it says "No content".  If it is empty but contains subfolders, it just says "Subfolders".  More useful than just saying it contains zero items.
    • It displays the users that have the various major permissions categories for each folder (Owner, Contributor, Reviewer, Editor, etc.).  The Owner category is useful for me to know who I have to talk to about it and the others are just gravy.
    • It displays all of the e-mail addresses associated with the public folder, if it is mail-enabled.

    As far as how it handles, for example, the list of users that have Owner permission on a giving public folder, it lists them in a single "field" separated by semi-colon.  Same with the e-mail addresses.

    I will post a new question as soon as I have done a little more tweaking on both scripts.

    Again, thank you.  I wish I had the same level of PowerShell wizardry that you do.

    Wednesday, September 28, 2011 8:52 PM
  • Hi scott,

    I need

    I am trying to run the following command but its giving an error.

    Get-PublicFolder -identity "\" -Recurse -Server " Server name"  | Get-Publicfolderstatistics | Select-Object Name,Folderpath,TotalItemSize,Itemcount | Export-Csv "c:\pf.csv"

    Basically we need to find information on all the public folders (and subfolders) like the total items, folderpath, total size of the public folders etc. We need to get this information to understand what is the exact size of the PF data we have on all the PFs as it has become very difficult for us to manage the pf DB.

     

    Any help from you guys will be appreciated.

    Thanks.

    Friday, October 28, 2011 11:34 AM
  • Hi scott,

    I need

    I am trying to run the following command but its giving an error.

    Get-PublicFolder -identity "\" -Recurse -Server " Server name"  | Get-Publicfolderstatistics | Select-Object Name,Folderpath,TotalItemSize,Itemcount | Export-Csv "c:\pf.csv"

    Basically we need to find information on all the public folders (and subfolders) like the total items, folderpath, total size of the public folders etc. We need to get this information to understand what is the exact size of the PF data we have on all the PFs as it has become very difficult for us to manage the pf DB.

     

    Any help from you guys will be appreciated.

    Thanks.

    Do you want a total of these values or a values for each of the folders separately?
    Saturday, October 29, 2011 1:12 PM
  • Work fine for me!
    Thursday, March 23, 2017 8:52 PM