All posts by Thiyagu

Merge/Combine JSON objects using Array in PowerShell

In which Scenario do we use JSON object?

Mostly When REST APIs return data in JSON format, you can get at it through PowerShell.So there you have it, working with JSON data can be easy once you turn it into PowerShell objects. Hopefully, this helps you work with APIs and process data on yours.

Recently I was processing some JSON files for one of my projects and wanted to merge multiple similar JSON data into a single JSON.  All the data I was processing were in a similar JSON format, however, coming from different sources. This post will discuss simple JSON data and how we can combine it with single JSON data.

You can also read Application Pool Monitoring Automation – Powershell to check if an application pool has stoppedCreating a scheduled task with PowerShell

Step #1

Declare/Get the JSON data and assigned it to the variable. To make a simple example, I had created two variables to contain the EMP details based on Countries and want to combine those data to have consolidated Employee details.

$Ind_EMPDetails = ‘{ “Indian_EMPDetails” : [ { “firstName”: “Mike”, “lastName”: “John” }, { “firstName”: “Joseph”, “lastName”: “laz” } ] }’

$UK_EMPDetails = ‘{ “UK_EMPDetails” : [ { “firstName”: “Peter”, “lastName”: “Joe” } ] }’

Step #2

Declaring the Empty array for Merging the two JSON objects (ie., $Ind_EMPDetails & $UK_EMPDetails)

$totalEMPDetails = @()

Step #3

Merge the two JSON objects using the ConvertFrom-Json cmdlet. As we are aware, Powershell can’t merge the Json object directly and we need to perform by converting it into the custom PSCustomObject object. We need to use ConvertFrom-Json cmdlet for Combining the JSON objects, ConvertFrom-Json cmdlet converts a JavaScript Object Notation (JSON) formatted string to a custom PSCustomObject object that has a property for each field in the JSON string.

$totalEMPDetails += ConvertFrom-Json $Ind_EMPDetails
$totalEMPDetails += ConvertFrom-Json $UK_EMPDetails

Step #4

After Merging, we can use ConvertTo-Json cmdlet to converting t the Array to a JSON object. Finally, print the output in JSON format.

$totalEMPDetails = $totalEMPDetails | ConvertTo-Json
$totalEMPDetails

Full Example

############################################################################
#Project: How to Combine JSON objects using Array in PowerShell
#Developer: Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 [irp]
#E-Mail: mail2thiyaguji@gmail.com 
###########################################################################

$totalEMPDetails = @()

$Ind_EMPDetails = '{ "Indian_EMPDetails" : [ { "firstName": "Mike", "lastName": "John" }, { "firstName": "Joseph", "lastName": "laz" } ] }'
$UK_EMPDetails = '{ "UK_EMPDetails" : [ { "firstName": "Peter", "lastName": "Joe" } ] }'

$totalEMPDetails += ConvertFrom-Json  $Ind_EMPDetails
$totalEMPDetails += ConvertFrom-Json  $UK_EMPDetails

$totalEMPDetails = $totalEMPDetails | ConvertTo-Json
$totalEMPDetails

OUTPUT:

 

How to add values to the string array from xml using Powershell

We already discussed the reading/Writing XML file using PowerShell in previous posts. Now in this post, we will discuss how to add the value to the Array or Object from the 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.

EMPdetails.xml File

<EMPLOYEE_DETAILS>
    <EMP_NAME>Max</EMP_NAME>
    <EMP_NAME>John</EMP_NAME>
    <EMP_NAME>Mike Ban</EMP_NAME>
    <EMP_NAME>Beny</EMP_NAME>
</EMPLOYEE_DETAILS>

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.

$XML1path = “C:\dotnet-helper\EMPdetails.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:

Now you can declare the empty array for getting XML value.

[String[]]$str_EMP_Array = @()

STEP 3:

After the execution of STEP 1, the $XmlDocument variable will have the entire XML node tree. Now you can loop the XML elements from the parent Node. From the below code, you can loop all the child elements by pointing to the parent node.

foreach ($emp_Detail in $XmlDocument.EMPLOYEE_DETAILS.EMP_NAME)
{
[String[]]$str_EMP_Array += $emp_Detail
}

STEP 4:

Finally, print the Array data.

$str_EMP_Array

Final Code:

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

