All posts by Thiyagu

PowerShell

How to remotely get computer CPU and memory usage

Leave a comment

Requirement: One of my clients requested to check the status of the CPU utilization and Memory usage before starting the deployment. If any utilization is beyond the threshold (by taking the N samples of utilization with N intervals), the deployment process needs to stop in the remote machine and share the email notification with the support team.

Post by automation, though to write the post on the same and with sample examples. In Windows PowerShell, there is no exclusive cmdlet to find out the CPU and memory utilization rates. You can use the get-wmi object cmdlet along with the required parameters to fetch the Memory results.

STEP #1: Get the IP and hostname of the Remote server 

Param ( [string]$iP, [string]$hostName )

STEP #2: Assign the username and password to connect the server to execute the remote script

$username = $env:serviceUsername
$password = $env:servicePwd
$userPassword = ConvertTo-SecureString -String $password -AsPlainText -Force
$userCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $userPassword

STEP #3: Use Invoke-Command to connect the server and execute the script inside the connected Machine.

In the Invoke-Command pass the IP of the remote server which going to check the CPU & Memory usage.

Invoke-Command -ComputerName $iP -ScriptBlock {
#…Enter your Logic
} -credential $userCredential

STEP 4: Get the sample CPU usage with an interval of 2 seconds and Find the average.

We can achieve this by using the Get-Counter cmdlet in Powershell. It will get performance counter data directly from the performance monitoring instrumentation in the Windows family of operating systems. Get-Counter gets performance data from a local computer or remote computers. For this example, we fetched the CPU samples 5 times at 2-sec Intervals.

$CPUAveragePerformance = (GET-COUNTER -Counter “\Processor(_Total)\% Processor Time” -SampleInterval 2 -MaxSamples 5 |select -ExpandProperty countersamples | select -ExpandProperty cookedvalue | Measure-Object -Average).average

Write-Host “Average of CPU usage (calculated with 5 Sample with an interval of 2 sec):” $CPUAveragePerformance

STEP 5: Get the Memory usage using Get-WmiObject cmdlet.

$ComputerMemory = Get-WmiObject -ComputerName $env:COMPUTERNAME -Class win32_operatingsystem -ErrorAction Stop

$Memory = ((($ComputerMemory.TotalVisibleMemorySize – $ComputerMemory.FreePhysicalMemory)*100)/ $ComputerMemory.TotalVisibleMemorySize)

$RoundMemory = [math]::Round($Memory, 2)
Write-Host “Memory usage percentage of the system :” $RoundMemory

You can save this script as .PS1 (DEVMachine_ServerHealthCheck.ps1) and call in automation bypassing the IP & Hostname as parameters to get the result.

Full Code:

#Check the CPU & Memory Usage in the Remote Machine

Param ( [string]$iP, [string]$hostName  )

$username = $env:serviceUsername
$password = $env:servicePwd
$userPassword = ConvertTo-SecureString -String $password -AsPlainText -Force
$userCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $userPassword

try {

Invoke-Command -ComputerName $iP -ScriptBlock { 
$CPUAveragePerformance = (GET-COUNTER -Counter "\Processor(_Total)\% Processor Time" -SampleInterval 2 -MaxSamples 5 |
select -ExpandProperty countersamples | 
select -ExpandProperty cookedvalue | Measure-Object -Average).average
Write-Host "Average of CPU usage (calculated with 5 interval of 2 sec) :" $CPUAveragePerformance

$ComputerMemory = Get-WmiObject -ComputerName $hostName -Class win32_operatingsystem
$Memory = ((($ComputerMemory.TotalVisibleMemorySize - 
$ComputerMemory.FreePhysicalMemory)*100)/ $ComputerMemory.TotalVisibleMemorySize) 
$RoundMemory = [math]::Round($Memory, 2)
Write-Host "Memory usage percentage of the system :" $RoundMemory

}  -credential $userCredential
}
catch{
Write-Host $error[0]
}

Output:

PowerShell

Downloading a file with PowerShell

Leave a comment

Scenario:

We got a requirement to download the Excel files on a daily basis from the webpage and need to process the same automatically (window scheduled jobs) and share the report with my supervisors. If we manage this on daily basis by manual and it will occupy some resource bandwidth each day and there is a chance to have some manual error, to avoid this we thought to spend time with automation to complete this task.

