infamousjoeg
3/20/2017 - 3:56 PM

v0.2b - Convert DNA Results to PUU passwords.csv

v0.2b - Convert DNA Results to PUU passwords.csv

#####################################################
#
# DNA2PUU.ps1
#
# Converts DNA Results to passwords.csv format for
# Password Upload Utility Bulk Upload
#
# Version 0.2b
#
# Joe Garcia, CyberArk Software
# Created: 03/21/2017
# Modified: 03/30/2017
#
# Change Log:
# 0.1b - Initial release
# 0.2b - Fixed Password value to "NO_VALUE"
#        instead of ""; added default values
#
#####################################################

###### DNA_Raw.csv Mapping
#
# Machine Name      components.joe-garcia.local
# Account Name      username
# Account Type      Local or Domain: joe-garcia.local
# Account Category  Privileged Personal, Privileged Shared
# Account State     Enabled or Disabled (Password)

###### passwords.csv Mapping
#
# Password_name     Object Name
# CPMUser           PasswordManager
# Safe              Safe Name
# Folder            Root
# Password          NO_VALUE
# DeviceType        Operating System
# PolicyID          PlatformID
# Address           Address (IP, DNS, FQDN)
# UserName          Username

# Import CSVs
$DNARawCSVImport = Import-Csv "DNA_Raw.csv"
# Declare Output Filename
$now = Get-Date -Format "yyyyMMdd-hhmmss"
$passwordsCSV = "passwords_DNA2PUU_" + $now + ".csv"

# Declare user-defined variables
$CPMUser = Read-Host "Which CPMUser would you like to assign? [PasswordManager]"
if ($CPMUser -eq "") {$CPMUser = "PasswordManager"}
$Safe = Read-Host "Which safe would you like to Bulk Upload to?"
$Folder = "Root"
$DeviceType = "Operating System"
#$PolicyID = "Which Platform ID would you like to assign? [WinDomain]"
#if ($PolicyID -eq "") {$PolicyID = "WinDomain"}

$DNARawRowCount = $DNARawCSVImport.Count
$Counter = 0

#$headers = "Password_name,CPMUser,Safe,Folder,Password,DeviceType,PolicyID,Address,UserName"
#$headers >> $passwordsCSV

# Step row-by-row through DNA_Raw.csv
foreach ($row in $DNARawCSVImport) {
    $Counter++
    Write-Host "Processing Row #"$Counter" of "$DNARawRowCount"..."

    # Declare variables by column name
    $MachineName = $row."Machine Name"
    $AccountName = $row."Account Name"
    $AccountType = $row."Account Type"
    $AccountCategory = $row."Account Category"
    $AccountState = $row."Account State"

    #Write-Host $MachineName $AccountName $AccountType $AccountCategory $AccountState

    if ($AccountState -eq "Enabled") {
        if ($AccountCategory -eq "Privileged Personal" -or $AccountCategory -eq "Privileged Shared") {
            if ($AccountType -eq "Local") {
                # Declare configured variables for passwords_DNA2PUU.csv
                $Password_name = $DeviceType + "-WinServerLocal-" + $MachineName + "-" + $AccountName

                # Build PSObject for CSV Export
                $ExportRow = New-Object -TypeName psobject
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Password_name" -Value $Password_name
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "CPMUser" -Value $CPMUser
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Safe" -Value $Safe
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Folder" -Value $Folder
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Password" -Value "NO_VALUE"
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "DeviceType" -Value $DeviceType
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "PolicyID" -Value "WinServerLocal"
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Address" -Value $MachineName
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "UserName" -Value $AccountName
                $ExportRow | Select-Object Password_name,CPMUser,Safe,Folder,Password,DeviceType,PolicyID,Address,UserName | Export-Csv -Force -NoTypeInformation -Append $passwordsCSV
            }
            if ($AccountType -match "Domain:") {
                # Grab domain name from AccountType column of DNA_Raw.csv
                $AccountTypeSplit = $AccountType.Split(" ")
                $AccountTypeDomain = $AccountTypeSplit[1]

                # Declare configured variables for passwords_DNA2PUU.csv
                $Password_name = $DeviceType + "-WinDomain-" + $AccountTypeDomain + "-" + $AccountName

                # Build PSObject for CSV Export
                $ExportRow = New-Object -TypeName psobject
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Password_name" -Value $Password_name
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "CPMUser" -Value $CPMUser
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Safe" -Value $Safe
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Folder" -Value $Folder
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Password" -Value "NO_VALUE"
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "DeviceType" -Value $DeviceType
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "PolicyID" -Value "WinDomain"
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "Address" -Value $AccountTypeDomain
                    $ExportRow | Add-Member -MemberType NoteProperty -Name "UserName" -Value $AccountName
                $ExportRow | Select-Object Password_name,CPMUser,Safe,Folder,Password,DeviceType,PolicyID,Address,UserName | Export-Csv -Force -NoTypeInformation -Append $passwordsCSV
            }
        }
    }
}