Tag Archives: ImportExcel

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

PScribo for the win!

Sup’ PSHomies,

Is it just me or are there way too many cool development in the community happening right now? You know you’re addicted to PowerShell if your idea of unwinding on a Sunday evening is scripting! 😛

How I discovered PScribo

I was busy creating a server documentation script based on WMI. I’ve used sydi-server in the past, but I wanted it to be PowerShell-based for obvious reasons… 😉

My first idea was to create a well-defined XML document and use CSS to generate the HTML file. HTML had my preference. My first attempt was using the specialized XmlTextWriter object. That was only the first part, I had to do the CSS as well. It was a lot of code. At that time I didn’t make use of snapshots.

My next attempt was to use ConvertTo-HTML -Fragment -As (list,Table). This worked out pretty well for what I wanted, still it’s no PScribo. I’ve probably done three versions of this script.

As luck would have it, while working on the third version, the PSConfEu 2015 was taking place in StockHolm. I didn’t attend that one. Luckily for me, the presentations were uploaded. One presentation immediately caught my eye Documenting xxIT.

This is exactly what I wanted! HTML reporting capabilities!!! Having a Word version was a bonus! I downloaded the module and tried every example. Took me 2 hours to master the syntax. After that I never looked back!

PowerShell documentation made easy

PScribo has been brought to us by Iain Brighton.  Iain gave me some background information on how PScribo came to be:

I was helping Carl sort out a few “performance” issues with the Word functions he was using. As Carl’s documentation framework uses COM, it is painful! Here are a list of some of the issues:

  1. It’s slow
  2. When MS release a new version (or update) Word things can break
  3. There are localisation issues
  4. There is a reliance on Word actually being installed
  5. PS v5 has actually broken quite a lot of his code (although sped it up too)
  6. The code wasn’t factored particularly well, it is somewhat “better” now!

I told Carl I’d get rid of the Word dependency, improve performance and try to enforce a separation of concerns to aid maintenance. PScribo is the result of this.

So fun fact, I recently saw that Mr. Carl Webster is the guardian of Jeff Wouter’s ADHealthCheck script. A few lines into the script and I’m seeing a lot of Word configuration going on. I thought to myself this needs PScribo! In all my enthusiasm I sent a tweet to Mr Webster not knowing he already knew about PScribo 😛

Iain Twitt

Don’t I feel silly! I wasn’t trying to be cheeky or anything like that, I just thought I’d share what the advantages are of PScribo. I apologize for my ignorance. If I could just take a moment to demonstrate the awesomeness of PScribo, I’m sure you’ll be hooked as well!

Creating the ADHC snapshot

