Replace String In File

The following PowerShell script replaces all occurrences of the string in file.

#Usage powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 "c:\Temp\DirlistDEV.txt" '.' '`,'
#Calling from VBA: Call Shell("powershell -executionpolicy bypass C:\SharedToAnyone\ReportsAudit\ReplaceStringInFile.ps1 'c:\Temp\DirlistDEV.txt' '.' ','")

    Clear-Host;
    $inputFile = $($args[0]);
    if ($inputFile) {
        Write-Host "Passed: $inputFile`n";
            $index = $inputFile.IndexOf(".");
            if ($index -eq 0) {
                Write-Host "Input file does not have any extensions";
                break;
            }
    } else {
        Write-Host "No file passed";
        break;
    }
    $findString = $($args[1]);
    if ($findString) {
        Write-Host "Passed string to find: $findString`n";
    } else {
        Write-Host "No string to find passed";
        break;
    }
    $findString = $findString.Replace(".","\.");
    $findString;
    $Replacement = $($args[2]);
    if ($Replacement) {
        Write-Host "Passed replacement string : $Replacement`n";
    } else {
        Write-Host "No replacement string passed";
        break;
    }
    $outputFile = $inputFile.SubString(0,$index) + ".tmp"
    $outputFile;
    Get-Content $inputfile | ForEach-Object { $_ -replace $findString, $Replacement } | Set-Content ($outputFile)
    Remove-Item $inputfile
    Rename-Item $outputFile $inputfile 

If you find this script useful please donate generously.

MS SSRS Reporting Management vs TFS Branches Reconcilation

The MS Access database attached allows to reconcile DEV, UAT and PROD TFS Branches with SSRS reports deployed to the Reporting Management Service.  It requires to create a ODBC connection to ReportServer SQL Server PROD database and uses the Catalog table records to compare against.
The ReconcilePROD_ReportingServerManagementWithTFSBranches.accdb database availlable for download.

If you find this script useful please donate generously.

Extracting MS Active Directory Users

The following user defined function returns query against MS Active Directory:

USE active_directory
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT * 
          FROM sys.objects
          WHERE name = 'implementation.ADusers')
        DROP FUNCTION implementation.ADusers
        PRINT 'DROP FUNCTION implementation.ADusers'
GO

PRINT 'CREATE FUNCTION implementation.ADusers'
GO

CREATE FUNCTION implementation.ADusers()
/************************************************************************************************************************************
AD users

Usage: EXEC sp_executesql @strSQL

Change Log
 #    Date       Who  Change
*************************************************************************************************************************************/ 
 RETURNS nvarchar(max)
 BEGIN
    DECLARE @strSQL1 AS varchar(600)
    ,       @strSQL2 AS varchar(max) 
    ,       @strSQL3 AS varchar(100)
    ,       @strSQL  AS nvarchar(max) 

    SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
    ,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
                    ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
                END AS datetime
            ) FROM (
    '

    SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
    FROM (
    SELECT char(decimal#) alpha
    ,'SELECT  samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint) 
        FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
        FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
        WHERE    objectCategory = ''''Person'''' and    objectClass = ''''USER'''' and samaccountname = '
     + '''''' + CHAR(decimal#) + '*' + ''''''''
     + IIF(CHAR(decimal#) <> 'z',' 
    ) UNION ALL 
     ','')    strSQL
    FROM (select row_number() over (order by (select 1)) as decimal#
    FROM sys.all_columns) cte_tally
    WHERE decimal# > 96 and decimal# < 123) alpha_union

    SET @strSQL3 = (SELECT ')) t    
    WHERE t.distinguishedName LIKE ''%OU=Users%''')

    SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3
    RETURN @strSQL
END

If you find this script useful please donate generously.