SQL Cheat Sheet

A place to save the SQL queries that I constantly have to Google.

SELECT Statements

SELECT * 
  FROM [dbo].[table]
  WHERE (column = 'value1') AND (Column = 'value2')

SELECT DISTINCT [column] 
  FROM [dbo].[table]

SELECT column1, COUNT(column2) 
  FROM [dbo].[table]
  GROUP BY email
  HAVING ( COUNT(email) > 1 )

 DECLARE @Temp TABLE (NAME varchar(20))
  INSERT INTO @Temp (NAME)
  SELECT accountName --, COUNT(AccountName) AS Count
  FROM [StagingDirectory].[dbo].[Identities]
  GROUP BY accountName
  HAVING ( COUNT(accountName) > 1 )

  SELECT [accountName]
      ,[lastName]
      ,[firstName]
      ,[initials]
      ,[employeeID]
      ,[employeeStatus]
      ,[employeeType]
      ,[employeeNumber]
  FROM @Temp Temp
  JOIN Identities
  ON Temp.name=Identities.accountname
  ORDER BY accountName

UPDATE Statements

UPDATE [dbo].[table]
  SET column='value'
  WHERE column='value';

UPDATE [dbo].[table] 
  SET Column = REPLACE(Column,'xx','XX')

INSERT Statements

INSERT INTO [dbo].[table] (column1,column2,column3)
  VALUES ('value1','value2','value3');

DELETE Statements

DELETE FROM [dbo].[table] 
  WHERE column = 'value'

MERGE Statements

BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

PowerShell to Remove Password Not Required Flag in AD

We had a couple thousand users in our test AD that had the Password Not Required flag set without a password. This was causing an error when Microsoft Identity Management tried to set the password for these user objects.

We used the following script to remove the flag and set a password.

$Users = Get-ADUser -searchscope subtree -ldapfilter "(&(objectCategory=User)(userAccountControl:1.2.840.113556.1.4.803:=32))" 
$newPassword = (Read-Host -Prompt "Provide New Password" -AsSecureString)

foreach ($user in $Users)
    {
    Set-ADAccountPassword -Identity $User.samaccountname -NewPassword $newPassword -Reset
    Set-ADAccountControl $User.samaccountname -PasswordNotRequired $false
    }

MIM 2016 Metaverse Cleanup by Deleting CS

Cleaning up poorly constructed joins in Microsoft Identity Manager by cleaning out the FIMMA and ADMA connector spaces with the following steps:

1 – Disable “Synchronization Rule Provisioning” in the Synchronization Management tool
2 – Disable the Metaverse object deletion rule
3 – Delete the ADMA connector space
4 – Delete the FIMMA connector space
5 – Perform a Delta synchronize on each management agent to process disconnectors clean those objects from the the metaverse
6 – Run a Full Import on the ADMA and FIMMA
7 – Run a Full synchronization on FIMMA first to project objects into the metaverse
8 – RUn a Full synchronization on the ADMA so that the objects in the ADMA CS join objects in the MV
9 – Re-enable “Synchronization Rule Provisioning” in the Synchronization Management tool
10 – Run Full synchronization and Export on both the AD and FIM management agents
11 – Recreate the object deletion rule

Use PowerShell to Create a Storage Usage Report for a Windows Server

This PowerShell script will give you a report for a server’s storage usage. It will give you a status of “Green”, “Amber”, or “Red” for each drive.

$StorageReport = @()
$date = get-date -uformat "%Y-%m-%d"
$Drives = Get-WmiObject -Class Win32_LogicalDisk | ? {$_.drivetype -eq 3} | select DeviceID,Size,Freespace,drivetype,VolumeName

foreach ($Drive in $Drives)
    {
    if ($Drive.freespace)
        {
        $PercentFree = "{0:N2}" -f (($drive.freespace / $drive.size) * 100)
        $Size = "{0:N2}" -f (($drive.size / 1024 / 1024 / 1024))
        $Freespace = "{0:N2}" -f (($drive.freespace / 1024 / 1024 / 1024))
        switch ($PercentFree)
        {
        {$_ -gt 20} {$Status="Green"}
        {($_ -lt 20)-and($_ -gt 10)} {$Status="Amber"}
        {$_ -lt 10} {$Status="Red"}
        Default {$Status="Unknown"}
        }

        $StorageReport += New-object PSObject -Property @{
            "Drive" = $drive.DeviceID
            "DriveName" = $drive.volumeName
            "Freespace" = $Freespace
            "Size" = $Size
            "PercentFree" = $PercentFree + "%"
            "Status" = $Status
            }
        }
    }

$StorageReport | ft Drive,DriveName,Size,FreeSpace,PercentFree,status -AutoSize

The results look like this:

Drive DriveName           Size   Freespace PercentFree Status
----- ---------           ----   --------- ----------- ------
C:    System              99.66  17.28     17.34%      Amber 
H:    SQL Backup          160.00 83.35     52.10%      Green 
I:    MIM Svc SQL Logs    20.00  0.01      0.05%       Red   
J:    MIM Svc SQL Data    140.00 129.86    92.76%      Green 
K:    MIM Svc SQL TempDB  10.00  7.28      72.78%      Green 
L:    MIM Sync SQL Logs   20.00  19.86     99.30%      Green 
M:    MIM Sync SQL Data   80.00  79.12     98.91%      Green 
N:    MIM Sync SQL TempDB 10.00  9.80      98.07%      Green 
O:    MIM SPS SQL Logs    5.00   2.61      52.19%      Green 
P:    MIM SPS SQL Data    10.00  9.95      99.58%      Green 
Q:    MIM SPS SQL TempDB  10.00  9.93      99.37%      Green 
R:    MIM Stage SQL Logs  5.00   4.61      92.25%      Green 
S:    MIM Stage SQL Data  10.00  9.81      98.15%      Green 
T:    MIM Stage TempDB    10.00  9.95      99.49%      Green