In simple, If you need to download files from the web by repeatedly clicking links and processing the data in it on daily basis then you will probably want to automate the task. Windows PowerShell and PowerShell come with file-download capabilities. Using PowerShell to download files is a matter of knowing which cmdlets and .NET classes to use and how to use them

Download File Using PowerShell:

In this discussion, I am going to show how to download a file from a URL using PowerShell and this can be achieved using the Invoke-WebRequest method. We can use Invoke-WebRequest/Invoke-RestMethod/Start-BitsTransfer to download the files in the PowerShell. Whichever one of these methods you use, the logic and components to make them work are the same. To download a file we need to know the source URL and give up a destination for the file that we want to download. If required by the webserver, you need to enter the credentials as well. You can also download the files by using Invoke-RestMethod & Start-BitsTransfer

For our example, I am going to use the Invoke-WebRequest method for downloading the files from the Web. If the source location requires users to log in, the Invoke-WebRequest cmdlet can handle requests with credentials as well.

To download a file, the syntax below shows the minimum parameters required to achieve our requirement. To download the file, the parameter -OutFile is required. You don’t need to enter the full path, but a file name is required. The Outfile expects a path and a filename.

Invoke-WebRequest -Uri <source> -OutFile <destination>

STEP #1 Get the Souce path

# Source URL
$sourceURL = “https://filesamples.com/samples/document/txt/sample3.txt”

STEP #2 Get the Destination path

# Destation file
$destPath = “C:\Thiyagu Disk\dotnet-helpsers\output.txt”

STEP #3  Download the file using Invoke Method.

# Download the file
Invoke-WebRequest -Uri $sourceURL -OutFile $destPath

Full Code:

# Assign SoruceURL and Destination Path
$sourceURL = "https://filesamples.com/samples/document/txt/sample3.txt"
$destPath = "C:\Thiyagu Disk\BLOG_2022\output.txt"
# USe Invoke Method to download the File
Invoke-WebRequest -Uri $sourceURL -OutFile $destPath

Output: 

Authentication with Invoke-WebRequest

Some URLs require you to log in before you can access/download the files. With the Invoke-WebRequest cmdlet, we can provide the credentials that are needed for downloading the files. For this example, i am using the Credential directly in the script. If you are creating a script for automation (that will need to run automatically), then you need to store the credentials in the script itself.

Full Code:

# Assign SoruceURL and Destination Path
$sourceURL = "https://filesamples.com/samples/document/txt/sample3.txt"
$destPath = "C:\Thiyagu Disk\BLOG_2022\output.txt"
# Assign the Username and Pwd to access the $sourceURL
$username = 'XXXXX'
$password = 'XXXXX'
# Convert to SecureString
$secPassword = ConvertTo-SecureString $password -AsPlainText -Force
# Create Credential Object
$credObject = New-Object System.Management.Automation.PSCredential ($username, $secPassword)
# USe Invoke Method to download the File
Invoke-WebRequest -Uri $sourceURL -OutFile $destPath

Points to Remember:

  • The Invoke-WebRequest doesn’t perform a check if the target file exists. If the file already exists, it is overwritten without any warning.
  • If you need to authenticate under the current user on a remote website (via NTLM or Kerberos), you need to add the –UseDefaultCredentials parameter
  • You can also download the files by using Invoke-RestMethod & Start-BitsTransfer

 

 

PowerShell

How to Extract Specific Files from ZIP Archive using PowerShell

1 Comment

When is this required (Real-time Scenario)?

In our blog, we already discussed the zip and unzip files using Powershell. In this post, we are going to discuss how to extract specific files from the zip. I have several zip files that Contain multiple file types and my team got one requirement for extracting only specific files (like .txt/.wav/.xls, file name filter) alone from the Zip on weekly basis and need to ignore all other file types in the zips. If we go for manual then it will be time taking process to check all the zip files on a weekly basis. so we decided to make this automated.

Sample Folder structure used for this example:

How to achieve?

