File servers configuration report

Sup’ PSHomies,

I recently blogged about how awesome PScribo is, couldn’t forget my other favorite go to module when it comes to reporting: ImportExcel brought to us by Doug Finke!

I’ve blogged about my love for csv files and what the possibilities are when it comes to exporting and what to look out for.

My reason for exporting to csv is mostly to create Excel reports. Well if that’s your case you might as well just cut out the middle-man and go straight to the source!

Excel is a great way to analyze your data. I know my way around Excel so that helps.

The title of this blog is about file servers configuration. Think volumes, shares etc etc. The idea is to gather all File server related data (Now where have you seen that before 😛 ) and create a xlsx file report.

I don’t know about you but it seems like the community is producing more and more high quality blogs on just about every subject you can think of! I can barely keep up!

I enjoyed Jaap Brasser’s take on storing your credentials. Thanks for sharing Jaap! 😉

I’m using a scriptblock in combination with invoke-command to gather the needed information . The scriptblock returns a custom object.

$sbStorage = {
    [PSCustomObject]@{
        Volume = $(Get-Volume | Select-Object *)
        SMBShare = $(Get-SmbShare | Select-Object *)
        SMBShareAccess = $(Get-SmbShare | Get-SmbShareAccess)
        SMBShareNTFS = $(Get-SmbShare |
            Where-Object{ $_.Name -ne 'IPC$'} |
            Get-Acl |
                Select-Object @{Name='Path';Expression={($_.Path).Replace('Microsoft.PowerShell.Core\FileSystem::','')}},
                Owner,Access
        )
        Disk = $(Get-Disk | Select-Object *)
        VSSShadows = $(vssadmin.exe list Shadows)
        VSSWriters = $(vssadmin.exe list Writers)
    }
}

#region Main
$snapshotStorage = $Servers |
ForEach-Object{
  Invoke-Command  -ComputerName $_.ComputerName -ScriptBlock $sbStorage -Credential $cred
}
#endregion

I’m using admin credentials in my test lab so this will also work without -Credential. This was sufficient in my case.

Here’s a quick rundown on what the script basically does. Let’s take a look at documenting Shares to get the general idea…

#region Get Shares
$snapshotStorage |
ForEach-Object{
    $ComputerName = $_.PSComputerName

    $_.SMBShare |
    ForEach-Object{
        [PSCustomObject]@{
            ComputerName = $ComputerName
            Name = $_.Name
            Path = $_.Path
            Description = $_.Description
            ShareState = $_.ShareState
        }
    }
}|
Export-Csv .\export\storage\FS_SMBShares-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation

#Export to xlsx file
Import-Csv .\export\storage\FS_SMBShares-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname SMBShares -AutoSize -BoldTopRow -FreezeTopRow
#endregion

For each server we’ve captured share information to $_.SMBShares. Simply select the properties you want and save to a custom object. Saving to csv and then exporting to Excel makes sure that everything is a string . This is one of those times that having your object cast as string comes in handy ;-). Also, most of us don’t have Excel on every server. Having the csv files let’s you quickly assess what you can expect in your Excel report.

Export-Excel has quite an extensive parameterset

Export-Excel syntax

No need to open the xlsx file, autosize and freeze to the toprow, just add the following switches -AutoSize -BoldTopRow -FreezeTopRow and your good to go!

