AWSTemplateFormatVersion: 2010-09-09 Description: 'Use AWS License Manager and AWS Systems Manager to discover SQL Server BYOL instances' Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: 'License Manager Configuration for SQL Servers' Parameters: - SQLServerEELicenseConfiguration - SQLServerSTDLicenseConfiguration - SQLServerWEBLicenseConfiguration - SQLServerEXPLicenseConfiguration - SQLServerDEVLicenseConfiguration ParameterLabels: SQLServerEELicenseConfiguration: default: 'Enterprise Edition' SQLServerSTDLicenseConfiguration: default: 'Standard Edition' SQLServerWEBLicenseConfiguration: default: 'Web Edition' SQLServerEXPLicenseConfiguration: default: 'Express Edition' SQLServerDEVLicenseConfiguration: default: 'Developer Edition' Parameters: InstanceId: Description: (Required) Use * to target all instances in this account or you can specify an individual instance ID Type: String Default: '*' AllowedPattern: '\*|^[m]{0,1}i-[a-z0-9]{8,17}' ConstraintDescription: Provide * to target all instances or specify an individual instnace ID TagKey: Description: (Optional) This parameter is required if you want to target all managed instances using tags Type: String Default: 'LicenseTrackingSolution-ManagedInstance' ConstraintDescription: Provide a valid tag key in the form of 'tag:ExampleTagKey' TagValue: Description: (Optional) This parameter is required if you want to target all managed instances using tags Type: String Default: 'true' SQLServerEELicenseConfiguration: Description: License Configuration ARN for SQL Server Enterprise Edition Type: String Default: '-' AllowedPattern: '-|arn:aws:license-manager:[a-z0-9-]*[-]\d:[0-9]{12}:license-configuration:lic-[a-z0-9]*$' ConstraintDescription: Provide a valid License Manager Configuration ARN for SQL Enterprise Edition or leave it as - SQLServerSTDLicenseConfiguration: Description: License Configuration ARN for SQL Server Standard Edition Type: String Default: '-' AllowedPattern: '-|arn:aws:license-manager:[a-z0-9-]*[-]\d:[0-9]{12}:license-configuration:lic-[a-z0-9]*$' ConstraintDescription: Provide a valid License Manager Configuration ARN for SQL Standard Edition or leave it as - SQLServerWEBLicenseConfiguration: Description: License Configuration ARN for SQL Server Web Edition Type: String Default: '-' AllowedPattern: '-|arn:aws:license-manager:[a-z0-9-]*[-]\d:[0-9]{12}:license-configuration:lic-[a-z0-9]*$' ConstraintDescription: Provide a valid License Manager Configuration ARN for SQL Web Edition or leave it as - SQLServerEXPLicenseConfiguration: Description: License Configuration ARN for SQL Server Express Edition Type: String Default: '-' AllowedPattern: '-|arn:aws:license-manager:[a-z0-9-]*[-]\d:[0-9]{12}:license-configuration:lic-[a-z0-9]*$' ConstraintDescription: Provide a valid License Manager Configuration ARN for SQL Express Edition or leave it as - SQLServerDEVLicenseConfiguration: Description: License Configuration ARN for SQL Server Developer Edition Type: String Default: '-' AllowedPattern: '-|arn:aws:license-manager:[a-z0-9-]*[-]\d:[0-9]{12}:license-configuration:lic-[a-z0-9]*$' ConstraintDescription: Provide a valid License Manager Configuration ARN for SQL Developer Edition or leave it as - Resources: PrimarySSMDocument: Type: 'AWS::SSM::Document' Properties: Name: Primary-SQLServerLicenseTrackingSolution-Document Content: !Sub | { "description" : "Primary document for SQL License Tracking Solution", "schemaVersion" : "0.3", "assumeRole" : "{{ AutomationAssumeRole }}", "outputs" : [ "deleteCustomInventory.result", "invokeSecondarySQLServerLicenseTrackingSolutionAutomation.result" ], "parameters" : { "InstanceId" : { "type" : "String", "description" : "(Required) Use * to target all instances in this account or you can specify an individual instance ID", "allowedPattern" : "\\*|^[m]{0,1}i-[a-z0-9]{8,17}", "default" : "*" }, "TagKey" : { "type" : "String", "description" : "(Optional) This parameter is required if you want to target all managed instances using tags", "default" : "LicenseTrackingSolution-ManagedInstance" }, "TagValue" : { "type" : "String", "description" : "(Optional) This parameter is required if you want to target all managed instances using tags", "default" : "true" }, "Region" : { "type" : "String", "description" : "AWS Region", "default" : "${AWS::Region}" }, "AccountId" : { "type" : "String", "description" : "AWS Account ID", "default" : "${AWS::AccountId}" }, "SQLServerEELicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Enterprise Edition or leave it as -", "default" : "${SQLServerEELicenseConfiguration}" }, "SQLServerEXPLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Express Edition or leave it as -", "default" : "${SQLServerEXPLicenseConfiguration}" }, "SQLServerSTDLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Standard Edition or leave it as -", "default" : "${SQLServerSTDLicenseConfiguration}" }, "SQLServerWEBLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Web Edition or leave it as -", "default" : "${SQLServerWEBLicenseConfiguration}" }, "SQLServerDEVLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Developer Edition or leave it as -", "default" : "${SQLServerDEVLicenseConfiguration}" }, "AutomationAssumeRole" : { "type" : "String", "description" : "(Required) The IAM role required to execute this document. If no role is specified, then AWS Systems Manager will be unable to complete the execution of this document.", "default" : "" } }, "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 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 print(deletion_id)\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}\n" }, "nextStep" : "invokeSecondarySQLServerLicenseTrackingSolutionAutomation", "outputs" : [ { "Name" : "result", "Selector" : "$.Payload.message", "Type" : "String" } ], "onFailure": "Abort", "isCritical" : true }, { "name" : "invokeSecondarySQLServerLicenseTrackingSolutionAutomation", "description" : "This step invokes the SecondarySQLServerLicenseTrackingSolutionDocument automation", "action" : "aws:executeScript", "inputs" : { "Runtime" : "python3.7", "Handler" : "script_handler", "Script" : "import sys\nimport boto3\nimport json\nimport botocore\n\ndef script_handler(events, context):\n client = boto3.client('ssm')\n \n instanceId = events['instanceId']\n accountId = events['accountId']\n tagKey = events['tagKey']\n tagValue = events['tagValue']\n region = events['region']\n sqlServerEELicenseConfig = events['sqlServerEELicenseConfig']\n sqlServerSTDLicenseConfig = events['sqlServerSTDLicenseConfig']\n sqlServerEXPLicenseConfig = events['sqlServerEXPLicenseConfig']\n sqlServerWEBLicenseConfig = events['sqlServerWEBLicenseConfig']\n sqlServerDEVLicenseConfig = events['sqlServerDEVLicenseConfig']\n automationAssumeRole = events['automationAssumeRole']\n \n if instanceId == '*':\n key = 'tag:'+tagKey\n values = tagValue\n else:\n key = 'ParameterValues'\n values = instanceId\n \n response = client.start_automation_execution(\n DocumentName='Secondary-SQLServerLicenseTrackingSolution-Document',\n Parameters={\n 'Region': [\n region\n ],\n 'AccountId': [\n accountId\n ],\n 'InstanceId': [\n instanceId\n ],\n 'TagKey': [\n tagKey\n ],\n 'TagValue': [\n tagValue\n ],\n 'SQLServerEELicenseConfiguration': [\n sqlServerEELicenseConfig\n ],\n 'SQLServerSTDLicenseConfiguration': [\n sqlServerSTDLicenseConfig\n ],\n 'SQLServerEXPLicenseConfiguration': [\n sqlServerEXPLicenseConfig\n ],\n 'SQLServerWEBLicenseConfiguration': [\n sqlServerWEBLicenseConfig\n ],\n 'SQLServerDEVLicenseConfiguration': [\n sqlServerDEVLicenseConfig\n ],\n 'AutomationAssumeRole': [\n automationAssumeRole\n ]\n },\n TargetParameterName='InstanceId',\n Targets=[\n {\n 'Key': key,\n 'Values': [\n values\n ]\n },\n ]\n )\n \n if response[\"AutomationExecutionId\"]:\n status = \"Secondary-SQLServerLicenseTrackingSolution-Document has been successfuly invoked. Check AutomationExecutionId - \" + response[\"AutomationExecutionId\"] + \" for more details\"\n else:\n status = \"Secondary-SQLServerLicenseTrackingSolution-Document was not invoked\"\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 }}", "tagKey" : "{{ TagKey }}", "tagValue" : "{{ TagValue }}", "accountId" : "{{ AccountId }}", "region" : "{{ Region }}", "sqlServerEELicenseConfig" : "{{ SQLServerEELicenseConfiguration }}", "sqlServerSTDLicenseConfig" : "{{ SQLServerSTDLicenseConfiguration }}", "sqlServerEXPLicenseConfig" : "{{ SQLServerEXPLicenseConfiguration }}", "sqlServerWEBLicenseConfig" : "{{ SQLServerWEBLicenseConfiguration }}", "sqlServerDEVLicenseConfig" : "{{ SQLServerDEVLicenseConfiguration }}", "automationAssumeRole" : "{{ AutomationAssumeRole }}" } }, "outputs" : [ { "Name" : "result", "Selector" : "$.Payload.message", "Type" : "String" } ], "isEnd" : true, "onFailure": "Abort", "isCritical" : true } ] } DocumentType: "Automation" SecondarySSMDocument: Type: 'AWS::SSM::Document' Properties: Name: Secondary-SQLServerLicenseTrackingSolution-Document Content: !Sub | { "description" : "Secondary document for SQL License Tracking Solution", "schemaVersion" : "0.3", "assumeRole" : "{{ AutomationAssumeRole }}", "outputs" : [ "updateInventory.result", "updateLicenseManagerConfiguration.result" ], "parameters" : { "InstanceId" : { "type" : "String", "description" : "(Required) Use * to target all instances in the account this solution is deployed into or you can specify an individual instance ID", "allowedPattern" : "\\*|^[m]{0,1}i-[a-z0-9]{8,17}", "default" : "*" }, "TagKey" : { "type" : "String", "description" : "(Optional) This parameter is required if you want to target all managed instances using tags", "default" : "LicenseTrackingSolution-ManagedInstance" }, "TagValue" : { "type" : "String", "description" : "(Optional) This parameter is required if you want to target all managed instances using tags", "default" : "true" }, "Region" : { "type" : "String", "description" : "AWS Region", "default" : "${AWS::Region}" }, "AccountId" : { "type" : "String", "description" : "AWS Account ID", "default" : "${AWS::AccountId}" }, "SQLServerEELicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Enterprise Edition or leave it as -" }, "SQLServerEXPLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Express Edition or leave it as -" }, "SQLServerSTDLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Standard Edition or leave it as -" }, "SQLServerWEBLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Web Edition or leave it as -" }, "SQLServerDEVLicenseConfiguration" : { "type" : "String", "description" : "License Configuration ARN for SQL Developer Edition or leave it as -" }, "AutomationAssumeRole" : { "type" : "String", "description" : "(Required) The IAM role required to execute this document. If no role is specified, then AWS Systems Manager will be unable to complete the execution of 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.7", "Handler" : "script_handler", "Script" : "import boto3\nimport json\nimport botocore\n\ndef script_handler(events, context):\n\n lmClient = boto3.client('license-manager')\n \n accountId = events['accountId'] \n region = events['region'] \n instanceId = events['instanceId'] \n sqlServerEELicenseConfig = events['sqlServerEELicenseConfig'] \n sqlServerSTDLicenseConfig = events['sqlServerSTDLicenseConfig']\n sqlServerEXPLicenseConfig = events['sqlServerEXPLicenseConfig']\n sqlServerWEBLicenseConfig = events['sqlServerWEBLicenseConfig']\n sqlServerDEVLicenseConfig = events['sqlServerDEVLicenseConfig']\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" : "{{ AccountId }}", "region" : "{{ Region }}", "sqlServerEELicenseConfig" : "{{ SQLServerEELicenseConfiguration }}", "sqlServerSTDLicenseConfig" : "{{ SQLServerSTDLicenseConfiguration }}", "sqlServerEXPLicenseConfig" : "{{ SQLServerEXPLicenseConfiguration }}", "sqlServerWEBLicenseConfig" : "{{ SQLServerWEBLicenseConfiguration }}", "sqlServerDEVLicenseConfig" : "{{ SQLServerDEVLicenseConfiguration }}" } }, "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,\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} is eligible for this automation execution\"\n else:\n raise Exception(f\"{instanceId} currently appears to be unavilable for this automation\")\n else:\n raise Exception(f\"{instanceId} 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 Version = (Get-ItemProperty \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$p\\Setup\").Version\n Edition = (Get-ItemProperty \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$p\\Setup\").Edition\n Name = $p\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 \n response = ssm.put_inventory(\n InstanceId=events['instanceId'],\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" : "{{ AccountId }}", "region" : "{{ Region }}", "instanceId" : "{{ InstanceId }}", "sqlInfo" : "{{isBYOLSQLServerInstalled.result}}", "sqlServerEELicenseConfig" : "{{ SQLServerEELicenseConfiguration }}", "sqlServerSTDLicenseConfig" : "{{ SQLServerSTDLicenseConfiguration }}", "sqlServerEXPLicenseConfig" : "{{ SQLServerEXPLicenseConfiguration }}", "sqlServerWEBLicenseConfig" : "{{ SQLServerWEBLicenseConfiguration }}", "sqlServerDEVLicenseConfig" : "{{ SQLServerDEVLicenseConfiguration }}" }, "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 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 accountId = events['accountId'] \n region = events['region'] \n instanceId = events['instanceId'] \n sqlServerEELicenseConfig = events['sqlServerEELicenseConfig'] \n sqlServerSTDLicenseConfig = events['sqlServerSTDLicenseConfig'] \n sqlServerEXPLicenseConfig = events['sqlServerEXPLicenseConfig'] \n sqlServerWEBLicenseConfig = events['sqlServerWEBLicenseConfig'] \n sqlServerDEVLicenseConfig = events['sqlServerDEVLicenseConfig'] \n \n ec2ARN = f\"arn:aws:ec2:{region}:{accountId}:instance/{instanceId}\"\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" SSMAssociation: Type: "AWS::SSM::Association" Properties: Name: !Ref PrimarySSMDocument DocumentVersion: "$DEFAULT" Parameters: InstanceId: - !Ref InstanceId TagKey: - !Ref TagKey TagValue: - !Ref TagValue AccountId: - !Ref AWS::AccountId AutomationAssumeRole: - !GetAtt IAMRole.Arn Region: - !Ref AWS::Region SQLServerEELicenseConfiguration: - !Ref SQLServerEELicenseConfiguration SQLServerEXPLicenseConfiguration: - !Ref SQLServerEXPLicenseConfiguration SQLServerSTDLicenseConfiguration: - !Ref SQLServerSTDLicenseConfiguration SQLServerWEBLicenseConfiguration: - !Ref SQLServerWEBLicenseConfiguration SQLServerDEVLicenseConfiguration: - !Ref SQLServerDEVLicenseConfiguration AssociationName: SQLServerLicenseTrackingSolution-Association IAMRole: Type: 'AWS::IAM::Role' Properties: Path: / RoleName: SQLServerLicenseTrackingSolution-Role AssumeRolePolicyDocument: >- {"Version":"2012-10-17","Statement":[{"Sid":"","Effect":"Allow","Principal":{"Service":"ssm.amazonaws.com"},"Action":"sts:AssumeRole"}]} MaxSessionDuration: 3600 Policies: - PolicyName: SQLServerLicenseTrackingSolution-Policy PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Sid": "sid0", "Effect": "Allow", "Action": [ "license-manager:UpdateLicenseSpecificationsForResource", "ssm:*" ], "Resource": "*" }, { "Sid": "sid1", "Effect": "Allow", "Action": [ "ssm:SendCommand" ], "Resource": [ "arn:aws:ec2:*:${AWS::AccountId}:instance/*", "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:automation-definition/${PrimarySSMDocument}:VersionId}", "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:document/${PrimarySSMDocument}", "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:automation-definition/${SecondarySSMDocument}:VersionId}", "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:document/${SecondarySSMDocument}", "arn:aws:ssm:${AWS::Region}:*:document/AWS-RunPowerShellScript" ] }, { "Sid": "sid2", "Effect": "Allow", "Action": "iam:PassRole", "Resource": "arn:aws:iam::${AWS::AccountId}:role/SQLServerLicenseTrackingSolution-Role" }, { "Sid": "sid3", "Effect": "Allow", "Action": [ "ssm:DeleteInventory", "ssm:PutInventory", "ssm:StartAutomationExecution", "ssm:ListCommands", "ec2:DescribeInstances", "ssm:DescribeInstanceInformation", "ec2:DescribeTags", "ssm:ListCommandInvocations", "ssm:UpdateInstanceAssociationStatus", "license-manager:ListLicenseSpecificationsForResource", "ec2messages:GetEndpoint", "ec2messages:FailMessage", "ec2messages:AcknowledgeMessage", "ec2messages:SendReply", "ec2messages:GetMessages", "tag:GetResources" ], "Resource": "*" } ] } Description: Allows SSM to call AWS services on your behalf for SQL Server License Tracker