In this example, we are going to use .NET method. To make this process of separating the specific files from the Zip, We need to specify the ‘entry’ object within the .zip file object and pass that to the ExtractToFile() method. Below are the 4 main steps for extracting specific files from the Zip.

  • Fetch the ZIP file and open it for reading.
  • Identifies all files inside the ZIP file.
  • Fetch the files based on the given extension/any matched filters & copy them to the destination folder.
  • Finally, close the zip.

STEP #1: Fetch the ZIP file and open it for reading.

First, we need to open a zip archive for reading (As we are using .NET, we must first open it for reading) at the specified path using the dotnet ZipFile.OpenRead(String) Method (Namespace: System.IO.Compression).

Add-Type -Assembly System.IO.Compression.FileSystem
$zipFile = [IO.Compression.ZipFile]::OpenRead($sourceFilePath)

STEP #2: Identifies all files inside the ZIP file

Next, we need to get all the entries and start identifying/filtering the files. In this example, we are going to fetch all the txt files from the zip.

$zipFile.Entries | where {$_.Name -like ‘*.txt’}

STEP #3: Fetch all the files based on the filters & copy them to the destination folder.

Now you can extract the selected items from the ZIP archive and copy them to the output folder.

foreach {
$FileName = $_.Name
[System.IO.Compression.ZipFileExtensions]::ExtractToFile($_, “$destPath\$FileName”, $true)
}

Note: To only extract a single file from a .zip file, things get a little trickier. We need to specify the ‘entry’ ($zipFile.Entries[0]) object within the .zip file object and pass that to the ExtractToFile() method.   

[System.IO.Compression.ZipFileExtensions]::ExtractToFile($zipFile.Entries[0], “$destPath\ExtractMe1.txt”, $true)

To extract multiple files the target file needs to be composed for each.

$zipFile.Entries | Where-Object Name -like *.txt | ForEach-Object{[System.IO.Compression.ZipFileExtensions]::ExtractToFile($_, “$extractPath\$($_.Name)”, $true)}

STEP #4: Close/Dispose the Zip

$zipFile.Dispose()

Final Code:

 
############################################################################################
#Project: How to Extract Specific Files from ZIP Archive in PowerShell
#Developer: Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 [irp]
#E-Mail: mail2thiyaguji@gmail.com
############################################################################################

$destPath = "C:\dotnet-helpers\Destination\"
$sourcePath = 'C:\dotnet-helpers\Source\ExtractMe.zip'

# load ZIP methods
Add-Type -Assembly System.IO.Compression.FileSystem

# open ZIP archive for reading
$zipFile = [IO.Compression.ZipFile]::OpenRead($sourcePath)

#Find all files in ZIP that match the filter (i.e. file extension)
#Use the Entries property to retrieve the entire collection of entries

$zipFile.Entries | where {$_.Name -like '*.txt'} | foreach {$FileName = $_.Name
[System.IO.Compression.ZipFileExtensions]::ExtractToFile($_, "$destPath\$FileName", $true)}

# close ZIP file
$zipFile.Dispose()

Output

 

PowerShell

How to Pass function as a parameter in PowerShell

Leave a comment

I got a few questions to my inbox, that is “How to pass the function as a parameter (calling function from another function) using PowerShell”. To answer this, I thought to write a quick article with a simple example. Let us quickly discuss with a simple example.

Example 1: 

In the below example, we have two functions named as Supply-Numbers and Show-Numbers. Here the requirement is to invoke/call the Show-Numbers  to print the numbers as output.

function Show-Numbers($number)
{
echo “Number is $number”
}

function Supply-Numbers($function)
{
$numbers = 1..15
foreach ($number in $numbers)
{
# Here we should call $function and pass in $number
}
}

Example 2: Calling a Function From Another Function in PowerShell

To call another function, you need to use the Invoke-Command cmdlet and pass in the argument using the ArgumentList parameter like below.

Invoke-Command $function -ArgumentList $number

The argument list (-ArgumentList) expects an array as its value. So if you want to pass in more than 1 parameter like a number and a text that would look something like below

Invoke-Command $function -ArgumentList $number, $txtMsg

function Show-Numbers($number)
{ 
Write-host “From Supply-Numbers: $number”
}
function Supply-Numbers($function)
{
$numbers = 1..15
foreach ($number in $numbers)
{
Invoke-Command $function -ArgumentList $number
}
}

