AWSTemplateFormatVersion: 2010-09-09 Description: 'CloudFormation template to deploy all the required resources for SQL Server License Tracking Solution within an AWS Organization management account' Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: 'Inventory collection' Parameters: - InventoryBucketName - OrganizationId - Label: default: 'Automation details' Parameters: - IsDelegatedAdministrator - AdministratorAccountId - Label: default: 'Targets' Parameters: - TargetOUs - TargetRegions - TargetKey - TargetValues - MaxConcurrency - MaxErrors ParameterLabels: IsDelegatedAdministrator: default: 'Is this a delegated administrator account' AdministratorAccountId: default: 'Administrator Account Id' InventoryBucketName: default: 'InventoryBucketName' TargetOUs: default: 'Target Organizational Unit IDs' TargetRegions: default: 'Target Regions' TargetKey: default: 'Target Key' TargetValues: default: 'Target Values' OrganizationId: default: 'Organization Id' MaxConcurrency: default: 'Maximum Concurrency' MaxErrors: default: 'Maximum Errors' Parameters: IsDelegatedAdministrator: Type: String AllowedValues: - 'true' - 'false' Description: Specify if this solution is being deployed in a delegated adminstrator account. With this option you no longer need to be logged into the AWS Organizations management account to administer this solution. AdministratorAccountId: Description: (Required) AWS Organization's Management or Delegated Administrator account ID. Type: String AllowedPattern : '^\d{12}$' TargetKey: Type: String Description: Specify which instances have to be targeted for this solution. Allowed values - ParameterValues, ResourceGroup or begin with tag:(default), AWS::EC2::Instance, InstanceIds, instanceids. To target all instances use . Refer https://docs.aws.amazon.com/systems-manager/latest/APIReference/API_Target.html for more details. Default: 'tag:SQLServerLTS-ManagedInstance' TargetValues: Type: String Description: Specify the values of the target keys specified above, default is true. To target all instances use *. Refer https://docs.aws.amazon.com/systems-manager/latest/APIReference/API_Target.html for more details. Default: 'true' OrganizationId: Description: (Required) AWS Organization ID. Type: String AllowedPattern: '^[o]+(-[a-z0-9]+)*$' ConstraintDescription: Provide a valid Organization ID (for eg. o-abc5drtefg) TargetOUs: Description: (Required) Specify AWS organizational unit IDs within your AWS Organization whose accounts have the target instances (e.g., ou-abcd-1qwert43, ou-klhd-1fgdt78). Type: CommaDelimitedList AllowedPattern : '.+' TargetRegions: Type: CommaDelimitedList Description: Specify all AWS Regions which needs to be managed by this solution MaxConcurrency: Type: String Description: The maximum number of targets allowed to run this task in parallel. You can specify a number, such as 10, or a percentage, such as 10%. The default value is 4. Default: '4' MaxErrors: Type: String Description: The number of errors that are allowed before the system stops running the automation on additional targets. You can specify a number, such as 10, or a percentage, such as 10%. The default value is 4. Default: '4' InventoryBucketName: Description: (Required) Name for the S3 bucket that will be used to aggregate the Inventory data. Type: String AllowedPattern: '^[a-z]+(-[a-z]+)*$' ConstraintDescription: Provide a valid name for S3 bucket Conditions: IsGovCloud: !Equals [!Ref AWS::Partition, aws-us-gov] HasTargetOUs: !Not [!Equals [!Join ["", !Ref TargetOUs], '']] IsDelegatedAdministrator: !Equals [!Ref IsDelegatedAdministrator,'true'] Resources: InventoryS3Bucket: Type: AWS::S3::Bucket DeletionPolicy: Delete Properties: BucketName: !Ref InventoryBucketName PublicAccessBlockConfiguration: BlockPublicAcls: true BlockPublicPolicy: true IgnorePublicAcls: true RestrictPublicBuckets: true InventoryS3BucketPolicy: Type: AWS::S3::BucketPolicy Properties: Bucket: !Ref InventoryS3Bucket PolicyDocument: Version: "2012-10-17" Statement: - Sid: "SSMBucketPermissionsCheck" Effect: "Allow" Principal: Service: "ssm.amazonaws.com" Action: "s3:GetBucketAcl" Resource: !Sub "arn:${AWS::Partition}:s3:::${InventoryS3Bucket}" - Sid: "SSMBucketDelivery" Effect: "Allow" Principal: Service: "ssm.amazonaws.com" Action: "s3:PutObject" Resource: !Sub "arn:${AWS::Partition}:s3:::${InventoryS3Bucket}/*/accountid=*/*" Condition: StringEquals: "s3:RequestObjectTag/OrgId": !Sub - "${OrgID}" - OrgID: !Ref OrganizationId "s3:x-amz-acl": "bucket-owner-full-control" - Sid: "SSMBucketDeliveryTagging" Effect: "Allow" Principal: Service: "ssm.amazonaws.com" Action: "s3:PutObjectTagging" Resource: !Sub "arn:${AWS::Partition}:s3:::${InventoryS3Bucket}/*/accountid=*/*" DependsOn: InventoryS3Bucket AutomationAdministrationRole: Type: "AWS::IAM::Role" Properties: Path: "/" RoleName: "SQLServerLTS-SystemsManager-AutomationAdministrationRole" AssumeRolePolicyDocument: >- {"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":"ssm.amazonaws.com"},"Action":"sts:AssumeRole"}]} MaxSessionDuration: 3600 ManagedPolicyArns: - !Sub 'arn:${AWS::Partition}:iam::aws:policy/service-role/AmazonSSMAutomationRole' Description: "SQLServerLTS-SystemsManager-AutomationAdministrationRole" Policies: - PolicyName: SQLServerLTS-SystemsManager-AutomationAdministrationPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - 'organizations:ListAccountsForParent' - 'tag:GetResources' - 'ec2:DescribeInstances' - 'ec2messages:GetEndpoint' - 'ec2messages:AcknowledgeMessage' - 'ec2messages:GetMessages' - 'ec2messages:FailMessage' - 'ec2messages:SendReply' - 'cloudformation:*' - 'resource-groups:ListGroupResources' - 'license-manager:ListLicenseConfigurations' - 'license-manager:UpdateLicenseSpecificationsForResource' Resource: '*' - Effect: Allow Action: - 'sts:AssumeRole' Resource: !Sub 'arn:${AWS::Partition}:iam::*:role/SQLServerLTS-SystemsManager-AutomationExecutionRole' - Effect: Allow Action: - 'iam:PassRole' Resource: !Sub 'arn:${AWS::Partition}:iam::${AWS::AccountId}:role/SQLServerLTS-SystemsManager-AutomationAdministrationRole' StackSetAdministrationRole: Type: 'AWS::IAM::Role' Properties: Path: / RoleName: SQLServerLTS-CloudFormation-StackSetAdministrationRole AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: Service: cloudformation.amazonaws.com Action: - sts:AssumeRole Policies: - PolicyName: AdministrationPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - sts:AssumeRole Resource: - "arn:*:iam::*:role/SQLServerLTS-CloudFormation-StackSetExecutionRole" Description: SQLServerLTS-CloudFormation-StackSetAdministrationRole to enable use of CloudFormation Stacksets StackSetExecutionRole: Type: 'AWS::IAM::Role' Properties: Path: / RoleName: SQLServerLTS-CloudFormation-StackSetExecutionRole AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: AWS: - !Sub "arn:${AWS::Partition}:iam::${AdministratorAccountId}:root" Action: - sts:AssumeRole Policies: - PolicyName: ExecutionPolicy PolicyDocument: Version: 2012-10-17 Statement: - Sid: Sid0 Effect: Allow Action: - 'iam:CreateRole' - 'iam:AttachRolePolicy' - 'iam:PutRolePolicy' - 'iam:PassRole' - 'iam:DetachRolePolicy' - 'iam:DeleteRolePolicy' - 'iam:DeleteRole' Resource: - !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:role/SQLServerLTS-SystemsManager-AutomationAdministrationRole" - !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:role/SQLServerLTS-SystemsManager-AutomationAdministrationRole" - !Sub "arn:${AWS::Partition}:iam::${AWS::AccountId}:role/SQLServerLTS-SystemsManager-AutomationExecutionRole" - Sid: Sid1 Effect: Allow Action: - 'ssm:CreateDocument' - 'ssm:DeleteDocument' Resource: - !Sub "arn:${AWS::Partition}:ssm:*:${AWS::AccountId}:document/SQLServerLTS-DeleteInventory" - !Sub "arn:${AWS::Partition}:ssm:*:${AWS::AccountId}:document/SQLServerLTS-ManageLicenceUtilization" - Sid: Sid2 Effect: Allow Action: - 'iam:GetRolePolicy' - 'iam:GetRole' - 'ssm:ListTagsForResource' - 'ssm:DescribeDocument' Resource: '*' - Sid: Sid3 Effect: Allow Action: - 'sns:*' - 'cloudformation:*' Resource: '*' Description: SQLServerLTS-CloudFormation-StackSetExecutionRole to enable use of CloudFormation Stacksets AutomationPermissionsStackSet: Type: AWS::CloudFormation::StackSet Properties: AutoDeployment: Enabled: true RetainStacksOnAccountRemoval: false CallAs: !If [IsDelegatedAdministrator,DELEGATED_ADMIN,SELF] StackSetName: SQLServerLTS-SystemsManager-AutomationPermissionsStackSet Parameters: - ParameterKey: AccountId ParameterValue: !Ref AdministratorAccountId PermissionModel: SERVICE_MANAGED Capabilities: - CAPABILITY_IAM - CAPABILITY_NAMED_IAM StackInstancesGroup: - Regions: - !If [IsGovCloud, us-gov-west-1, us-east-1] DeploymentTargets: OrganizationalUnitIds: !If [HasTargetOUs, !Ref TargetOUs, !Ref "AWS::NoValue"] TemplateBody: | Parameters: AccountId: Description: (Required) Account ID where the SQL Server LTS solution is being deployed (AWS Organization management or delegated admin account). Type: String Default: '123456789012' Resources: AutomationExecutionRole: Type: 'AWS::IAM::Role' Properties: RoleName: SQLServerLTS-SystemsManager-AutomationExecutionRole AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: AWS: !Ref AccountId Action: - 'sts:AssumeRole' - Effect: Allow Principal: Service: ssm.amazonaws.com Action: - 'sts:AssumeRole' ManagedPolicyArns: - !Sub 'arn:${AWS::Partition}:iam::aws:policy/service-role/AmazonSSMAutomationRole' Path: / Policies: - PolicyName: ExecutionPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - 'resource-groups:ListGroupResources' - 'tag:GetResources' - 'ec2:DescribeInstances' - 'ec2:DescribeTags' - 'ec2messages:GetEndpoint' - 'ec2messages:FailMessage' - 'ec2messages:AcknowledgeMessage' - 'ec2messages:SendReply' - 'ec2messages:GetMessages' - 'license-manager:ListLicenseSpecificationsForResource' - 'license-manager:ListLicenseConfigurations' - 'license-manager:UpdateLicenseSpecificationsForResource' - 'license-manager:GetLicenseConfiguration' Resource: '*' - Effect: Allow Action: - 'iam:PassRole' Resource: !Sub >- arn:${AWS::Partition}:iam::${AWS::AccountId}:role/SQLServerLTS-SystemsManager-AutomationExecutionRole SQLServerLTSOrchestrate: Type: "AWS::SSM::Document" Properties: Name: "SQLServerLTS-Orchestrate" Content: !Sub | { "description": "Document to orchestrate the the SQL database License Tracking Solution", "schemaVersion": "0.3", "assumeRole": "{{ AutomationAssumeRole }}", "parameters": { "DeploymentTargets": { "type": "StringList", "description": "Specify the AWS Accounts/OUs where SQL databases are deployed" }, "TargetRegions": { "type": "StringList", "description": "Specify the AWS Regions where SQL databases are deployed" }, "TargetKey": { "type": "String", "default": "tag:SQLServerLTS-ManagedInstance", "description": "(Optional) Specify the instances you want to target using Resource Groups, tags (use tag: format) or all instances with InstanceIds. Refer https://docs.aws.amazon.com/systems-manager/latest/APIReference/API_StartAutomationExecution.html for more details." }, "TargetValues": { "type": "String", "default": "true", "description": "(Optional) Specify the values you want to target like tag value or * for all instances. Refer https://docs.aws.amazon.com/systems-manager/latest/APIReference/API_StartAutomationExecution.html for more details." }, "AutomationAssumeRole": { "type": "String", "description": "The IAM role required to execute this document (select SQLServerLTS-SystemsManagerAutomationAdministrationRole from the drop-down if you deployed this solution using CloudFormation)" }, "MaxConcurrency": { "type": "String", "default": "4" }, "MaxErrors": { "type": "String", "default": "4" } }, "mainSteps": [ { "name": "invokeSQLServerLTS_DeleteInventory", "description": "This step invokes the invokeSQLServerLTS_DeleteInventory Automation", "action": "aws:executeAutomation", "inputs": { "RuntimeParameters": { "AutomationAssumeRole": [ "{{ AutomationAssumeRole }}" ] }, "DocumentName": "arn:${AWS::Partition}:ssm:{{global:REGION}}:${AWS::AccountId}:document/SQLServerLTS-DeleteInventory", "TargetLocations": [ { "Accounts": "{{ DeploymentTargets }}", "ExecutionRoleName": "SQLServerLTS-SystemsManager-AutomationExecutionRole", "Regions": "{{ TargetRegions }}", "TargetLocationMaxConcurrency": "{{ MaxConcurrency }}", "TargetLocationMaxErrors": "{{ MaxErrors }}" } ] }, "isCritical": true, "onFailure": "Abort", "nextStep": "invokeSQLServerLTS_ManageLicenceUtilizationAutomation" }, { "name": "invokeSQLServerLTS_ManageLicenceUtilizationAutomation", "description": "This step invokes the invokeSQLServerLTS_ManageLicenceUtilization Automation", "action": "aws:executeAutomation", "inputs": { "RuntimeParameters": { "AutomationAssumeRole": [ "{{ AutomationAssumeRole }}" ] }, "DocumentName": "arn:${AWS::Partition}:ssm:{{global:REGION}}:${AWS::AccountId}:document/SQLServerLTS-ManageLicenceUtilization", "TargetLocations": [ { "Accounts": "{{ DeploymentTargets }}", "ExecutionRoleName": "SQLServerLTS-SystemsManager-AutomationExecutionRole", "Regions": "{{ TargetRegions }}", "TargetLocationMaxConcurrency": "{{ MaxConcurrency }}", "TargetLocationMaxErrors": "{{ MaxErrors }}" } ], "TargetParameterName": "InstanceId", "Targets": [ { "Key": "{{ TargetKey }}", "Values": [ "{{ TargetValues }}" ] } ] }, "isCritical": true, "onFailure": "Abort", "isEnd": true } ] } DocumentType: "Automation" TargetType: "/AWS::EC2::Instance" SQLServerLTSDeleteInventory: Type: "AWS::SSM::Document" Properties: Name: "SQLServerLTS-DeleteInventory" Content: !Sub | { "description": "Document to delete Inventory data for the SQL Server License Tracking Solution", "schemaVersion": "0.3", "assumeRole": "{{ AutomationAssumeRole }}", "parameters": { "AutomationAssumeRole": { "type": "String", "description": "The IAM role required to execute this document (select SQLServerLTS-SystemsManagerAutomationAdministrationRole from the drop-down if you deployed this solution using CloudFormation)" } }, "mainSteps": [ { "name": "deleteCustomInventory", "description": "This clean up step deletes all the metadata associated with the custom inventory type created for MSSQL [Custom:SQLServer] on all the EC2 instances.", "action": "aws:executeScript", "inputs": { "Runtime": "python3.7", "Handler": "script_handler", "Script": "import sys\nimport boto3\nimport json\nimport botocore\nimport time\n\ndef script_handler(events, context):\n client = boto3.client('ssm')\n \n get_inventory_response = client.get_inventory_schema(\n TypeName='Custom:SQLServer'\n )\n \n print(get_inventory_response)\n \n if get_inventory_response['Schemas']:\n delete_inventory_response = client.delete_inventory(\n TypeName='Custom:SQLServer',\n SchemaDeleteOption='DeleteSchema'\n )\n \n deletion_id = delete_inventory_response['DeletionId']\n \n wait_loop = True\n while wait_loop == True:\n describe_inventory_deletions_response = client.describe_inventory_deletions(\n DeletionId=deletion_id\n )\n if describe_inventory_deletions_response['InventoryDeletions']:\n if describe_inventory_deletions_response['InventoryDeletions'][0]['LastStatus'] == \"Complete\":\n status = \"Custom:SQLServer schema has been deleted successfuly. DeletionID: \"+deletion_id\n wait_loop = False\n break\n else:\n time.sleep(60)\n\n else:\n status = \"Custom:SQLServer type doesn't exist in the Inventory so continuing to the next step.\"\n \n return {'message': status}" }, "outputs": [ { "Name": "result", "Selector": "$.Payload.message", "Type": "String" } ], "isCritical": true, "onFailure": "Abort", "isEnd": true } ] } DocumentType: "Automation" TargetType: "/AWS::EC2::Instance" SQLServerLTSTrackLicenceUtilization: Type: "AWS::SSM::Document" Properties: Name: "SQLServerLTS-ManageLicenceUtilization" Content: !Sub | { "description": "Document to discover and manage SQL Server license utilization for the SQL Server License Tracking Solution", "schemaVersion": "0.3", "assumeRole": "{{ AutomationAssumeRole }}", "parameters": { "InstanceId": { "description": "(Required) Use * to target all instances in this account or you can specify an individual instance ID", "type": "StringList", "default": [ "*" ], "allowedPattern": "\\*|^[m]{0,1}i-[a-z0-9]{8,17}" }, "AutomationAssumeRole": { "type": "String", "description": "The IAM role required to execute this document" } }, "mainSteps": [ { "name": "removeLicenseConfigDataForInstance", "action": "aws:executeScript", "description": "This step performs a clean up task. Removing any association to the license configuration if they exist. This gives us an opportunity to rehydrate the latest data to License Manager in case changes have been made", "inputs": { "Runtime": "python3.8", "Handler": "script_handler", "Script": "import boto3\nimport json\nimport botocore\n\ndef script_handler(events, context):\n\n lmClient = boto3.client('license-manager')\n currentLicenseList = {}\n listResponse = lmClient.list_license_configurations()\n \n if listResponse['LicenseConfigurations']:\n for config in listResponse['LicenseConfigurations']:\n if config['Name']:\n currentLicenseList[config['Name']] = config['LicenseConfigurationArn']\n \n accountId = events['accountId'] \n region = events['region'] \n instanceId = events['instanceId']\n sqlServerEELicenseConfig = currentLicenseList['SQLServerEELicenseConfiguration'] \n sqlServerSTDLicenseConfig = currentLicenseList['SQLServerSTDLicenseConfiguration']\n sqlServerEXPLicenseConfig = currentLicenseList['SQLServerEXPLicenseConfiguration']\n sqlServerWEBLicenseConfig = currentLicenseList['SQLServerWEBLicenseConfiguration']\n sqlServerDEVLicenseConfig = currentLicenseList['SQLServerDEVLicenseConfiguration']\n\n ec2ARN = f\"arn:aws:ec2:{region}:{accountId}:instance/{instanceId}\"\n licenseConfigARN = ''\n numberOfUpdates = 0\n \n try:\n lmResponse = lmClient.list_license_specifications_for_resource(\n ResourceArn=ec2ARN,\n MaxResults=10\n )\n \n if lmResponse['LicenseSpecifications']:\n for spec in lmResponse['LicenseSpecifications']:\n if spec['LicenseConfigurationArn']:\n if (sqlServerEELicenseConfig and spec['LicenseConfigurationArn'] == sqlServerEELicenseConfig):\n licenseConfigARN = sqlServerEELicenseConfig\n \n elif (sqlServerSTDLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerSTDLicenseConfig):\n licenseConfigARN = sqlServerSTDLicenseConfig\n \n elif (sqlServerEXPLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerEXPLicenseConfig):\n licenseConfigARN = sqlServerEXPLicenseConfig\n \n elif (sqlServerWEBLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerWEBLicenseConfig):\n licenseConfigARN = sqlServerWEBLicenseConfig\n \n elif (sqlServerDEVLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerDEVLicenseConfig):\n licenseConfigARN = sqlServerDEVLicenseConfig\n \n if licenseConfigARN:\n updateResponse = lmClient.update_license_specifications_for_resource(\n ResourceArn=ec2ARN,\n RemoveLicenseSpecifications=[\n {\n 'LicenseConfigurationArn': licenseConfigARN\n }]);\n numberOfUpdates = numberOfUpdates + 1\n licenseConfigARN = '' \n \n except botocore.exceptions.ClientError as error:\n if 'Resource ARN is invalid' in error.response['Error']['Message']:\n print('EC2 does not exist for this resource. Skipping this step') \n \n return {'message': f\"Removed {ec2ARN} from {numberOfUpdates} License Configuration(s)\" }", "InputPayload": { "instanceId": "{{ InstanceId }}", "accountId": "{{global:ACCOUNT_ID}}", "region": "{{global:REGION}}" } }, "nextStep": "assertInstanceEligibility", "onFailure": "Abort" }, { "name": "assertInstanceEligibility", "action": "aws:executeScript", "description": "This step checks if the EC2 instance is eligible for this document. The two criteria for this are - (i) is a SSM managed instance and is currently online, (ii) is running on a Windows operating system", "inputs": { "Runtime": "python3.7", "Handler": "script_handler", "Script": "import sys\nimport boto3\nimport json\nimport botocore\n\ndef script_handler(events, context):\n instanceId = events['instanceId']\n\n client = boto3.client('ssm')\n \n response = client.describe_instance_information(\n Filters=[\n {\n 'Key': 'InstanceIds',\n 'Values': [\n instanceId[0],\n ]\n },\n ]\n )\n \n if response[\"InstanceInformationList\"]:\n if response[\"InstanceInformationList\"][0]['PingStatus'] == 'Online' and response[\"InstanceInformationList\"][0]['PlatformType'] == 'Windows':\n status = f\"{instanceId[0]} is eligible for this automation execution\"\n else:\n raise Exception(f\"{instanceId[0]} currently appears to be unavilable for this automation\")\n else:\n raise Exception(f\"{instanceId[0]} is ineligible for this automation\")\n \n return status", "InputPayload": { "instanceId": "{{ InstanceId }}" } }, "nextStep": "isBYOLSQLServerInstalled", "isCritical": false }, { "name": "isBYOLSQLServerInstalled", "description": "In this step we check if SQL Server is installed, is not a SQL Server License Included instance and if it exists then retrieve the instance(s) details running on the EC2 instance using Windows Registry. The output captures the Name, Edition and Version of the SQL Servers", "isEnd": false, "action": "aws:runCommand", "onFailure": "Abort", "inputs": { "DocumentName": "AWS-RunPowerShellScript", "InstanceIds": [ "{{ InstanceId }}" ], "Parameters": { "commands": [ "$found = \"false\"\n\ntry {\n $webrequest = [System.Net.WebRequest]::Create(\"http://169.254.169.254/latest/dynamic/instance-identity/document\")\n $response = $webrequest.GetResponse()\n $stream = $response.GetResponseStream()\n $sr = new-object System.IO.StreamReader($stream)\n $content = $sr.ReadToEnd();\n [System.Reflection.Assembly]::LoadWithPartialName(\"System.Web.Extensions\") | out-null\n $ser = New-Object System.Web.Script.Serialization.JavaScriptSerializer\n $billingProduct = $ser.DeserializeObject($content).billingProducts\n\n if (($billingProduct -eq \"bp-6aa54003\") -or ($billingProduct -eq \"bp-62a5400b\") -or ($billingProduct -eq \"bp-65a5400c\")) \n {\n return $found\n }\n}\n\ncatch {\n $ErrorMessage = $Error[0].Exception.ErrorRecord.Exception.Message;\n}\n\n$list = New-Object System.Collections.ArrayList\n$pathExists = Test-Path -Path \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\"\nif ($pathExists)\n{\n $found = \"true\"\n $server = (get-itemproperty -ErrorAction Stop 'HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server').InstalledInstances\n foreach ($s in $server) \n {\n $p = (Get-ItemProperty 'HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL').$s\n $o = [pscustomobject]@{\n Name = $p\n Edition = (Get-ItemProperty \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$p\\Setup\").Edition\n Version = (Get-ItemProperty \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$p\\Setup\").Version\n }\n $server_info_list = $list.Add($o)\n }\n $jsonList = ConvertTo-Json -InputObject $list\n return $jsonList\n}\nelse\n{\n return $found\n} \n" ] } }, "outputs": [ { "Name": "result", "Type": "String", "Selector": "$.Output" } ] }, { "name": "foundSQLServerInstalledBranch", "description": "This step will branch (https://docs.aws.amazon.com/systems-manager/latest/userguide/automation-action-branch.html) out based on the evaluation of the previous step, defaulting to the next step if SQL Server exists on the EC2 instance otherwise exiting if unavailable", "isEnd": false, "onFailure": "Abort", "action": "aws:branch", "inputs": { "Choices": [ { "NextStep": "exitIfNoSqlServerFound", "Variable": "{{isBYOLSQLServerInstalled.result}}", "Contains": "false" } ], "Default": "updateInventory" } }, { "name": "updateInventory", "description": "Using the output of step 3 (metadata) we will update SSM Inventory with a custom inventory of type Custom:SQLServer for the EC2 instance", "action": "aws:executeScript", "inputs": { "Runtime": "python3.7", "Handler": "script_handler", "Script": "import boto3\nimport json\nfrom datetime import datetime, timezone\n \nssm = boto3.client('ssm')\n\ndef script_handler(events, context):\n sqlInfo = events['sqlInfo']\n infoList = list(eval(sqlInfo))\n timestamp = datetime.now(timezone.utc).isoformat()[:-13] + 'Z'\n instanceId = events['instanceId']\n \n response = ssm.put_inventory(\n InstanceId=instanceId[0],\n Items=[\n {\n 'TypeName': 'Custom:SQLServer',\n 'SchemaVersion': '1.0',\n 'CaptureTime': timestamp,\n 'Content': infoList\n },\n ]\n )\n \n if \"HTTPStatusCode\" in response[\"ResponseMetadata\"]:\n httpStatusCode = response[\"ResponseMetadata\"][\"HTTPStatusCode\"]\n if httpStatusCode == 200:\n status = 'Successfully updated Inventory'\n else:\n status = 'Failed to update Inventory'\n else:\n raise Exception(f\"It appears that this step couldn't be completed due to an unknown error. Please check the logs for more details\")\n \n return {'message': status}", "InputPayload": { "instanceId": "{{InstanceId}}", "sqlInfo": "{{isBYOLSQLServerInstalled.result}}" } }, "isEnd": false, "outputs": [ { "Name": "result", "Selector": "$.Payload.message", "Type": "String" } ], "nextStep": "updateLicenseManagerConfiguration" }, { "name": "updateLicenseManagerConfiguration", "description": "In this last step we determine the highest edition of SQL installed and accordingly update the License Manager Configuration associated with the EC2 instance", "action": "aws:executeScript", "isEnd": true, "onFailure": "Abort", "inputs": { "Runtime": "python3.7", "Handler": "script_handler", "InputPayload": { "accountId": "{{global:ACCOUNT_ID}}", "region": "{{global:REGION}}", "instanceId": "{{ InstanceId }}", "sqlInfo": "{{isBYOLSQLServerInstalled.result}}" }, "Script": "import boto3\nimport json\nimport re\nfrom re import search\n\ndef script_handler(events, context):\n \n lmClient = boto3.client('license-manager')\n currentLicenseList = {}\n sqlInfo = events['sqlInfo']\n jsonSqlInfo = json.loads(sqlInfo)\n \n editions = {1: 'Enterprise Edition',\n 2: 'Standard Edition',\n 3: 'Web Edition',\n 4: 'Express Edition',\n 5: 'Developer Edition'}\n \n highestEdition = ''\n for key, value in editions.items():\n\n for instance in jsonSqlInfo:\n \n if search(value,instance[\"Edition\"]):\n highestEdition = value\n break\n if highestEdition:\n break\n \n listResponse = lmClient.list_license_configurations()\n \n if listResponse['LicenseConfigurations']:\n for config in listResponse['LicenseConfigurations']:\n if config['Name']:\n currentLicenseList[config['Name']] = config['LicenseConfigurationArn']\n \n accountId = events['accountId'] \n region = events['region'] \n instanceId = events['instanceId']\n sqlServerEELicenseConfig = currentLicenseList['SQLServerEELicenseConfiguration'] \n sqlServerSTDLicenseConfig = currentLicenseList['SQLServerSTDLicenseConfiguration']\n sqlServerEXPLicenseConfig = currentLicenseList['SQLServerEXPLicenseConfiguration']\n sqlServerWEBLicenseConfig = currentLicenseList['SQLServerWEBLicenseConfiguration']\n sqlServerDEVLicenseConfig = currentLicenseList['SQLServerDEVLicenseConfiguration']\n \n ec2ARN = f\"arn:aws:ec2:{region}:{accountId}:instance/{instanceId[0]}\"\n licenseConfigARN = ''\n \n if highestEdition == \"Enterprise Edition\" and sqlServerEELicenseConfig:\n licenseConfigARN = sqlServerEELicenseConfig\n \n elif highestEdition == \"Standard Edition\" and sqlServerSTDLicenseConfig:\n licenseConfigARN = sqlServerSTDLicenseConfig\n \n elif highestEdition == \"Web Edition\" and sqlServerWEBLicenseConfig:\n licenseConfigARN = sqlServerWEBLicenseConfig \n \n elif highestEdition == \"Express Edition\" and sqlServerEXPLicenseConfig:\n licenseConfigARN = sqlServerEXPLicenseConfig\n \n elif highestEdition == \"Developer Edition\" and sqlServerDEVLicenseConfig:\n licenseConfigARN = sqlServerDEVLicenseConfig\n \n else:\n raise Exception(f\"Sorry, No {highestEdition} License Configuration ARN has been supplied\")\n \n \n if licenseConfigARN:\n \n response = lmClient.update_license_specifications_for_resource(\n ResourceArn=ec2ARN,\n AddLicenseSpecifications=[\n {\n 'LicenseConfigurationArn': licenseConfigARN\n }]);\n \n if \"HTTPStatusCode\" in response[\"ResponseMetadata\"]:\n httpStatusCode = response[\"ResponseMetadata\"][\"HTTPStatusCode\"]\n if httpStatusCode == 200:\n status = f\"Succesfully updated {licenseConfigARN} with {ec2ARN}\"\n else:\n status = f\"Failed to update {licenseConfigARN} with {ec2ARN}\"\n else:\n raise Exception(f\"It appears that this step couldn't be completed due to an unknown error. Please check the logs for more details\")\n \n return {'message': status}" }, "outputs": [ { "Name": "result", "Selector": "$.Payload.message", "Type": "String" } ] }, { "name": "exitIfNoSqlServerFound", "action": "aws:runCommand", "isEnd": true, "onFailure": "Abort", "inputs": { "DocumentName": "AWS-RunPowerShellScript", "Parameters": { "commands": [ "$message= 'No SQL Instances found'\nreturn $message\n" ] }, "InstanceIds": [ "{{ InstanceId }}" ] }, "outputs": [ { "Name": "message", "Selector": "$.Output", "Type": "String" } ], "description": "This step is triggered if there are no SQL servers found." } ] } DocumentType: "Automation" TargetType: "/AWS::EC2::Instance"