$XML1path = "C:\dotnet-helper\EMPdetails.xml"
[String[]]$str_EMP_Array = @()
[xml]$XmlDocument = get-content $XML1path
foreach ($emp_Detail in $XmlDocument.EMPLOYEE_DETAILS.EMP_NAME)
{
[String[]]$str_EMP_Array += $emp_Detail
}
$str_EMP_Array

OUTPUT:

Work with Environment Variables using Windows PowerShell – Part I

Question: Hey dotnet-helpers, I wanted to see the values of all the Environment variables in my servers/PC, How do I do this in PowerShell?

Dotnet-helpers Reply :

Windows environment variables provide information about the Windows operating system. Separated into the system and user-level scopes, default environment variables can be read and modified, and new environment variables can be also added.

PowerShell can access and manage environment variables in any of the supported operating system platforms. The PowerShell environment provider simplifies this process by making it easy to view and change the environment variables.

We access environment variables using the built-in variable called $env followed by a colon and the name of the environment variable. The $env variable can be used to access all user context environment variables.

Display all the Environmental Variables

Example 1: Get Environment Variables using dir/gci/cd

Syntax : dir env: OR gci env: OR ls env: OR cd env:

You can get the complete list of environment variables using the below cmdlets which will list out all the environment variables as shown below.

Example 2: Get Environment Variables using Get-Childitem

You can get the complete list of environment variables using the Get-Childitem cmdlet. The following command list all the local environment variables.

Get-Childitem -Path Env:* | Sort-Object Name

Dispay the specific Environment Variable

Syntax : $Env:<variable-name>

In the above syntax, the dollar sign ($) indicates a variable, and the drive name (Env:) indicates an environment variable followed by the variable name (windir).

Example 1: Get Specific Environment variable using $ENV

This script will find temporary files.

$ENV:TEMP

Example 2: Get Specific Environment variable using Get-Childitem

Instead of listing all the environment variables, you can also get the value of a specific environment variable by passing the specified variable name.

Get-Childitem Env:computername

How to Un-ZIP compressed files using PowerShell Expand-Archive cmdlet

In the previous post, we have discussed how to zip the folder/files, so here we going to discuss how to unzip files and folders archives. The process is even easier than compressing them; all you need is the source file and a destination for the data ready to unzip.

Syntax : Expand-Archive -LiteralPath <PathToZipFile> -DestinationPath <PathToDestination>

In the above command, replacing <PathToZipFile> and <PathToDestination> with the path to the files you want to Un-compress and the name and folder you want it to go to, respectively

Example :

Expand-Archive -LiteralPath C:\dotnet-helpers\Source\ExtractMe.zip -DestinationPath C:\dotnet-helpers\Destination\

The destination folder specified to extract the files into will populate with the contents of the archive. If the folder didn’t exist before unzipping, PowerShell will create the folder and place the contents into it before unzipping.

If the destination folder already exists in the destination, PowerShell will return an error when it tries to unzip the files. However, you can use -Force PowerShell to overwrite the data with the new ones using the -Force parameter.

OUTPUT

How to Remove Empty Folders/Directories recursively with PowerShell

As part of System Admin, you will have control of more servers and there may be hundreds of empty folders as junk files which may take up your hard disk. While the junk files occupy disk and it became more Junk in the servers so it became critical to maintaining the important files.

The empty folders don’t take up disk space, but to organize your data better, you may want to trim them every once in a while. If you feel to manually delete empty folders then it will need to routine and time consuming manual work. So we below PowerShell script will help you t to query and delete all empty folders and subfolders.

The following PowerShell command-line deletes empty folders, located under the specified base folder recursively.

STEP #1: Get the recursive child items

First, we need to get the child items from the source path ie., C:\dotnet-helpers\TEMP Folder

//gci alias of Get-ChildItem
gci “C:\dotnet-helpers\TEMP Folder” -r

STEP #2: Fetch all the empty folders

To filter the folders/directories available in the current context, the following property can use $_.psiscontainer. It will return a boolean, indicating whether the current object is a directory or not.

PSIsContainer retrieves an array of strongly typed FileSystemInfo objects representing files and subdirectories of the current directory. The count is not 0, it doesn’t exist at all meaning that the directory is empty or holds other empty folders

(gci “C:\dotnet-helpers\TEMP Folder” -r | ? {$_.PSIsContainer -eq $True}) | ?{$_.GetFileSystemInfos().Count -eq 0}

OUTPUT

STEP #3: Remove the collection of Empty folders.