My mantra these days is to gather now and process later. I already did most of the grunt work with the Active Directory configuration snapshot. I just needed to extend it with Users, Privileged groups and Computers. With the snapshot, there’s no limitation where you get your source from. For Forest information, I gathered data using the Get-ADForest cmdlet and also [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest(). The latter also has sites,sitelinks and subnets data, which comes in handy if the Get-ADReplication* cmdlets aren’t available… Gather now, process later… 😉

Here’s what I came up with:

<#
Author: I.Strachan
Version:
Version History:
Purpose: Get Snapshot of Active Directory current Health
#>
[cmdletbinding()]
Param()
Import-Module ActiveDirectory,GroupPolicy -Verbose:$false
#region Helper Functions. Ideally this would be a module. You can also . Source an external script
Function Get-SitesStats{
#Ahsley McGlone Freaky neat AD site links
#https://blogs.technet.microsoft.com/ashleymcglone/2012/09/10/freaky-neat-active-directory-site-links-with-powershell/
Get-ADObject -LDAPFilter '(objectClass=site)' -SearchBase (Get-ADRootDSE).ConfigurationNamingContext -Properties WhenCreated, Description |
Select-Object Name,
@{label='IsEmpty';expression={If ($(Get-ADObject -Filter {ObjectClass -eq 'nTDSDSA'} -SearchBase $_.DistinguishedName)) {$false} else {$true}}},
@{label='DCCount';expression={@($(Get-ADObject -Filter {ObjectClass -eq 'nTDSDSA'} -SearchBase $_.DistinguishedName)).Count}},
@{label='SubnetCount';expression={@($(Get-ADObject -Filter {ObjectClass -eq 'subnet' -and siteObject -eq $_.DistinguishedName} -SearchBase (Get-ADRootDSE).ConfigurationNamingContext)).Count}},
@{label='SiteLinkCount';expression={@($(Get-ADObject -Filter {ObjectClass -eq 'sitelink' -and siteList -eq $_.DistinguishedName} -SearchBase (Get-ADRootDSE).ConfigurationNamingContext)).Count}},
WhenCreated,Description
}
Function Get-GPOsSoM {
#Ashley McGlone GPO Report
#https://blogs.technet.microsoft.com/ashleymcglone/2013/05/29/dude-wheres-my-gpo-using-powershell-to-find-all-of-your-group-policy-links/
BEGIN{
#region Get a list of all GPOs
$GPOs = Get-GPO -All |
Select-Object ID, Path, DisplayName, GPOStatus, WMIFilter
#endregion
#Array for GPLinks results
$gPLinks = @()
#region GPO Linked to the Domain
$domainGPO = @{
Identity = ((Get-ADDomain).distinguishedName)
Properties = @('name', 'distinguishedName', 'gPLink', 'gPOptions', 'canonicalname')
}
$gPlinks += Get-ADObject @domainGPO |
Select-Object 'name', 'distinguishedName', 'gPLink', 'gPOptions', 'canonicalname',
@{name='Depth';expression={0}}
#endregion
#region GPO Linked to OUs
$ouGPOs = @{
Filter = '*'
Properties = @('name', 'distinguishedName', 'gPLink', 'gPOptions', 'canonicalname')
}
$gPLinks += Get-ADOrganizationalUnit @ouGPOs |
Select-Object name, distinguishedName, gPLink, gPOptions ,canonicalname ,
@{name='Depth';expression={($_.distinguishedName -split 'OU=').count - 1}}
#endregion
#region GPOs linked to sites
$siteGPOs = @{
LDAPFilter = '(objectClass=site)'
SearchBase = "CN=Sites,$((Get-ADRootDSE).configurationNamingContext)"
SearchScope = 'Onelevel'
Properties = @('name', 'distinguishedName', 'gPLink', 'gPOptions', 'canonicalname')
}
$gPLinks += Get-ADObject @siteGPOs |
Select-Object name, distinguishedName, gPLink, gPOptions ,canonicalname,
@{name='Depth';expression={0}}
#endregion
#Hashtable to lookup GPOs
$lookupGPO = $GPOs | Group-Object -AsHashTable -Property 'Path'
}
PROCESS{
#Get the Scope of Management of each gPLink
ForEach ($SOM in $gPLinks) {
if ($SOM.gPLink) {
If ($SOM.gPLink.length -gt 1) {
$links = @($SOM.gPLink -split {$_ -eq '[' -or $_ -eq ']'} | Where-Object {$_})
For ( $i = $links.count - 1 ; $i -ge 0 ; $i-- ) {
$GPOData = $links[$i] -split {$_ -eq '/' -or $_ -eq ';'}
[PSCustomObject]@{
Depth = $SOM.Depth;
Name = $SOM.Name;
DistinguishedName = $SOM.distinguishedName;
canonicalName = $SOM.canonicalname;
PolicyDN = $GPOData[2];
LinkOrderNr = $links.count - $i
GUID = $lookupGPO.$($GPOData[2]).ID;
DisplayName = $lookupGPO.$($GPOData[2]).DisplayName;
GPOStatus = $lookupGPO.$($GPOData[2]).GPOStatus;
WMIFilter = $lookupGPO.$($GPOData[2]).WMIFilter.Name;
Config = $GPOData[3];
LinkEnabled = [bool](!([int]$GPOData[3] -band 1));
Enforced = [bool]([int]$GPOData[3] -band 2);
BlockInheritance = [bool]($SOM.gPOptions -band 1)
}
}
}
}
}
}
END{}
}
Function Get-PrivilegedGroups{
#Jeff Wouters script.
Param (
$Domain
)
BEGIN{
$PrivilegedGroups = @(
"$($Domain.DomainSID)-512" #Domain Admins
"$($Domain.DomainSID)-518" #Schema Admins
"$($Domain.DomainSID)-519" #Enterprise Admins
"$($Domain.DomainSID)-520" #Group Policy Creatr Owners
'S-1-5-32-544' #Builtin\Administrators
'S-1-5-32-548' #Builtin\Account Operators
'S-1-5-32-549' #Builtin\Server Operators
'S-1-5-32-550' #Builtin\Print Operators
'S-1-5-32-551' #Builtin\Backup Operators
'S-1-5-32-552' #Builtin\Replicators
'S-1-5-32-556' #Builtin\Network Configuration Operations
'S-1-5-32-557' #Builtin\Incoming Forest Trust Builders
'S-1-5-32-573' #Builtin\Event Log Readers
'S-1-5-32-578' #Builtin\Hyper-V Administrators
'S-1-5-32-580' #Builtin\Remote Management Users
)
}
PROCESS{
$objDomainPrivilegedGroups = @{
DomainSID = $Domain.DomainSID
NETBIOSName = $Domain.Name
FQDN = $Domain.DNSRoot
}
$colPrivilegedGroups = @()
foreach($group in $PrivilegedGroups){
$colPrivilegedGroups += Get-ADGroup -Identity $group -Properties Members,MemberOf
}
$objDomainPrivilegedGroups.Groups = $colPrivilegedGroups
[PSCustomObject]$objDomainPrivilegedGroups
}
END{}
}
#endregion
$snapshot = @{
ADDS = @{}
Users = @{}
Groups = @{}
GPOs = @{}
Computers = @{}
}
#region: snapshotADDS
$snapshot.ADDS.RootDSE = $(Get-ADRootDSE)
$snapshot.ADDS.Forest = $(Get-ADForest)
$snapshot.ADDS._Forest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()
$snapshot.ADDS.Domain = $(Get-ADDomain)
$snapshot.ADDS._Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$snapshot.ADDS.DomainControllers = $(Get-ADDomainController -Filter *)
$snapshot.ADDS.DomainTrusts = (Get-ADTrust -Filter *)
$snapshot.ADDS.DefaultPassWordPoLicy = $(Get-ADDefaultDomainPasswordPolicy)
$snapshot.ADDS.AuthenticationPolicies = $(Get-ADAuthenticationPolicy -LDAPFilter '(name=AuthenticationPolicy*)')
$snapshot.ADDS.AuthenticationPolicySilos = $(Get-ADAuthenticationPolicySilo -Filter 'Name -like "*AuthenticationPolicySilo*"')
$snapshot.ADDS.CentralAccessPolicies = $(Get-ADCentralAccessPolicy -Filter *)
$snapshot.ADDS.CentralAccessRules = $(Get-ADCentralAccessRule -Filter *)
$snapshot.ADDS.ClaimTransformPolicies = $(Get-ADClaimTransformPolicy -Filter *)
$snapshot.ADDS.ClaimTypes = $(Get-ADClaimType -Filter *)
$snapshot.ADDS.DomainAdministrators =$( Get-ADGroup -Identity $('{0}-512' -f (Get-ADDomain).domainSID) | Get-ADGroupMember -Recursive)
$snapshot.ADDS.OrganizationalUnits = $(Get-ADOrganizationalUnit -Filter *)
$snapshot.ADDS.OptionalFeatures = $(Get-ADOptionalFeature -Filter *)
$snapshot.ADDS.Sites = $(Get-ADReplicationSite -Filter *) #Applies To: Windows 8.1, Windows PowerShell 4.0, Windows Server 2012 R2
$snapshot.ADDS.Subnets = $(Get-ADReplicationSubnet -Filter *) #Applies To: Windows 8.1, Windows PowerShell 4.0, Windows Server 2012 R2
$snapshot.ADDS.SiteLinks = $(Get-ADReplicationSiteLink -Filter *) #Applies To: Windows 8.1, Windows PowerShell 4.0, Windows Server 2012 R2
$snapshot.ADDS.ReplicationMetaData = $(Get-ADReplicationPartnerMetadata -Target (Get-ADDomain).DNSRoot -Scope Domain)
$snapshot.ADDS.SitesStats = $(Get-SitesStats) #Courtesy of Ashley McGlone
$snapshot.ADDS.repadmin = $(repadmin.exe /showrepl * /csv | ConvertFrom-CSV) #Courtesy of Ashley McGlone
#endregion
#region snapshotGPOs
$snapshot.GPOs.GPOsSoM = Get-GPOsSoM
$snapshot.GPOs.All = Get-GPO -All | Select-Object '*'
#endregion
#region snapshotUsers
$snapshot.Users.Disabled = Search-ADAccount -AccountDisabled
$snapshot.Users.Expired = Search-ADAccount -AccountExpired
$snapshot.Users.Expiring = Search-ADAccount -AccountExpiring
$snapshot.Users.NoExpireDate = Get-ADUser -LDAPFilter '(|(accountExpires=0)(accountExpires=9223372036854775807))'
$snapshot.Users.Inactive = Search-ADAccount -AccountInactive
$snapshot.Users.NoKerberosPreAuth = get-aduser -filter * -properties DoesNotRequirePreAuth | Where-Object {$_.DoesNotRequirePreAuth}
$snapshot.Users.MustChangePassWord = Get-ADUser -Filter {pwdLastSet -eq 0}
$snapshot.Users.CannotChangePassWord = Get-ADUser -Filter * -Properties CannotChangePassword |Where-Object {$_.CannotChangePassword}
$snapshot.Users.All = Get-ADUser -Filter '*' | Select-Object '*'
#endregion
#region snapshotGroups
$snapshot.Groups.All = Get-ADGroup -Filter '*'
$snapshot.Groups.Privileged = Get-PrivilegedGroups -Domain $(Get-ADDomain)
#endregion
#region snapshotComputers
$snapshot.Computers.All = Get-ADComputer -Filter * -Properties OperatingSystem
$snapshot.Computers.Disabled = Search-ADAccount -AccountDisabled -ComputersOnly
$snapshot.Computers.Expired = Search-ADAccount -AccountExpired -ComputersOnly
$snapshot.Computers.Expiring = Search-ADAccount -AccountExpiring -ComputersOnly
#endregion
#region Export to XML. Change folder to reflect your location
$exportDate = Get-Date -Format ddMMyyyy
$snapshot | Export-Clixml .\export\adds\ADHC-$($exportDate).xml -Encoding UTF8
#endregion
#region Querying snapshot. Try each one seperately!
"`nDisabled users`n"
$snapshot.Users.Disabled | Select-Object Name
"`nExpired users`n"
$snapshot.Users.Expired | Select-Object Name
"`nExpiring users`n"
$snapshot.Users.Expiring | Select-Object Name
"`nPrivilegedGroup`n"
$snapshot.Groups.Privileged.Groups | Select-Object Name
"`nPrivilegedGroup Members count`n"
$snapshot.Groups.Privileged.Groups |
Foreach-object {
[PSCustomObject]@{
Name = $_.Name
MemberCount = @($_.Members).Count
}
} #From here you can do your own filtering
"`nGPOs`n"
$snapshot.GPOs.All | Select-Object DisplayName,ID
"`nGPOs Scope of Management Inheritance blocked`n"
$snapshot.GPOs.GPOsSoM | Select-Object Displayname,BlockInheritance,GUID
"`nMember servers`n"
$snapshot.Computers.All |
Where-Object {
($_.OperatingSystem -like '*server*') -and
(!($_.DistinguishedName -like '*OU=Domain Controllers*'))
} |
Select-Object Name
#endregion

The snapshot helps me concentrate on the task at hand. It’s also a great way to separate the documentation process from gathering all the Active Directory bits and pieces. This makes a huge difference in maintenance! If you need to add anything just update the snapshot.

Now for the fun part!

Creating the PScribo ADHC report

First I need to import the saved snapshot. Export-CliXml & Import-CliXml are growing on me!

#Get ADSnapshot
$ADHCSnapshot = Import-Clixml .\export\adds\ADHC-$($snapshotDate).xml

Next I need to initiate the Document. PScribo is DSL (Domain-specific Language) oriented. This makes the flow quite logical.

#region Create PScribo Document
$reportAD = Document &amp;amp;quot;ADHC snapshot report - $($snapshotDate)&amp;amp;quot; {
   GlobalOption -ForceUppercaseSection -EnableSectionNumbering -PageSize A4 -Margin 24
   BlankLine -Count 20
   Paragraph &amp;amp;quot;Active Directory Health report - $($snapshotDate)&amp;amp;quot;  -Style Title
   BlankLine -Count 20
   PageBreak
   TOC -Name 'Table of Contents'
   PageBreak

A “Document” is an object that contains one or more “sections”, TOC and Paragraphs just to name a few. Just have a look at the README.MD on github get an idea of what is possible and by all means try the examples!

To find the commands available in PScribo run

get-command -Module PScribo

Quick update: Iain just updated PScribo to include landscape page orientation!

Iain Twitt - Landscape

Now to break the document down into sections. Sections can be nested to create sub-sections much like you would do in Word with Header1, Header2, Header 3 etc. The TOC (Table of Content) will be generated according to how the sections are nested. You can also exclude a section from the TOC.

For ADHC, I started with the Forest Information

   Section -Style Heading1 'Forest Information' {
      $ADForest = [Ordered]@{
         Name = $($ADHCSnapshot.ADDS.Forest.Name)
         RootDomain = $($ADHCSnapshot.ADDS.Forest.RootDomain)
         ForestMode = $($ADHCSnapshot.ADDS.Forest.ForestMode.ToString())
         Domains = $($ADHCSnapshot.ADDS.Forest.Domains)
      }

      Table -Name 'Forest Information' -List -Width 0 -Hashtable $ADForest

For this part I created an ordered hashtable to generate a list

My favorite part is the following: Selecting the properties I want to report!

      Section -Style Heading2 'FSMO Roles' {
         $ADHCSnapshot.ADDS.Forest |
         Select-Object DomainNamingMaster,SchemaMaster |
         Table -Name 'FSMO Roles Forest' -List -Width 0

         Blankline

         $ADHCSnapshot.ADDS.Domain |
         Select-Object PDCEmulator,InfrastructureMaster,RIDMaster |
         Table -Name 'FSMO Roles Domain' -List -Width 0
      }

I demonstrated at the end of the ADHC snapshot how you can extract the information you want.

&amp;amp;quot;`nDisabled users`n&amp;amp;quot;
$snapshot.Users.Disabled | Select-Object Name

&amp;amp;quot;`nExpired users`n&amp;amp;quot;
$snapshot.Users.Expired  | Select-Object Name

&amp;amp;quot;`nExpiring users`n&amp;amp;quot;
$snapshot.Users.Expiring | Select-Object Name

To document, it’s as simple as selecting the properties and sending them down the pipeline.

Here’s where you do your formatting and filtering as well:

         Section -Style Heading3 'Privileged groups count'{

            #Create Style for Privileged Groups count greater than 5
            Style -Name PrivilegedGroupsGT5 -Color White -BackgroundColor Firebrick

            $PrivilegedGroupsGT5 = $ADHCSnapshot.Groups.Privileged.Groups |
            Foreach-object {
               [PSCustomObject]@{
                  Name = $_.Name
                  MemberCount = @($_.Members).Count
               }
            }

            #Set Style for Privileged Groups count greater than 5
            $PrivilegedGroupsGT5 | Where-object{ $_.MemberCount -gt 5} | Set-Style -Style 'PrivilegedGroupsGT5'

            Table -InputObject $PrivilegedGroupsGT5 -Name 'Privileged groups count' -Width 0
         }

For the privileged group member count I created a special style that would higlight any row where the count is greater than five.

Privileged Groups.PNG
How cool is that?!

Want a table with all the sites without a description?

         Section -Style Heading3 'Sites without a description' {
            $ADHCSnapshot.ADDS.Sites.Where{$_.Description -eq $null} |
            Select-Object Name |
            Table -Name 'Sites without a description' -Width 0
         }

Here the reporting part:

<#
Author: I.Strachan
Version: 1.0
Version History:
Purpose: Active Directory Health Check PScribo/Excel report
#>
[CmdletBinding()]
Param(
$snapshotDate= '26052016'
)
#PScribo link: https://github.com/iainbrighton/PScribo
Import-Module PScribo,ImportExcel -Verbose:$false
#ImportExcel link: https://github.com/dfinke/ImportExcel
Import-Module ImportExcel -Verbose:$false
#Get ADSnapshot
$ADHCSnapshot = Import-Clixml .\export\adds\ADHC-$($snapshotDate).xml
#region Create PScribo Document
$reportAD = Document "ADHC snapshot report - $($snapshotDate)" {
GlobalOption -ForceUppercaseSection -EnableSectionNumbering -PageSize A4 -Margin 24
BlankLine -Count 20
Paragraph "Active Directory Health report - $($snapshotDate)" -Style Title
BlankLine -Count 20
PageBreak
TOC -Name 'Table of Contents'
PageBreak
Section -Style Heading1 'Forest Information' {
$ADForest = [Ordered]@{
Name = $($ADHCSnapshot.ADDS.Forest.Name)
RootDomain = $($ADHCSnapshot.ADDS.Forest.RootDomain)
ForestMode = $($ADHCSnapshot.ADDS.Forest.ForestMode.ToString())
Domains = $($ADHCSnapshot.ADDS.Forest.Domains)
}
Table -Name 'Forest Information' -List -Width 0 -Hashtable $ADForest
Section -Style Heading2 'FSMO Roles' {
$ADHCSnapshot.ADDS.Forest |
Select-Object DomainNamingMaster,SchemaMaster |
Table -Name 'Forest FSMO Roles' -List -Width 0
Blankline
$ADHCSnapshot.ADDS.Domain |
Select-Object PDCEmulator,InfrastructureMaster,RIDMaster |
Table -Name 'Domain FSMO Roles' -List -Width 0
}
Section -Style Heading2 'Global Catalogs' {
$ADHCSnapshot.ADDS._Forest.GlobalCatalogs |
Select-Object Name |
Table -Name 'Global Catalogs' -Width 0
}
}
PageBreak
Section -Style Heading1 'Domain Information' {
$ADDomain = [Ordered]@{
NetBIOSName = $($ADHCSnapshot.ADDS.Domain.NetBIOSName)
DomainMode = $($ADHCSnapshot.ADDS.Domain.DomainMode.ToString())
DistinguishedName = $($ADHCSnapshot.ADDS.Domain.DistinguishedName)
DomainSID = $($ADHCSnapshot.ADDS.Domain.DomainSID)
}
Table -Name 'Domain Information' -List -Width 0 -Hashtable $ADDomain
Section -Style Heading2 'Domain Controllers' {
$ADHCSnapshot.ADDS.DomainControllers |
Select-Object Name,OperatingSystem,IPv4Address,Site |
Table -Name 'Domain Controllers' -List -Width 0
}
Section -Style Heading2 'Default Domain Password Policy' {
$ADHCSnapshot.ADDS.DefaultPassWordPoLicy |
Select-Object ComplexityEnabled,LockoutDuration,LockoutObservationWindow,LockoutThreshold,
MaxPasswordAge,MinPasswordAge,MinPasswordLength,PasswordHistoryCount,ReversibleEncryptionEnabled |
Table -Name 'Default Domain Password Policy' -List -Width 0
}
Section -Style Heading2 'Domain Administrators' {
$ADHCSnapshot.ADDS.DomainAdministrators |
Select-Object Name,DistinguishedName |
Table -Name 'Domain Administrators' -Width 0
}
PageBreak
Section -Style Heading2 'Organizational Units' {
$ADHCSnapshot.ADDS.OrganizationalUnits |
Select-Object Name,DistinguishedName |
Table -Name 'Organizational Units' -Width 0
}
PageBreak
Section -Style Heading2 'Groups' {
$ADHCSnapshot.Groups.Privileged |
Select-Object DomainSID,NETBIOSName,FQDN |
Table -Name 'Groups' -list -Width 0
Section -Style Heading3 'Privileged groups'{
$ADHCSnapshot.Groups.Privileged.Groups |
Foreach-Object{
[PSCustomObject]@{
Name = $_.Name
Category = $_.GroupCategory.ToString()
Scope = $_.GroupScope.ToString()
SID = $_.SID
}
} |
Table -Name 'Privileged groups' -Width 0
}
Section -Style Heading3 'Privileged groups count'{
#Create Style for Privileged Groups count greater than 5
Style -Name PrivilegedGroupsGT5 -Color White -BackgroundColor Firebrick
$PrivilegedGroupsGT5 = $ADHCSnapshot.Groups.Privileged.Groups |
Foreach-object {
[PSCustomObject]@{
Name = $_.Name
MemberCount = @($_.Members).Count
}
}
#Set Style for Privileged Groups count greater than 5
$PrivilegedGroupsGT5 | Where-object{ $_.MemberCount -gt 5} | Set-Style -Style 'PrivilegedGroupsGT5'
Table -InputObject $PrivilegedGroupsGT5 -Name 'Privileged groups count' -Width 0
}
}
}
PageBreak
Section -Style Heading1 'Sites & Subnets' {
Section -Style Heading2 'Sites' {
$ADHCSnapshot.ADDS.Sites |
Select-Object Name,Description,DistinguishedName |
Table -Name 'Sites & Subnets' -Width 0
Section -Style Heading3 'Sites without a description' {
$ADHCSnapshot.ADDS.Sites.Where{$_.Description -eq $null} |
Select-Object Name |
Table -Name 'Sites without a description' -Width 0
}
if($ADHCSnapshot.ADDS._Forest.Sites.Where{@($_.Subnets).Count -eq 0 }){
Section -Style Heading3 'Sites without a subnet' {
$ADHCSnapshot.ADDS._Forest.Sites.Where{@($_.Subnets).Count -eq 0 } |
Select-Object Name |
Table -Name 'Sites without a subnet' -Width 0
}
}
if($ADHCSnapshot.ADDS._Forest.Sites.Where{@($_.Servers).Count -eq 0 }){
Section -Style Heading3 'Sites without a DC' {
$ADHCSnapshot.ADDS._Forest.Sites.Where{@($_.Servers).Count -eq 0 } |
Select-Object Name |
Table -Name 'Sites without a DC' -Width 0
}
}
Section -Style Heading3 'Sites Stats' {
$ADHCSnapshot.ADDS.SitesStats |
Table -Name 'Sites Stats' -Width 0
}
}
Section -Style Heading2 'Sitelinks' {
$ADHCSnapshot.ADDS.SiteLinks |
Select-Object Name,Cost,ReplicationFrequencyInMinutes |
Table -Name 'Sitelinks' -Width 0
}
Section -Style Heading2 'Subnets' {
$ADHCSnapshot.ADDS.Subnets |
Select-Object Name,Site |
Table -Name 'Subnets' -Width 0
}
}
PageBreak
Section -Style Heading1 'Group Policies' {
$ADHCSnapshot.GPOs.All |
Select-Object DisplayName,Description,GPOStatus,ModificationTime |
Table -Name 'Group Policies' -Width 0
Section -Style Heading2 'Group Policy Scope of Management' {
$ADHCSnapshot.GPOs.GPOsSoM |
Select-Object DisplayName,LinkOrderNr,GPOStatus,LinkEnabled,Enforced,BlockInheritance |
Table -Name 'Group Policy Scope of Management' -Width 0
}
}
}
#endregion
#region Render report in HTML,Word & XML format
$reportAD | Export-Document -Path .\export\adds -Format Html,Word,XML
#Or save it and render later on.
$reportAD | Export-Clixml .\export\adds\ADHCRawPScriboDocument-$($snapshotDate).xml -Encoding UTF8
#endregion
#region Export Users,Group & Computers to Excel
$xlsxUserFile = ".\export\adds\ADHC - UserReport - $($snapshotDate).xlsx"
$xlsxComputerFile = ".\export\adds\ADHC - ComputerReport - $($snapshotDate).xlsx"
$xlsxGroupMembersFile = ".\export\adds\ADHC - PrivilegedMembersReport - $($snapshotDate).xlsx"
$xlsxGroupMemberOfFile = ".\export\adds\ADHC - PrivilegedMemberOfReport - $($snapshotDate).xlsx"
#Users xlsx file
foreach($entry in $ADHCSnapshot.Users.Keys){
$WorkSheetName = $entry
If($ADHCSnapshot.Users.$entry){
$ADHCSnapshot.Users.$entry |
ConvertTo-Csv -Delimiter ';' -NoTypeInformation |
ConvertFrom-Csv -Delimiter ';' |
Export-Excel -Path $xlsxUserFile -WorkSheetname $WorkSheetName -AutoSize -BoldTopRow -FreezeTopRow
}
}
#Computers xlsx file
foreach($entry in $ADHCSnapshot.Computers.Keys){
$WorkSheetName = $entry
if($ADHCSnapshot.Computers.$entry){
$ADHCSnapshot.Computers.$entry |
ConvertTo-Csv -Delimiter ';' -NoTypeInformation |
ConvertFrom-Csv -Delimiter ';' |
Export-Excel -Path $xlsxComputerFile -WorkSheetname $WorkSheetName -AutoSize -BoldTopRow -FreezeTopRow
}
}
#Groups xlsx file
$snapshot.Groups.Privileged.Groups.ForEach{
if($_.Members){
$_.Members |
ForEach-Object{
$_ | Get-ADObject |
Select-Object Name,DistinguishedName
} |
Export-Excel -Path $xlsxGroupMembersFile -WorkSheetname $_.Name -AutoSize -BoldTopRow -FreezeTopRow
}
if($_.MemberOf){
$_.MemberOf |
ForEach-Object{
$_ | Get-ADObject |
Select-Object Name,DistinguishedName
} |
Export-Excel -Path $xlsxGroupMemberOfFile -WorkSheetname $_.Name -AutoSize -BoldTopRow -FreezeTopRow
}
}
foreach($entry in $ADHCSnapshot.Computers.Keys){
$WorkSheetName = $entry
if($ADHCSnapshot.Computers.$entry){
$ADHCSnapshot.Computers.$entry |
ConvertTo-Csv -Delimiter ';' -NoTypeInformation |
ConvertFrom-Csv -Delimiter ';' |
Export-Excel -Path $xlsxComputerFile -WorkSheetname $WorkSheetName -AutoSize -BoldTopRow -FreezeTopRow
}
}
#endregion

Some cool tip & tricks courtesy of Iain

Well-defined XML File

Here’s something else you can do with PScribo: Generate a well-defined XML File!

Iain stated that there are bits of functionality missing. With a well-defined XML file you could do your own XML mapping as part of your Word document generation solution.

Export at a later time

You can export the “document” object with Export-CliXml if you wanted to generate a report later. That way you can recreate the “document” in any format, at any time! Gotta love Export-, Import-CliXml! 😉

Manipulate the object directly

It’s just a [PSCustomObject], but why mess with a good thing eh? Hehe…

Bonus: Excel reports for user, groups and computers

As a added bonus I also threw in some excel files for reporting Users, Groups and Computers using ImportExcel by  Doug Finke. Instead of exporting to csv just to cut/paste in Excel, why not just export directly to a xlsx file!

I hope this gives you an idea what the possibilities are when it comes to reporting. PScribo is awesome! Add ImportExcel to the mix and you’ve got all you need when it comes to documentation!

Hope it’s worth something to you

Ttyl,

Urv

 

Revisiting Exporting Data

Sup PSHomies!

I’ve been playing with different formats lately. I’d like to share a few thoughts on the subject if I may… For demo purposes I’ll be using the following cmdlets: Export-Csv, Export-Clixml and ConvertTo-Json!

Export-Csv

I’ve talked about my love for exporting to csv in the past. Here’s thing, exporting to CSV treats everything as a string. For reporting purposes this might not be an issue. When it comes to nested objects… Yeah… Then you’re better off exporting to XML. Incidentally Jeff Hicks has a great blog on this topic, you should definitely check it out! CSV is still my goto format because of reporting in Excel, although, I’ve been using Doug Finke’s ImportExcel module more and more! Doug’s module cuts out the middle man and can export to Excel without having to export as a CSV first. It does a whole lot more! Worth looking into!

Export-Clixml

Exporting a nested object is pretty straightforward using Export-Clixml. The structure isn’t pretty though. That was the main reason I didn’t use the cmdlet. Export-Clixml is great when used in combination with Import-Clixml, it restores your nested object without a hitch! You can export your results, send the file and import elsewhere for further processing if needed. When I think of xml, I immediately conjure up ideas of html reporting. The xml tags are too cryptic for any css style, I wouldn’t even know where to begin. I recently discovered PScribo (Thanks to the PowerShell Summit in Stockholm), a module by Ian Brighton! This made html reporting a breeze! All I did was import my XML file back into PowerShell to retrieve my nested object and I did the rest in PowerShell! That was awesome!

ConvertTo-Json

The ConvertTo-Json cmdlet has been introduced in PowerShell version 3.0. Back then I was a stickler for XML so I briefly looked at it and forgot all about it… That is until Azure Resource Manager came along. If you’re doing anything with Azure Resource Manager then Json should be on your radar. If you’re not convinced just look at the ARM Templates out there. Json is a lot easier on the eyes for sure. Still not conviced? Just google Json vs XML.

Ok here’s some code you can play with to get a general idea of what the possibilities are when exporting to different formats. Have a look at the Json and Xml, which would you prefer? That was rhetorical… 😉


<#
Author: I.C.A Strachan
Version:
Version History:
Purpose: Demo exporting/importing to/from csv, xml and json format
#>
[CmdletBinding()]
#region Nested Object for Demo purposes
$Win32_ComputerSystem = @(
'DNSHostName'
'Model'
'Manufacturer'
'Domain'
'DomainRole'
'PartOfDomain'
'SystemType'
)
$Win32_OperatingSystem = @(
'Version'
'BuildNumber'
'BuildType'
'OSLanguage'
'OSType'
'OSArchitecture'
'MUILanguages'
'OperatingSystemSKU'
'Organization'
'ProductType'
'ServicePackMajorVersion'
'ServicePackMinorVersion'
'SizeStoredInPagingFiles'
'SystemDevice'
'SystemDirectory'
'WindowsDirectory'
)
$Win32_BIOS = @(
'SMBIOSBIOSVersion'
'Manufacturer'
'Name'
'SerialNumber'
'Version'
'ReleaseDate'
)
function Get-WMIData {
[cmdletbinding()]
param(
[string]
[ValidateNotNullOrEmpty()]
$WMIClass,
[string[]]
[ValidateNotNullOrEmpty()]
$ComputerName,
[string[]]
[ValidateNotNullOrEmpty()]
$WMIProperties
)
$queryProperties =''
foreach ($property in $WMIProperties){
$queryProperties += "$property,"
}
#Remove last character from $queryProperties
$queryProperties = $queryProperties -replace ".$"
Get-wmiObject Query "SELECT $queryProperties FROM $WMIClass" ComputerName $ComputerName |
Select-Object $WMIProperties
}
$Inventory = @{}
$Inventory.ComputerSystem = Get-WMIData WMIClass Win32_ComputerSystem ComputerName LocalHost WMIProperties $Win32_ComputerSystem
$Inventory.OperatingSystem = Get-WMIData WMIClass Win32_OperatingSystem ComputerName LocalHost WMIProperties $Win32_OperatingSystem
$Inventory.BIOS = Get-WMIData WMIClass Win32_BIOS ComputerName LocalHost WMIProperties $Win32_BIOS
#endregion
#region Export-Csv
$exportSplat = @{
NoTypeInformation = $true
Encoding = 'UTF8'
Delimiter = ';'
}
#Export each key to seperate csv File
Foreach ($key in $Inventory.Keys) {
if ( $Inventory.$key.Count -ne 0 ){
$exportSplat.Path = "$PSSCriptRoot\$($key).csv"
$Inventory.$key |
Export-Csv @exportSplat
}
}
#Import Csv for verfication
$importCsv = @{}
#Get csv Files
$csvFiles = Get-ChildItem Filter *.csv Path $PSSCriptRoot File
$importSplat =@{
Delimiter = ';'
Encoding = 'UTF8'
}
Foreach ($file in $csvFiles) {
$importCsv.$($file.BaseName) = Import-Csv Path $file.FullName @importSplat
}
#endregion
#region Export-Clixml
$Inventory |
Export-Clixml Path "$PSSCriptRoot\Inventory.xml" Encoding UTF8
#Import Xml for verfication
$importXml = Import-Clixml Path "$PSSCriptRoot\Inventory.xml"
#endregion
#region Export to Json Format using ConvertTo-Json
$Inventory |
ConvertTo-Json |
Out-File "$PSSCriptRoot\Inventory.json" Encoding utf8
#Import Json for verification
$importJson = Get-Content Path "$PSSCriptRoot\Inventory.json" |
ConvertFrom-Json
#endregion
#region Compare the membertypes of Csv, Xml & Json
$importCsv.ComputerSystem | Get-Member #Everything is a string
$importXml.ComputerSystem | Get-Member #Typecast is preserved
$importJson.ComputerSystem | Get-Member #Typecast is preserved
if ($psISE) {
psedit "$PSSCriptRoot\Inventory.json"
psedit "$PSSCriptRoot\Inventory.xml"
}
else {
notepad.exe "$PSSCriptRoot\Inventory.json"
notepad.exe "$PSSCriptRoot\Inventory.xml"
}
#endregion

Bottom line

Export-Csv is best when you need to report  anything in Excel workbooks and you’re not worried about type. Everyone gets Excel.

Export-Clixml isn’t pretty but excellent when it comes to keeping the data metadata in tact. You can always import preserving the metadata and process further in PowerShell.

Use Json if you want to have a structured data set à la XML. Json is a lot friendlier than XML. I was also surprised that the cmdlet interpreted values to it’s best avail. False became Boolean as you would expect. Json is growing on me…

Hope it’s worth something to you…

Ttyl,

Urv