<#
Author: I. Strachan
Version:
Version History:
Purpose: Get File Servers storage configuration
#>
#region FileServers to process
$Servers = @"
ComputerName
DC-DSC-01
MEM-DSC-01
"@ | ConvertFrom-Csv -Delimiter "`t"
#endregion
#region import saved credential and initialize variables
#Jaap Brassers blog on saving credentials.
#http://www.jaapbrasser.com/quickly-and-securely-storing-your-credentials-powershell/
$Hash = Import-CliXml -Path "${env:\userprofile}\Hash.Cred"
$cred = $Hash.'pshirwin-admin'
$exportDate = Get-Date -Format ddMMyyyy
$xlsxFile = ".\export\storage\PSHIRWIN - StorageReport - $($exportDate).xlsx"
$null = Get-SmbShare | Get-SmbShareAccess
#endregion
#region Helper functions and scriptblock
filter Get-CapacitySize {
'{0:N2} {1}' -f $(
if ($_ -lt 1kb) { $_, 'Bytes' }
elseif ($_ -lt 1mb) { ($_/1kb), 'KB' }
elseif ($_ -lt 1gb) { ($_/1mb), 'MB' }
elseif ($_ -lt 1tb) { ($_/1gb), 'GB' }
elseif ($_ -lt 1pb) { ($_/1tb), 'TB' }
else { ($_/1pb), 'PB' }
)
}
$sbStorage = {
[PSCustomObject]@{
Volume = $(Get-Volume | Select-Object *)
SMBShare = $(Get-SmbShare | Select-Object *)
SMBShareAccess = $(Get-SmbShare | Get-SmbShareAccess)
SMBShareNTFS = $(Get-SmbShare |
Where-Object{ $_.Name -ne 'IPC$'} |
Get-Acl |
Select-Object @{Name='Path';Expression={($_.Path).Replace('Microsoft.PowerShell.Core\FileSystem::','')}},
Owner,Access
)
Disk = $(Get-Disk | Select-Object *)
VSSShadows = $(vssadmin.exe list Shadows)
VSSWriters = $(vssadmin.exe list Writers)
}
}
#endregion
#region Main
$snapshotStorage = $Servers |
ForEach-Object{
Invoke-Command -ComputerName $_.ComputerName -ScriptBlock $sbStorage -Credential $cred
}
#endregion
#region Export snapshot to XML
$snapshotStorage | Export-Clixml .\export\storage\StorageSnapshot-$($exportDate).xml -Encoding UTF8
#endregion
#region Create CSV reports
#export Server names to xlsxFile
$Servers|
Export-Excel -Path $xlsxFile -WorkSheetname Servers -AutoSize -BoldTopRow
#region Get Volume Names
$snapshotStorage |
ForEach-Object{
$ComputerName = $_.PSComputerName
$_.Volume |
ForEach-Object{
[PSCustomObject]@{
ComputerName = $ComputerName
DriveLetter = $_.Driveletter
FileSystemLabel = $_.FileSystemLabel
DiskSize = $_.Size | Get-CapacitySize
UsedSpace = $_.SizeRemaining | Get-CapacitySize
FreeSpace = ($_.Size - $_.SizeRemaining) | Get-CapacitySize
}
}
} |
Export-Csv .\export\storage\FS_VolumeNames-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_VolumeNames-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname VolumeNames -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#region Get Shares
$snapshotStorage |
ForEach-Object{
$ComputerName = $_.PSComputerName
$_.SMBShare |
ForEach-Object{
[PSCustomObject]@{
ComputerName = $ComputerName
Name = $_.Name
Path = $_.Path
Description = $_.Description
ShareState = $_.ShareState
}
}
}|
Export-Csv .\export\storage\FS_SMBShares-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_SMBShares-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname SMBShares -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#region Get Sharesaccess
$snapshotStorage |
ForEach-Object{
$ComputerName = $_.PSComputerName
$_.SMBShareAccess |
ForEach-Object{
[PSCustomObject]@{
ComputerName = $ComputerName
Name = $_.Name
AccountName = $_.AccountName
AccessControlType = $_.AccessControlType
AccessRight = [Microsoft.PowerShell.Cmdletization.GeneratedTypes.SmbShare.ShareAccessRight]$_.AccessRight
}
}
}|
Export-Csv .\export\storage\FS_SMBSharesAccess-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_SMBSharesAccess-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname SMBSharesAccess -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#region Get Shares NTFS Access
$snapshotStorage |
ForEach-Object{
$ComputerName = $_.PSComputerName
$_.SMBShareNTFS |
ForEach-Object{
$fullPathName = $_.Path
$pathOwner = $_.Owner
$_.Access|
ForEach-Object{
[PSCustomObject]@{
ComputerName = $ComputerName
Path = $fullPathName
Owner = $pathOwner
IdentityReference = $_.IdentityReference
FileSystemRights = $_.FileSystemRights
AccessControlType = $_.AccessControlType
IsInherited = $_.IsInherited
InheritanceFlags = $_.InheritanceFlags
PropagationFlags = $_.PropagationFlags
}
}
}
}|
Export-Csv .\export\storage\FS_SMBSharesNTFS-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_SMBSharesNTFS-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname SMBSharesNTFS -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#region VSS Shadow copies
$shadowCopyProperties = [Ordered]@{
ShadowCopySetID = ''
CreationTime = ''
ShadowCopyID = ''
OriginalVolume = ''
ShadowCopyVolume = ''
Machine = ''
Provider = ''
Type = ''
Attributes = ''
}
$snapshotStorage |
ForEach-Object{
foreach($line in $_.VSSShadows){
switch($line){
{$_ -match 'Contents of shadow copy set ID:'}
{
$shadowCopy = New-Object -TypeName psobject -Property $shadowCopyProperties
$shadowCopy.ShadowCopySetID = $_.Substring(32).Trim()
}
{$_ -match ' Contained 1 shadow copies at creation time:'}
{ $shadowCopy.CreationTime = $_.Substring(46).Trim() }
{$_ -match ' Shadow Copy ID:'}
{ $shadowCopy.ShadowCopyID = $_.Substring(21).Trim() }
{$_ -match ' Original Volume:'}
{ $shadowCopy.OriginalVolume = $_.Substring(26).Trim() }
{$_ -match ' Shadow Copy Volume:'}
{ $shadowCopy.ShadowCopyVolume = $_.Substring(29).Trim() }
{$_ -match ' Originating Machine: '}
{ $shadowCopy.Machine = $_.Substring(30).Trim() }
{$_ -match ' Provider:'}
{ $shadowCopy.Provider = $_.Substring(19).Trim() }
{$_ -match ' Type:'}
{ $shadowCopy.Type = $_.Substring(15).Trim() }
{$_ -match ' Attributes:'}
{
$shadowCopy.Attributes = $_.Substring(21).Trim()
[PSCustomObject]$shadowCopy
}
}
}
}|
Export-Csv .\export\storage\FS_VSSShadowCopies-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_VSSShadowCopies-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname 'VSS Shadow copies' -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#region VSS Writers
$shadowWriterProperties = [Ordered]@{
ComputerName = ''
Name = ''
ID = ''
InstanceID = ''
State = ''
LastError = ''
}
$snapshotStorage |
ForEach-Object{
$ComputerName = $_.PSComputerName
foreach($line in $_.VSSWriters){
switch($line){
{$_ -match 'Writer name:'}
{
$shadowWriter = New-Object -TypeName psobject -Property $shadowWriterProperties
$shadowWriter.ComputerName = $ComputerName
$shadowWriter.Name = $_.Substring(12).Replace("'",'').Trim()
}
{$_ -match ' Writer Id:'}
{ $shadowWriter.ID = $_.Substring(13).Trim() }
{$_ -match ' Writer Instance Id:'}
{ $shadowWriter.InstanceID = $_.Substring(22).Trim() }
{$_ -match ' State: '}
{ $shadowWriter.State = $_.Substring(13).Trim() }
{$_ -match ' Last error:'}
{
$shadowWriter.LastError = $_.Substring(14).Trim()
[PSCustomObject]$shadowWriter
}
}
}
}|
Export-Csv .\export\storage\FS_VSSShadowWriters-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" -NoTypeInformation
#Export to xlsx file
Import-Csv .\export\storage\FS_VSSShadowWriters-$($exportDate).csv -Encoding UTF8 -Delimiter "`t" |
Export-Excel -Path $xlsxFile -WorkSheetname 'VSS Shadow writers' -AutoSize -BoldTopRow -FreezeTopRow
#endregion
#endregion

Here’s a quick impression of the xlsx file

File Server Excel Report

I can’t say enough good things about Import-Excel. Doug Finke actively maintains this module and is open to suggestions to make it better! Thanks for making reporting in Excel so much easier Doug!

Hope it’s worth something to you

Ttyl,

Urv

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s