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
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
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
pshirwin 