No Graph Article
My goal was to have one Graph article a week, on Wednesdays, but I have not completed my next Graph article, so instead we’ll just do a normal PowerShell Post with some interesting code that I produced for a real-world scenario. Hope this article piques your interest in what PowerShell can be used for.
Introduction
As many of my readers know, I write PowerShell books and publish them on this site Practical PowerShell. These books are not intended to be comprehensive in their coverage of cmdlets in a particular PowerShell module like Exchange Online or the Security and Compliance Center PowerShell module. However, they are intended to be useful and cover as many cmdlets as possible. While I was feeling a bit geeky I decided to see if I could take the book’s raw files (in Word DOCX format) and find a way to analyze how many cmdlets were there and how many were missing from the current books. To do this I used several methods that I have blogged here over the past two or three months.
Workflow
To make this work I needed to perform a series of tasks that ended up with a file of missing cmdlets that I could then read through and decide to place in the book or effectively ignore if they were not common enough for administrative tasks. Here is the overall workflow of the script:
- Log into Exchange Online or Security and Compliance Center PowerShell
- Pick the Word Doc and Book type
- Convert the document to a Txt file
- Get a list of cmdlets for the PowerShell connection
- Scan the document for these cmdlets and export to a txt file
Most of these steps are relatively easy to accomplish with the exception of converting the document to a text file, which we will cover more in-depth than the other sections for this blog post.
Step One: Login
For this part, please reference this blog post which talks about the CBA method for logging into multiple tenants and multiple resources. This reference code can be used for building a connection choice for ExO and SCC.
Step Two: Document and Workload
Simple step, entering the name of the file and the book type to analyze.
$CSVFile = Read-Host -Prompt 'Enter a file to process [ Word Doc Name]' $Content = Import-CSV $CSVFile $Book = Read-Host -Prompt 'Enter Book type [ExO / SCC / Ex19]'
Step Three: Word to Text
Having used PowerShell for quite some time I was familiar with PowerShell’s ability to export information to various file types like Text, CSV, Excel, HTML and more. However, I was not familiar if it was able to take a Word doc (DOCX format) and export it to a TXT. For this portion of the script I had initially tried some PDF to TXT options but had some issues and decided to try Option B for Word doc files. With some research I ran into some good information on these efforts:
Stack Overflow – Word to Txt Code Sample
In order to get the proper save format of a Text file, we can reference this link:
Word Save Formats
With this information I was able to create a code section that would extract the text from my Word doc and place it in a Text file:
# Fixup SavingType
$wdTypes = Add-Type -AssemblyName 'C:\Program Files (x86)\Kutools for Word\Microsoft.Office.Interop.Word.DLL' -Passthru
$wdSaveFormat = $wdTypes | Where {$_.Name -eq "wdSaveFormat"}
# Which file (s) to convert:
$SRCFile = Get-ChildItem $CSVFile
$NoExtension = $CSVFile.split(".")[0]+".txt"
$Path = (Get-Item -Path ".\" -Verbose).FullName
$FileName = $Path+"\"+$NoExtension
# Text File Format
$SaveFormat = [Enum]::Parse([Microsoft.Office.Interop.Word.WdSaveFormat], "wdFormatTextLineBreaks");
# Open Word App
$Word = New-Object -COMObject Word.Application
$Word.Visible = $False
# Perform conversion:
Function SaveAS-TextFile {
$WordDoc = $Word.Documents.Open($Doc.FullName);
$WordDoc.SaveAs([ref]"$FileName", [ref]$SaveFormat);
$WordDoc.Close();
}
# Process each doc:
Write-Host "Processing :" $SrcFile.FullName
SaveAS-TextFile
Some notes on the above. One key component that makes this work is having the ‘Microsoft.Office.Interop.Word.DLL’ file accessible to the script as this allows for the conversion process of the Word doc file. Also, having the correct file format will also lead to success as I originlly used another TXT file format and it failed.
Step Four: List of Cmdlets
In this step we now need to query a list of cmdlets. As we have made a connection to either Exchange Online or the Security and Compliance Center we can use PowerShell to get a current list of cmdlets available. In the below examples, the list pulled depends on which Book option we choose in a previous stage (ExO or SCC). Here are the two blocks of code:
# Exchange Online Cmdlets
If ($Connection -eq 'ExO') {
$Commands = (Get-Command | Where ModuleType -eq ScriptWhere Source -like 'tmp*').Name
$Commands2 = (Get-Command | Where Source -eq 'ExchangeOnlineManagement').Name
$CommandList += $Commands
$CommandList += $Commands2
}
# SCC Online Cmdlets
If ($Connection -eq 'SCC') {
$CommandList = (Get-Command | Where ModuleType -eq ScriptWhere Source -like 'tmp*').Name
}
Step Five: Search for text
So now we have a Word doc converted into a Text file and a list of cmdlets we are reviewing this text file. All we need to do now is to scan the file for these cmdlets. This block of code will do that for us:
# Import TXT version of book:
$Content = Import-CSV $FileName
# Variables
$MissingCount = 0
$InBookCount = 0
$MissingCommands = @()
$FoundCommands = @()
# Body
Foreach ($Command in $CommandList) {
$Text = "*"+"$Command"+"*"
If ($Content -Like $Text) {
$InBookCount++
$FoundCommands +=$Command
} Else {
$MissingCount++
$MissingCommands += $Command
}
}
# Results
Write-Host "Found: $InBookCount"
Write-Host "Missing: $MissingCount"
Write-Host '-----------------------------------------'
$File = "MissingCommands-"+"$Book"+".txt"
$MissingCommands | Out-File -FilePath $File
Sample Output of the script:
** Yellow rectangles are data I entered. The one warnng near the end is because I am using Import-Csv for simplicity and the file has no headers. **
Conclusion
While you may not have the same need of PowerShell, then goal of the article was not necessarily how to search a document for text, but a combination of how to take multiple code goals and combine them into one for a singular purpose. Hopefully you were able to see this and it provides some guidance for your future PowerShell projects which require the combination of multiple code sets into a singular one.
———————————————————————————————————–
Comments? Questions?
Feel free to leave your Comments below! Learn to more efficiently utilize PowerShell to manage Exchange Server, Exchange Online, Microsoft Defender for Office or Microsoft Purview Compliance portals by picking up frequently updated eBooks:
