Ajouter fonctionnalités active directory à PowerShell

Prérequis :
– Windows 2008R2 ou sup
– Réseau avec Controller de domaine

Le module nécessaire s’appelle : ActiveDirectory

Pour savoir si il est déjà installé

get-module -listavailable -Name "ActiveDirectory"

Sinon (ouvrir PowerShell en tant qu’admin si ce n’est déjà fait) et lancer la commande suivante :

Import-Module ServerManager
Add-WindowsFeature RSAT-AD-PowerShell

Ensuite possibilité de lancer des commandes du genre :

# Liste des groupes auquels appartient l'utilisateur toto@domain.com
Get-ADPrincipalGroupMembership 'toto' | select name

Taille et utilisation fichier de log

-- Création d'une table temporaire #TableTemporaire
CREATE TABLE #TableTemporaire (
        [DB Name][varchar](100) NOT NULL,
        [Logical Name][varchar](100) NOT NULL,
        [Physical Name][varchar](256) NOT NULL,
        [Logs Size Mo] float NOT NULL,
        [Logs UsedSpace Mo] float NOT NULL)

-- Insertion en boucle dans #TableTemporaire des informations
EXECUTE sp_MSforeachdb 'use ?; INSERT INTO #TableTemporaire SELECT DB_NAME(db_id()) AS db_name,s.name AS [Name], s.physical_name,s.size * CONVERT(float,8)/1024 AS [Size Mo],CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)/1024 AS [UsedSpace Mo]
FROM sys.master_files AS s
WHERE (s.type = 1 and s.database_id = db_id());'

     
-- Sélection dans #TableTemporaire
SELECT * FROM #TableTemporaire

DROP TABLE #TableTemporaire

Always on etat des réplicas

 SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, dr_state.database_id as database_id,
is_ag_replica_local = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE'
END ,
ag_replica_role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc
END,
dr_state.last_hardened_lsn, dr_state.last_hardened_time,
CASE WHEN EXISTS (SELECT dr_state_.last_hardened_lsn
FROM (( sys.availability_groups AS ag_ JOIN sys.availability_replicas AS ar_ ON ag_.group_id = ar_.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state_ ON ar_.replica_id = ar_state_.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state_ on ag_.group_id = dr_state_.group_id and dr_state_.replica_id = ar_state_.replica_id
WHERE ar_state_.role_desc = 'PRIMARY' AND dr_state.last_hardened_lsn = dr_state_.last_hardened_lsn)
THEN 0 ELSE
datediff(s,last_hardened_time, getdate())
END as 'seconds behind primary'
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id;

Dynamic Data Masking

Les fonctions de masking

– Default
Full masking according to the data types of the designated fields.

(FUNCTION = 'default()')

– Email
Masking method which exposes the first letter of an email address and the constant suffix « .com », in the form of an email address.

(FUNCTION = 'email()')

– Random
A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

(FUNCTION = 'random([start range], [end range])')

– Custom String
Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.

(FUNCTION = 'partial(prefix,[padding],suffix)') NULL

Créer MASK

CREATE TABLE Membership
(MemberID int IDENTITY PRIMARY KEY,
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
LastName varchar(100) NOT NULL,
Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

Ajouter un mask

ALTER TABLE dbo.PersonPhone
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE dbo.EmailAddress
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Employee
ALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()');

Identifier les colonnes masquées

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

Démasquer

GRANT UNMASK TO TestUser;
-- Removing the UNMASK permission
REVOKE UNMASK TO TestUser;

Supprimer le masque

ALTER TABLE Membership
ALTER COLUMN LastName DROP MASKED;

Lister les droits sur les bases

CREATE TABLE TempUserRole (
    ServerName VARCHAR(50),
    DatabaseName VARCHAR(50),
    DatabaseRoleName VARCHAR(50),
    DatabaseUserName VARCHAR(50)
)

exec sp_MSforeachdb
'INSERT INTO TempUserRole (ServerName,DatabaseName,DatabaseRoleName,DatabaseUserName)
 SELECT @@SERVERNAME AS ServerName,
        '
'?'' AS DatabaseName,
        DP1.name AS DatabaseRoleName,  
        isnull (DP2.name, '
'No members'') AS DatabaseUserName      
 FROM [?].sys.database_role_members AS DRM  
 RIGHT OUTER JOIN [?].sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN [?].sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = '
'R''
ORDER BY DP1.name'


Select *
From TempUserRole
-- Si utilisateur spécifique décommenter ligne suivante et choisir utilisateur
WHERE DatabaseUserName = 'EUROBAT\GR-USER-BI-DSI'

DROP TABLE TempUserRole;

Zipper le contenu d’un dossier en powershell

Le code suivant permet de générer un zip de tous les dossiers contenus dans un dossier racine.

function backupMe ($sourceFolder, $destinationZip) {
   [Reflection.Assembly]::LoadWithPartialName( "System.IO.Compression.FileSystem" )
   [System.IO.Compression.ZipFile]::CreateFromDirectory($sourceFolder, $destinationZip)
}

$dossierRacine = "C:\log"

$TAB_A_Compresser = Get-ChildItem $dossierRacine | Where-Object { $_.PSIsContainer } | Select-Object  Fullname

Foreach ($item in $TAB_A_Compresser) {  
    $sourceFolder = $item.fullname
    $destinationZip = $sourceFolder + ".zip"
    backupMe $sourceFolder $destinationZip
}