Change File Name with Powershell

Recently I began backing up photos from 15+ year old CD and DVDs to an external hard drive.  One problem I came across was the created date changed to the date I copied the file, but the modified date stayed in tact.  This is important for me because of the way I name and archive my photos.  I use a program titled NAMEXIF to change most of my photo names, but it would not work because a lot of my old photos do not have EXIF data so I had to find a different solution.  In doing some research I found out that you can accomplish the name change of photos/files from powershell. 



First I had to create a directory to place the files I wanted to change.  I copied over the pictures from the original directory into a Testing directory named C:\Test.  This is not necessary, if you are confident in the script, but I wanted to ensure I didn't mess up anything the first time since I haven't used Powershell much. 



1. Copy files into the Test directory.



In Powershell


2. Use the following code to get to the correct directory so you can run the script below: cd C:\Test



3. Run the following Powershell script:

Get-ChildItem *.jpg | Rename-Item -newname {$_.LastWriteTime.toString("yyyy-MM-dd-HH-mm-ss") + ".jpg"} 

Pass SSRS Field Values to URL

Recently a coworker wanted to pass a field value (ID) to a URL in order to go directly to a specific Web Page from an SSRS report.  This can be done in SSRS by creating a dynamic expression in the Action section of the Text Box or Item that will be clicked.  If you choose the Go To URL option you can create an expression that will do the job. Below is an example.

Dynamic expression that simply concatenates a URL with the value of a field in the report.  In this example the name of the field is ID.  

="http://www.somedomain.com/id="&Fields!ID.Value

This example uses javascript to open a new window. 

Notice: The format is different. When using javascript you have to use + instead of & to concatenate. 

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?"+ Fields!ElementID.Value + "','_blank'))"

This example opens a new window and uses the Right Function on the Field Value

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?r"+ Right(Fields!PEIMSRecord.Value,3)+ "','_blank'))"

This example opens a new window and grabs the url from a field within a database table.

="javascript:void(window.open('"+Fields!RefURL.Value+"','_blank'))"

Contributors: Mark Lansdon, Trey Lawyer

SSRS - Top Directory

Below is a script that can be used to strip out the '/' from the SSRS Path so you can display the Top Folder/Directory.

Replace(Left(Catalog.Path,CHARINDEX('/',Catalog.Path,2)-1),'/','') As Top_Folder

The following query will return information regarding subscriptions located on your SQL Server.

SELECT catalog.NAME,
       catalog.type,
       catalog.description,
       catalog.creationdate,
       catalog.modifieddate,
       subscriptions.laststatus,
       subscriptions.lastruntime,
       users.username,
       catalog.path,
       Replace(LEFT(catalog.path, Charindex('/', catalog.path, 2) - 1), '/', '')
       AS
       Top_Directory
FROM   catalog
       INNER JOIN subscriptions
               ON catalog.itemid = subscriptions.report_oid
       LEFT OUTER JOIN users
                    ON catalog.createdbyid = users.userid
                       AND catalog.modifiedbyid = users.userid
                       AND subscriptions.modifiedbyid = users.userid
                       AND subscriptions.ownerid = users.userid
WHERE  subscriptions.lastruntime >= '8/1/2015'; 

Download All RDL Files

The following is a great PowerShell Script for downloading all RDL files from the ReportServer Database.  Without a script to download the RDL files you would have to go into each report from the Web Interface and select Download.  This script could save days worth of work.


<# .SYNOPSIS
      Export of all SSRS reports datasources and images
   .DESCRIPTION
      This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database
      to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -.
      Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension.
      Please change the "Configuration data" below to your enviroment.
      Works with SQL Server 2005 and higher versions in all editions.
      Requires SELECT permission on the ReportServer database.
   .NOTES
      Author  : Olaf Helper
      Requires: PowerShell Version 1.0, Ado.Net assembly
   .LINK
      GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx
#>


# Configuration data
[string] $server   = "ServerName";        # SQL Server Instance.
[string] $database = "ReportServer";        # ReportServer Database.
[string] $folder   = "\\Some Location";          # Path to export the reports to.

# Select-Statement for file name & blob data with filter.
$sql = "SELECT CT.[Path]
              ,CT.[Type]
              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent
        FROM dbo.[Catalog] AS CT
        WHERE CT.[Type] IN (2, 3, 5)";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$rd = $cmd.ExecuteReader();

$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($rd.Read())
{
    Try
    {
        # Get the name and make it valid.
        $name = $rd.GetString(0);
        foreach ($invalid in $invalids)
           {    $name = $name.Replace($invalid, "-");    }

        If ($rd.GetInt32(1) -eq 2)
            {    $name = $name + ".rdl";    }
        ElseIf ($rd.GetInt32(1) -eq 5)
            {    $name = $name + ".rds";    }

        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $name);

        $name = [System.IO.Path]::Combine($folder, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($name), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        $bt = $rd.GetSqlBinary(2).Value;
        $bw.Write($bt, 0, $bt.Length);
        $bw.Flush();
        $bw.Close();
        $fs.Close();
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}

# Closing & Disposing all objects
$rd.Close();
$cmd.Dispose();
$con.Close();
$con.Dispose();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");

Reference:
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227

SSRS-Users, Reports and Permissions

Working with SSRS security can be frustrating at times because of the limitations of the web interface, especially if your company has thousands of reports and users.  If you work with SSRS it is important to get familiar with the ReportServer database in SSMS.  All of the data, including the XML that produces reports on the report Server is located in ReportServer tables.

The following is a simple query to return UserName, Roles, Role Descriptions, Report Path and Report Name.  This script will come in handy if you are ever asked to provide a list of users, their roles and what reports they can access.

Select C.UserName, D.RoleName, D.Description, E.Path, E.Name 

from dbo.PolicyUserRole A

   inner join dbo.Policies B on A.PolicyID = B.PolicyID

   inner join dbo.Users C on A.UserID = C.UserID

   inner join dbo.Roles D on A.RoleID = D.RoleID

   inner join dbo.Catalog E on A.PolicyID = E.PolicyID

Where C.UserName = 'DOMAIN\USERNAME'

order by C.UserName   

-----------------------------------------------------------------------------

FnSplit Function

The FnSplit Function comes in handy when using stored procedures within SSRS reports.  The function splits out the values for parameters and separates the values with commas.  This is needed if you want to select multiple values within a report.  There is an option for multiple values in the Report Builder and if your SQL is In-Line within the report you can simply use the following:

Select Field
From Table
Where School in (@SCHOOL)

However, this does not work properly in Stored Procedures.

There may be other options, but the one I use most often is the FnSplit Function.

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))

Depending on the report criteria you may need to add more code.  For example, if you want to select all values and have the ability to also select multiple values you will need to add an If statement to the stored procedure.

The first thing you have to do is add an option to your parameter.  If your parameter is a query you can do something like the following:

Select SchoolID, SchoolName
From Schools
Union
Select '000', '000-All Schools'

Stored Procedure:

If @School = '000'
Begin

Select Field
From Table
End

Else

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))
End