Supply-Numbers ${function:\Show-Numbers}

Note:

  • “function:” ( prefix ), is used to access the function object’s script block without executing it.
  • The Function provider gives you access to all functions available in PowerShell ant the Function provider gives you access to all functions available in PowerShell

OUTPUT:

Example 2: using -scriptBlockToCall cmdlet

function Show-Numbers {

Param($scriptBlockToCall)
Write-Host “Input from Supply-Numbers Function : $(&$scriptBlockToCall)”
}

Function Supply-Numbers {
return “1”
}

Show-Numbers -scriptBlockToCall { Supply-Numbers }

OUTPUT:

PowerShell

When to use Tee-Object Cmdlet in PowerShell

Leave a comment

In this blog post, we will discuss about Tee-Object cmdlet in PowerShell. Previously I am not got a chance to use this cmdlet in any of my automation and currently using the same in my current work. Due to this, I thought to write a post on this topic which is useful while scripting for others.

You can use the Tee-Object cmdlet to save command output to a file or to a variable. In simple, the Tee-Object cmdlet Saves command output in a file or variable and also sends it down the pipeline (If Tee-Object is the last command in the pipeline, the command output is displayed in the console).

Why do we need to use Tee-Object?

Before I tell you what the Tee-Object cmdlet does, we have to take a small time to talk about the normal way of doing things. Typically, when you use a Get cmdlet (Get-VM, Get-Service, Get-Process, etc.) you will end up doing one of two things.

  • The first option is, we will send the output down the pipeline, and then either display the output on the screen or write it to a file.
  • The other option is to write the cmdlet’s output to a variable.

But, in case if you want to have both then? for this question, we need to have Tee-Object.

Example 1: Normal Scripting without Tee-Object

The first line of code captures the Get-Process cmdlet’s output (get the notepad process) and assigns it to a variable named $processDetails. The second line of code displays the contents of the variable to the screen (as shown in the snapshot).
Hence, these two lines of code’s objective are to display the output on screen and also write it to a variable.

$processDetails = Get-Process notepad
$processDetails

Example 2: Using Tee-Object

Now in this example, let’s take a look at how the same thing might be done using the Tee-Object cmdlet.
The Tee-Objectcmdlet allows output to be written to the screen and simultaneously written to either a file or a variable. In this below example, the output is written in the variable called processDetails

Get-Process notepad | Tee-Object -Variable processDetails

Example 3: Using Tee-Object cmdlet in the middle of the command pipeline

You can also use the Tee-Object cmdlet to be inserted in the middle of the command pipeline as shown below.

If you see the output (from the below snapshot) we are also able to see that the variable’s contents ($notepadProcessDetail) are not an exact match for what is being displayed onscreen, because we declared the variable before using the Select-Object cmdlet.

Get-Process notepad | Tee-Object -Variable notepadProcessDetails | Select-Object ProcessName ,CPU
$notepadProcessDetail

Example 4: Using Tee-Object cmdlet in the Last of the command pipeline

In case, you decided to have the Tee-Object at end of the command pipeline then the variable’s contents will be same as shown in the below snapshot.

Get-Process notepad | Select-Object ProcessName ,CPU | Tee-Object -Variable notepadProcessDetails
$notepadProcessDetail

Example 5: Use Tee-Object for writing output to a file

You can also use the Tee-Object cmdlet to write a command’s output to a file, we want to dump the command’s output to a file instead of writing it to a variable, for this we can do like below.

Get-Process notepad | Tee-Object -FilePath “C:\dotnet-helpers\Details.txt”

You can use the Tee-Object command to declare a variable from the middle of the pipeline (as shown in example 3) or we can also write a file from the middle of the pipeline. In the above example, we wrote in the file rather than using the variable.

 

PowerShell

How to add XML values to the PSCustomObject using Powershell

Leave a comment

We already discussed the reading/Writing XML file using PowerShell in previous posts and How to add values to the string array from XML using Powershell. Now in this post, we will discuss How to add values to the Objects With [pscustomobject] from XML using Powershell. 

PowerShell has XPath but you don’t have necessarily have to use it. Instead of XPath, PowerShell provides the simplest/Easiest way to read XML files, manipulate the XML document, we use the same in the example below. For this demo, we have created a PCdetails.xml file as shown below.

