public/Get-VPASSQLAccounts.ps1
|
<#
.Synopsis GET SQL ACCOUNTS CREATED BY: Vadim Melamed, EMAIL: vpasmodule@gmail.com .DESCRIPTION USE THIS FUNCTION TO OUTPUT ALL ACCOUNTS INTO AN SQL TABLE .LINK https://vpasmodule.com/commands/Get-VPASSQLAccounts .PARAMETER token HashTable of data containing various pieces of login information (PVWA, LoginToken, HeaderType, etc). If -token is not passed, function will use last known hashtable generated by New-VPASToken .PARAMETER InputParameters HashTable of values containing the parameters required to make the API call .EXAMPLE $SQLAccounts = Get-VPASSQLAccounts .EXAMPLE $InputParameters = @{} $SQLAccounts = Get-VPASSQLAccounts -InputParameters $InputParameters .OUTPUTS $true if successful --- $false if failed #> function Get-VPASSQLAccounts{ [OutputType([bool])] [CmdletBinding(DefaultParameterSetName='Set1')] Param( [Parameter(Mandatory=$true,ParameterSetName='InputParameters',ValueFromPipelineByPropertyName=$true,HelpMessage="Hashtable of parameters required to make API call, refer to get-help -examples for valid inputs")] [hashtable]$InputParameters, [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)] [hashtable]$token ) Begin{ $tokenval,$sessionval,$PVWA,$Header,$ISPSS,$IdentityURL,$EnableTextRecorder,$AuditTimeStamp,$NoSSL,$VaultVersion,$HideWarnings,$AuthenticatedAs,$SubDomain,$EnableTroubleshooting = Get-VPASSession -token $token $CommandName = $MyInvocation.MyCommand.Name $log = Write-VPASTextRecorder -inputval $CommandName -token $token -LogType COMMAND } Process{ try{ if($PSCmdlet.ParameterSetName -eq "InputParameters"){ $KeyHash = @{ set1 = @{ AcceptableKeys = @() MandatoryKeys = @() } } $CheckSet = Test-VPASHashtableKeysHelper -InputHash $InputParameters -KeyHash $KeyHash if(!$CheckSet){ $log = Write-VPASTextRecorder -inputval "FAILED TO FIND TARGET PARAMETER SET" -token $token -LogType MISC Write-Verbose "FAILED TO FIND TARGET PARAMETER SET" Write-VPASOutput -str "FAILED TO FIND TARGET PARAMETER SET...VIEW EXAMPLES BELOW:" -type E $examples = Write-VPASExampleHelper -CommandName $CommandName return $false } else{ foreach($key in $InputParameters.Keys){ Set-Variable -Name $key -Value $InputParameters.$key } } } }catch{ $log = Write-VPASTextRecorder -inputval $_ -token $token -LogType ERROR $log = Write-VPASTextRecorder -inputval "REST API COMMAND RETURNED: FALSE" -token $token -LogType MISC Write-Verbose "FAILED TO RETRIEVE SQL DATA" Write-VPASOutput -str $_ -type E return $false } $curUser = $env:UserName $ConfigFile = "C:\Users\$curUser\AppData\Local\VPASModuleOutputs\SQL\SQLConfigFile.txt" try{ if(Test-Path -Path $ConfigFile){ Write-Verbose "FOUND SQL CONFIG FILE...PARSING DATA" } else{ Write-Verbose "FAILED TO FIND SQL CONFIG FILE...RERUN Set-VPASSQLConnectionDetails" Write-VPASOutput -str "FAILED TO FIND SQL CONFIG FILE...RERUN Set-VPASSQLConnectionDetails" -type E return $false } }catch{ Write-Verbose "FAILED TO FIND SQL CONFIG FILE...RERUN Set-VPASSQLConnectionDetails" Write-VPASOutput -str "FAILED TO FIND SQL CONFIG FILE...RERUN Set-VPASSQLConnectionDetails" -type E return $false } Write-Verbose "PARSING FILE CONTENTS" $SQLServerTemp = "" $SQLDatabaseTemp = "" $SQLUsernameTemp = "" $AAMTemp = "" $AppIDTemp = "" $FolderTemp = "" $SafeIDTemp = "" $ObjectNameTemp = "" $AIMServerTemp = "" $PasswordSDKTemp = "" $SQLPasswordTemp = "" $CertificateTPTemp = "" $AllLines = Get-Content -Path $ConfigFile foreach($line in $AllLines){ if($line -match "SQLServer="){ $SQLServerTemp = $line } if($line -match "SQLDatabase="){ $SQLDatabaseTemp = $line } if($line -match "SQLUsername="){ $SQLUsernameTemp = $line } if($line -match "AAM="){ $AAMTemp = $line } if($line -match "AppID="){ $AppIDTemp = $line } if($line -match "Folder="){ $FolderTemp = $line } if($line -match "SafeID="){ $SafeIDTemp = $line } if($line -match "ObjectName="){ $ObjectNameTemp = $line } if($line -match "AIMServer="){ $AIMServerTemp = $line } if($line -match "PasswordSDK="){ $PasswordSDKTemp = $line } if($line -match "SQLPassword="){ $SQLPasswordTemp = $line } if($line -match "CERTIFICATETP="){ $CertificateTPTemp = $line } } $AAMSplit = $AAMTemp -split "=" $AAM = $AAMSplit[1] Write-Verbose "AAM = $AAM" $SQLServerSplit = $SQLServerTemp -split "=" $SQLServer = $SQLServerSplit[1] Write-Verbose "SQLServer = $SQLServer" $SQLDatabaseSplit = $SQLDatabaseTemp -split "=" $SQLDatabase = $SQLDatabaseSplit[1] Write-Verbose "SQLDatabase = $SQLDatabase" $SQLUsernameSplit = $SQLUsernameTemp -split "=" $SQLUsername = $SQLUsernameSplit[1] Write-Verbose "SQLUsername = $SQLUsername" if($AAM -eq "CCP"){ #CCP $AppIDSplit = $AppIDTemp -split "=" $AppID = $AppIDSplit[1] Write-Verbose "AppID = $AppID" $FolderSplit = $FolderTemp -split "=" $Folder = $FolderSplit[1] Write-Verbose "Folder = $Folder" $SafeIDSplit = $SafeIDTemp -split "=" $SafeID = $SafeIDSplit[1] Write-Verbose "SafeID = $SafeID" $ObjectNameSplit = $ObjectNameTemp -split "=" $ObjectName = $ObjectNameSplit[1] Write-Verbose "ObjectName = $ObjectName" $AIMServerSplit = $AIMServerTemp -split "=" $AIMServer = $AIMServerSplit[1] Write-Verbose "AIMServer = $AIMServer" if([String]::IsNullOrEmpty($CertificateTPTemp)){ #DO NOTHING } else{ $CertificateTPSplit = $CertificateTPTemp -split "=" $CertificateTP = $CertificateTPSplit[1] Write-Verbose "CertificateTP = $CertificateTP" } try{ if($NoSSL){ $uri = "http://$AIMServer/AIMWebService/api/accounts?AppID=$AppID&Safe=$SafeID&Folder=$Folder&Object=$ObjectName" Write-Verbose "NO SSL ENABLED, USING HTTP INSTEAD OF HTTPS" } else{ $uri = "https://$AIMServer/AIMWebService/api/accounts?AppID=$AppID&Safe=$SafeID&Folder=$Folder&Object=$ObjectName" Write-Verbose "SSL ENABLED BY DEFAULT, USING HTTPS" } if([String]::IsNullOrEmpty($CertificateTP)){ $CCPResult = Invoke-RestMethod -Uri $uri } else{ $CCPResult = Invoke-RestMethod -Uri $uri -CertificateThumbprint $CertificateTP } $Secret = $CCPResult.Content if($Secret){ write-verbose "SECRET RETRIEVED SUCCESSFULLY" } else{ Write-VPASOutput -str "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CCP FUNCTIONALITY" -type E return $false } }catch{ Write-VPASOutput -str "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CCP FUNCTIONALITY" -type E Write-VPASOutput -str $_ -type E return $false } } elseif($AAM -eq "CP"){ #CP $AppIDSplit = $AppIDTemp -split "=" $AppID = $AppIDSplit[1] Write-Verbose "AppID = $AppID" $FolderSplit = $FolderTemp -split "=" $Folder = $FolderSplit[1] Write-Verbose "Folder = $Folder" $SafeIDSplit = $SafeIDTemp -split "=" $SafeID = $SafeIDSplit[1] Write-Verbose "SafeID = $SafeID" $ObjectNameSplit = $ObjectNameTemp -split "=" $ObjectName = $ObjectNameSplit[1] Write-Verbose "ObjectName = $ObjectName" $PasswordSDKSplit = $PasswordSDKTemp -split "=" $PasswordSDK = $PasswordSDKSplit[1] Write-Verbose "PasswordSDK = $PasswordSDK" try{ $Secret = & "$PasswordSDK" GetPassword /p AppDescs.AppID=$AppID /p Query="Safe=$SafeID;Folder=$Folder;Object=$ObjectName" /o Password if($Secret){ write-verbose "RETRIEVED SECRET SUCCESSFULLY" } else{ Write-VPASOutput -str "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CP FUNCTIONALITY" -type E return $false } }catch{ Write-VPASOutput -str "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CP FUNCTIONALITY" -type E Write-VPASOutput -str $_ -type E return $false } } else{ #NONE $SQLPasswordSplit = $SQLPasswordTemp -split "=" $SQLPassword = $SQLPasswordSplit[1] $SecureString = ConvertTo-SecureString -String $SQLPassword $Pointer = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString) $Secret = [Runtime.InteropServices.Marshal]::PtrToStringAuto($Pointer) } try{ import-module sqlserver -ErrorAction Stop }catch{ Write-VPASOutput -str "FAILED TO LOAD SQLServer MODULE..." -type E Write-VPASOutput -str $_ -type E Write-VPASOutput -str "FAILED TO FIND SQLServer MODULE IN THE FOLLOWING DIRECTORIES:" -type E $str = $env:PSModulePath -split ";" foreach($strsplit in $str){ Write-VPASOutput -str $strsplit -type E } Write-VPASOutput -str "DOWNLOAD THE MODULE BY TYPING IN 'Install-Module -Name SqlServer' THEN RERUN Set-VPASSQLConnectionDetails" -type E Write-VPASOutput -str "YOU CAN ALSO VIEW THIS LINK FOR MORE INFORMATION: 'https://www.powershellgallery.com/packages/SqlServer/21.1.18256'" -type E Write-VPASOutput -str "PROCESS TERMINATED" -type E return $false } try{ $output = @() $result = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query "SELECT DB_NAME()" -Username $SQLUsername -Password $Secret if($result.Column1 -eq $SQLDatabase){ write-verbose "SQL CONNECTIVITY SUCCESSFUL" } else{ Write-VPASOutput -str "FAILED TO CONNECT TO SQL DATABASE...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT" -type E return $false } }catch{ Write-VPASOutput -str "FAILED TO CONNECT TO SQL DATABASE...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT" -type E Write-VPASOutput -str $_ -type E return $false } #DROP $TableName = "Vpas_Account_Inventory" try{ $query = "DROP TABLE $TableName" $result = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $query -Username $SQLUsername -Password $Secret -ErrorAction Stop Write-Verbose "$TableName DELETED SUCCESSFULLY" }catch{ Write-Verbose "$TableName DOES NOT EXIST" } #CREATE try{ $query = "CREATE TABLE $TableName ( AcctID varchar(255), AcctName varchar(255), AcctAddress varchar(255), AcctUserName varchar(255), AcctPlatformID varchar(255), AcctSafeName varchar(255), AcctSecretType varchar(255), AcctAutomaticManagement varchar(255), AcctManualManagementReason varchar(255), AcctStatus varchar(255), AcctLastModifiedTime varchar(255), AcctCreationTime varchar(255), AcctOptionalProp varchar(4255) ); " $result = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $query -Username $SQLUsername -Password $Secret Write-Verbose "$TableName CREATED SUCCESSFULLY" }catch{ Write-Verbose "FAILED TO CREATE $TableName" Write-VPASOutput -str "FAILED TO CREATE $TableName" -type E Write-VPASOutput -str $_ -type E } #START QUERYING $AllAccounts = Get-VPASAllAccounts -token $token foreach($acct in $AllAccounts.value){ $AcctID = $acct.id $AcctName = $acct.name $AcctAddress = $acct.address $AcctUserName = $acct.username $AcctPlatformID = $acct.platformId $AcctSafeName = $acct.safeName $AcctSecretType = $acct.secretType $AcctAutomaticManagement = $acct.secretManagement.automaticManagementEnabled $AcctManualManagementReason = $acct.secretManagement.manualManagementReason $AcctStatus = $acct.secretManagement.status $AcctLastModifiedTime = $acct.secretManagement.lastModifiedTime $AcctCreationTime = $acct.createdTime $AcctOptionalProp = "" $props = $acct.platformAccountProperties $keys = $($props | Get-Member -MemberType *Property).Name foreach($key in $keys){ $val = $acct.platformAccountProperties.$key $AcctOptionalProp += "$key=$val;" } if([String]::IsNullOrEmpty($AcctID)){$AcctID = "NULL"} if([String]::IsNullOrEmpty($AcctName)){$AcctName = "NULL"} if([String]::IsNullOrEmpty($AcctAddress)){$AcctAddress = "NULL"} if([String]::IsNullOrEmpty($AcctUserName)){$AcctUserName = "NULL"} if([String]::IsNullOrEmpty($AcctPlatformID)){$AcctPlatformID = "NULL"} if([String]::IsNullOrEmpty($AcctSafeName)){$AcctSafeName = "NULL"} if([String]::IsNullOrEmpty($AcctSecretType)){$AcctSecretType = "NULL"} if([String]::IsNullOrEmpty($AcctAutomaticManagement)){$AcctAutomaticManagement = "NULL"} if([String]::IsNullOrEmpty($AcctManualManagementReason)){$AcctManualManagementReason = "NULL"} if([String]::IsNullOrEmpty($AcctStatus)){$AcctStatus = "NO STATUS"} if([String]::IsNullOrEmpty($AcctLastModifiedTime)){$AcctLastModifiedTime = "NULL"} if([String]::IsNullOrEmpty($AcctCreationTime)){$AcctCreationTime = "NULL"} if([String]::IsNullOrEmpty($AcctOptionalProp)){$AcctOptionalProp = "NULL"} $AcctID = $AcctID -replace "'","''" $AcctName = $AcctName -replace "'","''" $AcctAddress = $AcctAddress -replace "'","''" $AcctUserName = $AcctUserName -replace "'","''" $AcctPlatformID = $AcctPlatformID -replace "'","''" $AcctSafeName = $AcctSafeName -replace "'","''" $AcctSecretType = $AcctSecretType -replace "'","''" $AcctAutomaticManagement = $AcctAutomaticManagement -replace "'","''" $AcctManualManagementReason = $AcctManualManagementReason -replace "'","''" $AcctStatus = $AcctStatus -replace "'","''" $AcctLastModifiedTime = $AcctLastModifiedTime -replace "'","''" $AcctCreationTime = $AcctCreationTime -replace "'","''" $AcctOptionalProp = $AcctOptionalProp -replace "'","''" try{ $query = "INSERT INTO $TableName ( AcctID, AcctName, AcctAddress, AcctUserName, AcctPlatformID, AcctSafeName, AcctSecretType, AcctAutomaticManagement, AcctManualManagementReason, AcctStatus, AcctLastModifiedTime, AcctCreationTime, AcctOptionalProp ) VALUES ( '$AcctID','$AcctName','$AcctAddress','$AcctUserName','$AcctPlatformID','$AcctSafeName','$AcctSecretType','$AcctAutomaticManagement','$AcctManualManagementReason','$AcctStatus','$AcctLastModifiedTime','$AcctCreationTime','$AcctOptionalProp' );" $UpdateRec = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $query -Username $SQLUsername -Password $Secret Write-Verbose "ADDED RECORD INTO $TableName FOR ACCOUNTID: $AcctID || ACCOUNT NAME: $AcctName" }catch{ Write-Verbose "FAILED TO ADD RECORD INTO $TableName FOR ACCOUNTID: $AcctID || ACCOUNT NAME: $AcctName" Write-VPASOutput -str "FAILED TO ADD RECORD INTO $TableName FOR ACCOUNTID: $AcctID || ACCOUNT NAME: $AcctName" -type E Write-VPASOutput -str $_ -type E } } return $true } End{ $log = Write-VPASTextRecorder -inputval $CommandName -token $token -LogType DIVIDER } } |