PowerShell LInks

Get-AdUser Options

PowerShell Universe

PowerShell Verbs

Notepad++ Scripts

 Below are scripts that I have found useful when working in Notepad++.

Find Special Characters in a file using Regular expression:  [^\x00-\x7F]+

Powershell AD Scripts

This script will give you a list of AD groups based on a filter.

get-adgroup -filter{name -like  "_*"} -Properties Description | Select Name

View details of a specific user

Get-ADUser -Identity James.Lawyer -Properties *

View All users in an AD Group

Get-ADGroupMember -identity "RS_BusinessFinance" -Recursive | Get-ADUser -Property DisplayName
 | Select Name

Get AD Group Details/Properties

Get-ADGroup -Identity RS_ASPRINCIPALS -Properties *

Using Like in the Where clause on SSRS Reports

In most of my SSRS reports I use IN or = for Parameters, but sometimes you may need to use Like in the where clause.  There are a number of ways you can do this, but I am only showing the method I use.

Select * from Test
Where UserName Like '%' + @ParameterName + '%'

Conditional Formatting with Expression

The following code can be used in the background property in SSRS for a field to change the color.  In this example I am changing the background color of a Totals field based off of another field (School.)

So if the School field = "District" then I want the Total Field to be Yellow, If it equals Secondary then I want it to be Red and Finally if it equals Elementary I want the field to be Blue

1. In Design View for your report, click on the Textbox you want the colors to be used.
2. Find the Background Color under Fill in the Properties.
3. Select the dropdown for background color and choose Expression
4. Use the code below as a starting point for your conditional Formatting.

=Switch(Fields!School.Value = "District", "Yellow", Fields!School.Value = "Secondary", "Red",Fields!School.Value = "Elementary", "Blue")

Export to Excel Without Page Breaks

In SSRS, by default, the Export to Excel option will render a report with Page Breaks into multiple tabs.  This is fine if you want to view the data this way, but if you would prefer to have all the data on one tab then you will need to do the following in the design view of the SSRS Report: 

1. Click the group object under Row Groups.

2. In the Properties, Click on the Group arrow to expand if the section is not already expanded.

3. Click on the Group arrow on the second line under Group to expand. 

4. Click the arrow to expand PageBreak.

5. Select End for BreakLocation

6. Select Expression for Disabled and type the following in the expression: =IIf(Globals!RenderFormat.Name="EXCELOPENXML", True, False)

7. Save and check the report to ensure the fix worked.  

Use EXCEL to create SQL Insert Code

This script can be put into EXCEL to create Insert statements for SQL.  This will save a lot of time if you have data in EXCEL that needs to be put into a SQL table. 

This looks at 1 column and adds ' around data
="Insert into tmpCourses (Courses) VALUES ("&"'" & A1 & "'"& ");"

This looks at one column adding Text string before the data

="Insert into UserIDs (userid) VALUES ("& "'SCHOOL\"& A2 & "'"& ");"

This script looks at 3 columns

="Insert into Users (userid,first,last) VALUES ("& A2 & ","& CONCATENATE("'",B2,"'") &", "& CONCATENATE("'",C2,"'") &");"

Format Date with DATEPART

This is helpful when you need to get rid of milliseconds in a date for sorting.

DATETIMEFROMPARTS ( year(s.LastRunTime), month(s.LastRunTime), day(s.LastRunTime), DATEPART(hh,s.LastRunTime), DATEPART(mi,s.LastRunTime), 0, 0)

List of Date Formats

The website below has a list of SQL Server Date Formats.

http://www.sql-server-helper.com/tips/date-formats.aspx

Subquery Join with Max Date

The query below is used when you have a table with multiple rows per person and you want to select the MAX Date.  First you will select the fields you want to display in the query, then you will create a subquery to select the Max Date, then you will join the query and subquery on the ID and the Max Date.

Add Subquery As A Column

The SQL below adds aggregated columns to a query by using sub queries. The key to using this type of query is to join the inner and outer tables on the correct field.

Use the Instant SQL Formatter to Format the SQL below.  Just copy and paste into the Formatter at: http://www.dpriver.com/pp/sqlformat.htm

SELECT DISTINCT a.eeo_cls,

                a.classification,

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-I',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-I',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'M'

                        AND C.eeo_cls = a.eeo_cls) AS 'M-O',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'F'

                        AND C.eeo_cls = a.eeo_cls) AS 'F-O'

FROM   #ee05temp a

       INNER JOIN #ee05temp b

               ON a.prem_emp = b.prem_emp

       INNER JOIN #ee05_multiple

               ON a.prem_emp = b.prem_emp

GROUP  BY a.classification,

          a.eeo_cls

ORDER  BY a.eeo_cls 

SQL Help

This is a pretty good website for coders: Code Project.  I use mainly for SQL information and they have a cool visual representation for SQL Joins.

PowerShell Copy and Rename Files

# Define variables.

$Source = "C:\Test\*.*"

$Destination = "C:\Testing\"

$DestinationFiles = "C:\Testing\*.*"



# Create the $sourceFileList variable to loop through

$sourceFileList = Get-ChildItem -path $Source



# Loop through the $soureFileList and copy the items to the $Destination.

foreach ($item in $sourceFileList)

{

    Copy-Item -Path $Source -Destination $Destination

}



# Create the $destinationFileList variable to loop through

$destinationFileList = Get-ChildItem -path $DestinationFiles



# Loop through the $destinationFileList and rename the files with appended DateTime stamp.

foreach ($itemDest in $destinationFileList)

{

    $Date = (Get-Date).ToString("yyyyMMdd_HHmmss")

    $newFileName = $Date + "_" + $itemDest.Name

    Rename-Item $itemDest -NewName $newFileName

}

T-SQL ESCAPE for Querying Underscores

In T-SQL the underscore is a special character when querying/filtering records. 





The following code will return all records and not only those with an underscore because the underscore acts like a wildcard if not escaped.




Select * from TableName

Where ColumnName Like '%_%'




Using ESCAPE in the query will make the special character such as the underscore a literate character.  The following code will return only those records that contain an underscore.




Select * from TableName

Where ColumnName Like '%\_% ESCAPE '\'




More information about ESCAPE

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