PCdetails.xml File

STEP 1:

The simplest way to read an XML document in PowerShell is to typecast a variable to the type [XML]. To create this variable, you can use the Get-Content cmdlet to read all of the text in an XML document. To typecast the output of Get-Content we can simply prepend the text [xml] before the variable. This tells PowerShell that we need this variable typecasted as a System.Xml.XmlDocument type instead of the default array type that normally comes from Get-Content.

$XML1Filepath = “C:\dotnet-helper\PCdetails.xml”

Once you’ve executed the above cmdlet, the Get-Content cmdlet will read all the raw text from the XML document and cast the output to type System.Xml.XmlDocument, you now have a variable called $XmlDocument that contains the entire XML node tree that represents that document.

STEP 2:

After the execution of STEP 1, the $XmlDocument variable will have the entire XML node tree. Now you can loop the XML element from the parent Node. Here I am fetching all the child elements present under the SYSTEM_INFORMATION element.

$PCVersionsDetails = $XmlDocument.GET_DATA.SYSTEM_INFORMATION.ChildNodes | ForEach-Object {
[pscustomobject]@{
Name = $_.PART_NAME.Value;
Version = $_.PART_VERSION.Value
}
}

STEP 3:

Finally print the Object data.

$PCVersionsDetails

Final Code:

############################################################################
#Project : How to add values to the string Object from xml using Powershell
#Developer : Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 
#E-Mail : mail2thiyaguji@gmail.com 
###########################################################################

$XML1Filepath = "C:\dotnet-helper\PCdetails.xml"
[xml]$XmlDocument = Get-Content $XML1Filepath

$PCVersionsDetails = $XmlDocument.GET_DATA.SYSTEM_INFORMATION.ChildNodes | ForEach-Object {
    [pscustomobject]@{
        Name = $_.PART_NAME.Value;
        Version = $_.PART_VERSION.Value
    }
}

$PCVersionsDetails

OUTPUT:

 

 

PowerShell

Performance comparison between FOREACH LOOP and FOREACH-OBJECT using PowerShell

Leave a comment

If you’re familiar with any programming language then you’re probably also familiar with a ForEach loop. A ForEach loop is a simple language construct that enables you to iterate through a set of items in a collection or array. Whatever programming language it may be, the ForEach loops behave works in the same way.

What will ForEach & ForEach-Object do?

  • ForEach or ForEach-Object will iterate through collections to perform an action against each item in the collection. Each of these approaches can let you run through a collection and then perform actions in a script block.
  • For Each-Object cmdlet loops through the objects and performs code in the script block and references the passed objects as $_.
  • ForEach  variable was explicitly declared as shown in the below example ($item in this example)

Difference between FOREACH LOOP AND FOREACH-OBJECT

The ForEach statement loads all of the items upfront into a collection before processing them one at a time. ForEach-Object expects the items to be streamed via the pipeline, thus lowering the memory requirements, but at the same time, taking a performance hit.

ForEach-Object is best used when sending data through the pipeline because it will continue streaming the objects to the next command in the pipeline, You cannot do the same thing with ForEach () {} because it will break the pipeline and throw error messages if you attempt to send that output to another command.

Speed comparison (Foreach vs. Foreach-Object)

Measure-Command measures how long a command or script takes to complete. This is particularly important for processing large amounts of data. In this post, we going to use Measure-Command to calculate the performance of Foreach and Foreach-Object.

$items = 1…90000
Write-Host “Execution time taken for ForEach-Object =” (Measure-Command { $items | ForEach-Object { “Item: $_” }}).totalmilliseconds
Write-Host “Execution time taken for Foreach =” (Measure-Command { Foreach ($item in $items) { “Item: $element” }}).totalmilliseconds

Notice in the above case you don’t use $_ for ForEach and variable was explicitly declared ($item in this example). Note that two aliases exist for ForEach-Object; ForEach and %.

After the above execution, we can see the iteration time take for ForEach is less than ForEach-Object Cmdlet. Foreach consumes more memory (all objects are stored in memory) than ForEach but it’s faster. The Foreach-Object objects are processed one after another and the results for each object, which goes through the pipe are output instantly. But anyway, my favorite is Foreach-Object. 😉