Finally, use the remove-item cmdlet to remove all the empty folder/directories 

(gci “C:\dotnet-helpers\TEMP Folder” -r | ? {$_.PSIsContainer -eq $True}) | ?{$_.GetFileSystemInfos().Count -eq 0} | remove-item

How to get Sitecore Admin users for the domain using Sitecore PowerShell Extensions

The revisiting of Admin access is an important activity for the support team as most of the time we may provide temporary access for a certain time and forgot to roll back.

In this post, we will explore a great way of extracting Admin user data . This is the simplest way to extract using the Sitecore Powershell ISE.

Sample 1: Get All User mapped with specific domain

##################################################
#Project: Get a list of all users for Domain
#Developer: Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 & SiteCore 8.1
#E-Mail: mail2thiyaguji@gmail.com 
##################################################
#Getting User list by filtering with domain "dotnet-helpers"
$allSitecoreUsers = Get-User -Filter “dotnet-helpers\*”

$allSitecoreUsers | ForEach-Object  {
Write-Host $_.Name
}

Sample 2: Get All Admin Users mapped with specific domain

#####################################################################################################
#Project: How to get Sitecore Admin users for the domain using Sitecore PowerShell Extensions.
#Developer: Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 & SiteCore 8.1
#E-Mail: mail2thiyaguji@gmail.com 
#####################################################################################################
 
$adminSitecoreUsers = Get-User -Filter “dotnet-helpers\*”

$adminSitecoreUsers | Where{$_.IsAdministrator -eq $true } | ForEach-Object  {
Write-Host $_.Name
}

Output

Creating Multiple Tables in single HTML Report using Powershell

Actually System Admins do a lot stuff with Powershell Scripts. Often in Powershell you get lists where data is collected in table format. All Tech/non Tech peoples loves a nice HTML report for reviewing. Creating these type of reports in PowerShell is very easy and simple with Powershell . These type of nice HTML reports can be generate with the help ConvertTo-HTML cmdlet.  Converts Microsoft .NET Framework objects into HTML that can be displayed in a Web browser. The Powershell based Reports are easy to create  and you also can create a unique .css file to make the design for all reports identically to impress with reports.

In my previous article i had posted the article which helps to create single HTLML report. Here i going to deep drive about how to populate multiple reports in single HTML format.

Create CSS File

Here i had wrote the required CSS code for the report and placed the below code in notepad and saved with the name C:\dotnet-helpers\Logreport.css. If required you can create two separate .css file and design your reports based on your requirements. 

body {
  font-family: Monospace;
  font-size: 10pt;
}
table,td, th {
  border: 1px solid black;
}
th {
  color: #00008B;
  background-color: white;
  font-size: 12pt;
}
table {
  margin-left: 30px;
}
h2 {
  font-family: Tahoma;
  color: #6D7B8D;
}
h1 {
  color: #DC143C;
}
h5 {
  color: #c7bc07;
  font-size: 10pt;
}

In the below script, I had creating two separate variable to assign the Application and System Event logs ( $applicationLog ,$systemLog). Here you can get detail post about the Event log, so refer if required more details on it.

Then you can refer the stylesheet from an external CSS file with help of -CssUri parameter (-CssUri ‘C:\dotnet-helpers_com\Logreport.css’) and generate the HTML report in the destination location. The CSS  file will applied to the HTML element during the execution and generate the report.

#######################################################################
#Project : Creating Powershell Mulitple Reports in HTML with CSS Format
#Developer : Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 
#E-Mail : mail2thiyaguji@gmail.com 
######################################################################
 
$applicationLog = Get-EventLog -LogName Application -Newest 5| Select-Object -Property Source, EventID, InstanceId  
convertto-html -Title "Daily Log Report"-PreContent"<H1>Daily Event Applicaton Log Report</H1>" -PostContent "<H5><i>Copy right @ dotnet-helpers.com</i></H5>" -CSSUri "C:\dotnet-helpers_com\Logreport.css"
 
$systemLog = Get-EventLog -LogName System -Newest 5 | Select-Object -Property Source, EventID, InstanceId| 
convertto-html -Title "Daily Log Report" -PreContent "<H1>Daily Event Server Log Report</H1>" -PostContent "<H5><i>Copy right @ dotnet-helpers.com</i></H5>" -CSSUri "C:\Thiyagu Disk\Logreport.css"
 
 
ConvertTo-HTML -body "$applicationLog $systemLog"  | Set-Content "C:\dotnet-helpers_com\EventLogReport.html"

OUTPUT

How to remove duplicate rows in a CSV using Powershell

We are maintaining the user database in our environment, every week automatically the excel will be placed in the the specific folder. Post this, the automatic script will start run and upload the user information in the database by reading the excel file. In our current scenario, the excel will have duplicate entries which will create more than one entry for in the user in the database. The problem is that every once in a while, duplicates end up in the CSV file

To over come this scenario we thought to create script to remove the duplicate detail (which similar in any other rows in excel). This can be achieve by using the Sort-Object and the Import-CSV cmdlet to remove duplicates from a CSV file.

After the contents of the CSV file sorted using Sort-Object, you can use the unique switch to return only unique rows from the file.

Example

#######################################################################
#Project : How to remove duplicate rows in a CSV using Powershell
#Developer : Thiyagu S (dotnet-helpers.com)
#Tools : PowerShell 5.1.15063.1155 
#E-Mail : mail2thiyaguji@gmail.com 
######################################################################

#Getting the Path of CSV file
$inputCSVPath = 'C:\BLOG_2020\DEMO\UserDetails.csv'
#The Import-Csv cmdlet creates table-like custom objects from the items in CSV files
$inputCsv = Import-Csv $inputCSVPath | Sort-Object * -Unique
#The Export-CSV cmdlet creates a CSV file of the objects that you submit. 
#Each object is a row that includes a comma-separated list of the object's property values.
$inputCsv | Export-Csv "C:\\BLOG_2020\DEMO\UserDetails_Final.csv"  -NoTypeInformation

Input CSV:

Output CSV:

Unlocking all locked items in Sitecore using Sitecore PowerShell Extensions (SPE)

In my Sitecore environment, there are 100+ non-admin users are working and we have been asked many times if it is possible for non admin users in Sitecore to be able to unlock items that have been locked by others Users. But in open, we dont have option for non-admin users to Un-Lock the others locked items, in this case they need to contact the specific person or admin users for Un-Locking items.

But this is a feature that many user want to perform Un-locking without others support, So based on requirement we thought to write custom command to unlock the items with help of Sitecore Powershell extension.

In this article, we going to see how to create custom Powershell script for Un-Locking the parent and child items for all the language versions.

STEP 1:

First you need to Get Source , child path items and assigned to the variables ($rootItem, $ChildItems ). After below script execution, the variable $items will contain the parent and its child items collection.

$sourcePath = “/sitecore/content/Sites/White/home/components”
$rootItem = Get-Item -Path $sourcePath
$ChildItems = Get-ChildItem -Path $sourcePath -Recurse
$items = $ChildItems + $rootItem

STEP 2:

Next you need to start looping the items ($items) one by one to check for all the languages.

foreach ($item in $items)
{
foreach ($version in $item.Versions.GetVersions($true))
{
Your unlocking login here….
}
}

STEP 3:

The main logic is to check each item with each language version to verify whether its locked or not. If its locked then below condition will allow to Un-Lock the specific item on specific language version.

if($version.Locking.IsLocked())
{
$version.Editing.BeginEdit();
$version.Locking.Unlock();
$version.Editing.EndEdit();
Write-Host “Item Un-locked” $item.ID “for Language” $version.Language;
}

Full code

###########################################################################################
#Project : Unlocking all locked items in Sitecore using Sitecore PowerShell Extensions (SPE)
#Developer : Thiyagu S (dotnet-helpers.com)
#Tested Tool : Sitecore 8.1 
#E-Mail : mail2thiyaguji@gmail.com 
###########################################################################################

$sourcePath = "/sitecore/content/Sites/White/home/components"

function UnlockSiteCoreItems
{
    $rootItem = Get-Item -Path $sourcePath
    $ChildItemsToUnlock = Get-ChildItem -Path $sourcePath -Recurse
    $items = $ChildItemsToUnlock + $rootItem
    
    foreach ($item in $items)
    {
        foreach ($version in $item.Versions.GetVersions($true))
        {
            if($version.Locking.IsLocked())
            {
                $version.Editing.BeginEdit();
                $version.Locking.Unlock();
                $version.Editing.EndEdit();
                Write-Host "Item Un-locked" $item.ID "for Language" $version.Language;
            }
        }
    }
}
$unlockedItemsDetails = UnlockSiteCoreItems

OUTPUT: