Snippet to extract time part from SQL date and add hours

DECLARE @timeExtract DATETIME = '2018-10-17 22:00:00.000'

SELECT CASE WHEN CAST(@timeExtract as Time) = '23:00:00' then DATEADD(hour, 1, @timeExtract ) ELSE @timeExtract END as UpdatedDate

This will update the time by adding 1 hour when the timestamp is 23:00:00.000

Update Web.Config file using Power Shell

Recently in experts exchange forum some one asked how to update the web.config using Power Shell

https://www.experts-exchange.com/questions/29075387/Edit-multiple-Config-Files.html?notificationFollowed=201918453#a42415246

Here is the code which you can use to update the web.config, change the filter as per your requirement

$files = Get-ChildItem -Path "D:\WebConfig" -Filter "*.config"

foreach($file in $files)
{
    $configFile = $file.FullName
 
    $appConfig = [xml](Get-Content $configFile)
    $appConfig.configuration.ChildNodes | Where-Object Name -Match 'system.web' | ForEach-Object {
    $_.identity.password='testpasswordfrompowershell1';
    $_.identity.userName='testuserfrompowershell1';
}
    $appConfig.Save("$configFile")

}

Adding Sql Server agent service logon as administrator to SSAS

In this Power Shell script I will explain how we can add SQL server agent service logon to SSAS administrator group

Function Add-SqlServiceLogonAccount
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)]
        [string] $SqlServerInstance = $env:computername,
        [parameter(Mandatory=$true)]
        [string]  $AnalysisServerInstance,
        $ComputerName = $env:computername
    )
        try
        {
            $ValidAnalysis = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-String

            if([string]::IsNullOrWhitespace($ValidAnalysis))
            {
                throw "Unable to find either Microsoft.AnalysisServices.AdomdClient or Microsoft.AnalysisServices in GAC"
            }

            $SqlInstance =  $SqlServerInstance.Split("\")
            #Getting the required SQL services running for both default localhost and as well for new Instances
            if($SqlServerInstance -match "\\")
            {
                $SqlInstance = "SQLAgent`$$($SqlInstance[1])"
            }
            else
            {
                $SqlInstance = "SQLSERVERAGENT"
                $SqlServiceDetails = Get-WmiObject -Class Win32_Service -ComputerName $ComputerName |
                               select name,DisplayName, StartName, State |
                               Where {$_.name -eq "$SqlInstance"  -and $_.State -eq "Running" }          
            }

            [string]$loginName = [string]::Empty

            if($SqlServiceDetails -ne $null)
            {
                if($SqlServiceDetails.Name -eq $SqlInstance)
                {
                    $loginName = $SqlServiceDetails.StartName
                }
            }

            if(![string]::IsNullOrWhitespace($loginName))
            {
                $Targetserver = new-Object Microsoft.AnalysisServices.Server
                $Targetserver.Connect($AnalysisServerInstance)

                #Getting members under the role Administrators
                $administrators = $Targetserver.Roles["Administrators"]
                #checking for the existence of loginname, if not exists adding member to Administrators group
                if ($administrators.Members.Name -notcontains $loginName) {
                    Write-Host "Adding the agent logon account $loginName to the Administrators group"
                    $administrators.Members.Add($loginName) | Out-Null
                    $administrators.Update()
                    Write-Host "Adding the agent logon account $loginName to the Administrators group"
                }
                else
                {
                    Write-Verbose "$loginName was already added to the Administrators group"
                }
                $Targetserver.Disconnect()
            }
        }
        catch
        {
            throw $_.Message
        }
    }
}

Copy entire files from TFS to local using PowerShell

In this script I am going to show how we can copy all the files available under a specified path to your local machine

1. cls  
2.   
3. Write-Host "Enter source location "  // your tfs path
4. $sourceLocation = Read-Host  
5.   
6. $tfsCollectionUrl = New-Object System.URI($sourceLocation);  
7.   
8. Write-Host "Enter server path "  // tfs source location $/MyFirstProject/
9. $serverPath = Read-Host  
10.   
11. Write-Host "Enter local path to download"  // your local folder
12. $localPath = Read-Host  
13.   
14. [Microsoft.TeamFoundation.Client.TfsTeamProjectCollection] $tfsCollection = Get-TfsServer $tfsCollectionUrl  
15.   
16. $VersionControl = $tfsCollection.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer])  
17. $latest = [Microsoft.TeamFoundation.VersionControl.Client.VersionSpec]::Latest  
18. $recursionType = [Microsoft.TeamFoundation.VersionControl.Client.RecursionType]::Full     
19.   
20. try  
21. {  
22.     foreach ($item in $VersionControl.GetItems($serverPath, $latest,$recursionType).Items)  
23.  {  
24.     $target =   [io.path]::Combine($localPath,$item.ServerItem.Substring(2))  
25.     $exists=[System.IO.Directory]::Exists($target)  
26.  
27.    if($item.ItemType -eq "Folder" -and !$exists)  
28.    {  
29.        New-Item $target -Type Directory  
30.    }  
31.    if($item.ItemType -eq "File")  
32.    {  
33.       $item.DownloadFile($target)  
34.    }  
35.   }  
36. 
    
37. Write-Host "`n Successfully downloaded all the files to the target folder: " $localPath -ForegroundColor Green  
38. }  
39.     catch  
40.     {  
41.         $ErrorMessage = $_.Exception.Message  
42.         $FailedItem = $_.Exception.ItemName  
43.         Break  
44.     }  

PowerShell - Search XML by passing an input parameter

In this script I would like to show how we can perform XML search by passing an input parameter.
Sample xml file to work with the example

1. <Employees>  
2. <Employee>  
3.   <Name>TestName</Name>  
4.   <Age>32</Age>  
5.   <Location>Hyderabad</Location>  
6. </Employee>  
7. <Employee>  
8.   <Name>TestName1</Name>  
9.   <Age>29</Age>  
10.   <Location>Hyderabad</Location>  
11. </Employee>  
12. <Employee>  
13.   <Name>TestName3</Name>  
14.   <Age>45</Age>  
15.   <Location>Rajahmundry</Location>  
16. </Employee>  
17. </Employees> 

Power shell script 

1. cls  
2. function XmlOperations  
3. {  
4.    param (  
5.         [Parameter(Mandatory)]  
6.         [string] $EmployeeName  
7.     )  
8.   
9.     $xml = [xml](Get-Content C:\sample.xml)  
10.     $xml.Employees.Employee | where { $_.Name -like "*$EmployeeName*" }  
11. }  
12.   
13. XmlOperations

Powershell - Find all the application pools that are stopped in IIS and restart

This script is used to loop through all the application pools and will restart the application pools that are stopped

1. function RestartApplicationPool  
2. {  
3.     Clear-Host  
4.   
5.     $webapps = Get-WebApplication  
6.     [string] $list = @()  
7.       
8.     foreach ($WebAppPools in get-childitem IIS:\AppPools\)  
9.     {  
10.         $name = "IIS:\AppPools\" + $WebAppPools.name  
11.         $item = @{}  
12.    
13.         $item.WebAppName = $WebAppPools.name  
14.         $item.State = (Get-WebAppPoolState -Name $WebAppPools.name).Value  
15.   
16.         if($item.State -eq 'Stopped')  
17.         {  
18.             Start-WebAppPool -Name $WebAppPools.name  
19.             #$obj = New-Object PSObject -Property $item  
20.             $list += $WebAppPools.name  
21.             $list+=" and "  
22.               
23.         }  
24.     }  
25.     if($list.Length -gt 0)  
26.     {  
27.         $list = $list.Remove( $list.Length - 4, 4 )  
28.     }  
29.   
30.     if($list.Length -gt 0) {  
31.         Write-Host "Successfully restared the following " $list -ForegroundColor Green  
32.     }  
33.     else{  
34.         Write-Warning "No application pools found to restart"  
35.     }  
36. }  
37.   
38. RestartApplicationPool  

This is how it will display the result, displays the Application pool that it starts

Vowels and Consonants using power shell

I started working on power shell, I am doing some basic examples which I will share so that it might help some one

In this post I am sharing how we can display the number of Vowels and Consonants in the given string

1. function VowelsandConsonants  
2. {  
3.     [CmdletBinding()]  
4.     param (  
5.         [Parameter(Mandatory)]  
6.         [string] $InputString   
7.     )  
8.       
9.     [string] $VowelString  
10.     [string] $ConsonantsString  
11.     $StringLength = @()  
12.       
13.     $StringLength = $InputString.Length  
14.   
15.     for($i=0;$i -lt $StringLength;$i++)  
16.     {  
17.       
18.         if($InputString[$i] -eq "a" -or $InputString[$i] -eq "e" -or $InputString[$i] -eq "i" -or $InputString[$i] -eq "o" -or $InputString[$i] -eq "u")  
19.         {  
20.             $VowelString = $VowelString + $InputString[$i]  
21.         }  
22.         else  
23.         {  
24.             $ConsonantsString = $ConsonantsString + $InputString[$i]  
25.         }  
26.     }  
27.       
28.     Write-Host "`n Vowles are !" $VowelString -foregroundcolor "green"  
29.     Write-Host "`n Vowles are !" $VowelString.Length -foregroundcolor "green"  
30.     Write-Host "`n Consonants are !" $ConsonantsString -foregroundcolor "red"  
31.     Write-Host "`n Consonants are !" $ConsonantsString.Length -foregroundcolor "red"  
32. }  
33.   
34. VowelsandConsonants

Here is the output when we execute the script