PowerShell

How to Check if a PowerShell Script is Running with Admin Privileges

Leave a comment

In one of my PowerShell automation, the automated script needs to run with Admin mode. So to avoid the error during the scrip execution, we need to check whether the script is running in the context of a local administrator account or not.

As per my knowledge, PowerShell doesn’t built-in function or Cmdlet that lets us check whether the logged-in user is a member of the Administrators group. To solve this problem, you need to build a function to check the logged-in user’s security status before the main script execution.

The following PowerShell code can be used to check if the current script is running in the “Run as Administrator” mode or not.

STEP #1:  Get logged in user details using a WindowsIdentity object.

You need to use WindowsIdentity class to create a new PowerShell object containing security information about the logged-in user. In the first step, you need to get information about the current user and store it in a variable ($CurrentWindowsIdentity) as shown below.

$CurrentWindowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()

Note:

  • The System.Security.Principal is the base .NET library. The library can be used by C# and PowerShell.
  • The WindowsIdentity.GetCurrent() is a function in the library.

STEP #2:  Creating a new object of type WindowsPrincipal, and pass the Windows Identity to the constructor.

As shown in STEP 1, we got the information about the current user and store it in a variable ($CurrentWindowsIdentity). Now using $CurrentWindowsIdentity you need to create a new PowerShell object as shown below and pass the currently logged-in user object.

The WindowsPrincipal class is primarily used to check the role of a Windows user. The WindowsPrincipal.IsInRole method overloads to check the user role by using different role contexts.

For example, if you want to get the logged-in user name then execute $CurrentWindowsIdentity.Name.

$CurrentWindowsPrincipal = New-Object System.Security.Principal.WindowsPrincipal($CurrentWindowsIdentity)

STEP #3: Check current user below to Admin using WindowsPrincipal.IsInRole method.

Finally, we need to check does the current user has admin privilege using the .IsInRole method. WindowsPrincipal.IsInRole determines whether the current principal belongs to a specified Windows user group and it will output the Boolean result.

$CurrentWindowsPrincipal.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)

Note:

  • IsInRole(WindowsBuiltInRole) determines whether the current principal belongs to the Windows user group with
    the specified WindowsBuiltInRole.

Final Code:

#Returns a WindowsIdentity object that represents the current Windows user.
$CurrentWindowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
#creating a new object of type WindowsPrincipal, and passing the Windows Identity to the constructor.
$CurrentWindowsPrincipal = New-Object System.Security.Principal.WindowsPrincipal($CurrentWindowsIdentity)
#Return True if specific user is Admin else return False
if ($CurrentWindowsPrincipal.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)) 
{

Write-Host "Write your logical code to execute in Admin mode" -ForegroundColor Green
Write-Host "Admin permission is available and Code is running as administrator" -ForegroundColor Green

}
else {

Write-Warning "Insufficient permissions to run this script. Open the PowerShell console as an administrator and run this script again."

}

Output

Opened PowerShell in Admin privilege

Opened PowerShell in Non-Admin private

PowerShell

How to Quickly Find Hyperlinks in Excel using PowerShell

4 Comments

I recently had the requirement to fetch out all of the hyperlinks from a Large excel worksheet to prepare another document and its repetitive monthly task. While I could have gone the manual approach by looking through all of the cells in the worksheet and fetching/copying all of the hyperlinks through manually, that would have been a long grueling thing to perform. So we thought to create an Automated script to perform this action instead of Manual work,

For this scenario, we have the PowerShell COM object creation to hook into Excel. Application COM object and then open up an existing Excel document and locate all of the hyperlinks which are in the spreadsheet. Automation will always be better than manual if allowed to use it. Instead of giving a deep conversation, let us start implementation quickly.

STEP #1: Create Excel.Application COM Object

The first thing that you need to do is to create Excel.Application COM object.

$excel = New-Object -ComObject excel.application
$excel.visible = $False

STEP #2: Open the excel using the Open method

Now you can open up the existing Excel document using the Open method under the Workbooks property on the Excel object. Note that you need to supply the full path of the document else it will throw the error. Here I take a sample sheet with a dummy value (shown in the below snapshot)

$workbook = $excel.Workbooks.Open(‘C:\dotnet-helpers\MyExcel.xlsx’)

STEP #3: Fetch the Hyperlinks using a worksheet object which has a property called Hyperlinks

In this article, based on my requirement here I am using many worksheets so I will Loop the WorkSheet to validate the sheet one by one. If you have only a single worksheet, you can use the WorkSheet property and specify ‘1’ which says to use the first worksheet in the workbook. If you wanted the second worksheet, you would have used a ‘2’ as an increment way.

The worksheet object has a property called Hyperlinks which lists all of the cells which have hyperlinks. As per the below code, not only do you get the hyperlinks, but also the display text, cell Ranges, etc.,

#Looping the Sheets to check all the sheets in the Excel
for ($loop_index = 1; $loop_index -le $workbook.Sheets.Count; $loop_index++)
{
         #Assigining the hyperlinks to the Variable from each sheet
         $Sheet = $workbook.Worksheets($loop_index).Hyperlinks
        #looping the Sheet one by one and store it in collection
        $Sheet | ForEach-Object
                 {
                 $Range = $_.Range
                 [pscustomobject]@{
                 SheetName = $workbook.Worksheets($loop_index).name
                LinkText = $_.TextToDisplay
               Url = $_.Address
               Cell = $Range.Row , $Range.Column
                                                  }
              }
}

STEP #3: Finally kill the Excel process

The below cmdlet will kill all the open excel in the specific system. In my server, we will not open any excel so I am confident to use this in my automation. So you ensure before using this below line of code.

Stop-Process -name excel

Final Code

####################################################
#Project : Fetch all the Hyperlinks from the excel 
#Developer : Thiyagu S (dotnet-helpers.com) 
#Tools : PowerShell 5.1.15063.1155 
#E-Mail : mail2thiyaguji@gmail.com 
####################################################

#Load Excel
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Open('C:\dotnet-helpers\MyExcel.xlsx')

#Looping the Workbook sheet to check all the sheets in the Excel
for ($loop_index = 1; $loop_index -le $workbook.Sheets.Count; $loop_index++)
{
#Assigining the hyperlinks to the Variable from each sheet
$Sheet = $workbook.Worksheets($loop_index).Hyperlinks
#looping the Sheet one by one and store it in collection
$Sheet | ForEach-Object 
    {
    $Range = $_.Range
    [pscustomobject]@{
        SheetName = $workbook.Worksheets($loop_index).name
        LinkText = $_.TextToDisplay
        Url = $_.Address
        Cell = $Range.Row , $Range.Column 
       
                     }
    }
}
#Kill the excel
Stop-Process -name excel

Output

PowerShell

How to call REST APIs using Powershell

Leave a comment

What is API?

The API is not the database or even the server, it is the code that governs the access point(s) for the server. APIs allow developers to interact with online services without actually having to point and click their way through a UI.

Where we can have this scenario?

You can use REST APIs to manage GitHub, Azure, work with Microsoft 365, or use cognitive services in Azure.  Still, we have a lot of other use cases. In this post, we will explain with a very simple guide on how you can make REST API calls from PowerShell.

How to interact with APIs through Powershell?

The two most common PowerShell methods of interacting with REST API’s are to use either Invoke-RestMethod or Invoke-WebRequest.  To interact with a REST API the best option is to use Invoke-RestMethod. Invoke-RestMethod turns input JSON or XML into native PowerShell objects to make further interaction easy. In this post, we will continue our travel with Invoke-RestMethod to access the API.

Example

Let’s start with a simple example. We’ll need an existing REST API to work. Browsing around on the Internet, I come across a Dummy REST API called dummy.restapiexample.com/api/v1/. Every REST API has a base URI and an endpoint. Our API has a base URI of http://dummy.restapiexample.com/ and has an endpoint of /API/v1/employees, making the endpoint URI http://dummy.restapiexample.com/api/v1/employees.

$GetAPI_EMPDetails = @{
“URI” = ‘http://dummy.restapiexample.com/api/v1/employees’
}
Invoke-RestMethod @GetAPI_EMPDetails

Once the above code is executed the GetAPI_EMPDetails will have the object of employee details. Post the, we can start our future manipulation based on the data which we GET from the API.

Output: