description: Secondary document for SQL License Tracking Solution schemaVersion: '0.3' assumeRole: '{{ AutomationAssumeRole }}' outputs: - updateInventory.result - updateLicenseManagerConfiguration.result 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}' TagKey: description: (Optional) This parameter is required if you want to target all managed instances type: String default: 'tag:LicenseTrackingSolution-ManagedInstance' TagValue: description: (Optional) This parameter is required if you want to target all managed instances type: String default: 'true' Region: type: String description: (Optional) Specify the AWS Region where you are deploying this document otherwise the region in which this document is executed will be used default: '{{global:REGION}}' AccountId: type: String description: (Optional) Specify the AWS Account ID where you are deploying this document otherwise the account in which this document is executed will be used default: '{{global:ACCOUNT_ID}}' SQLServerEELicenseConfiguration: type: String description: License Configuration for SQL Enterprise Edition or leave it as - default: '-' SQLServerEXPLicenseConfiguration: type: String description: License Configuration for SQL Express Edition or leave it as - default: '-' SQLServerSTDLicenseConfiguration: type: String description: License Configuration for SQL Standard Edition or leave it as - default: '-' SQLServerWEBLicenseConfiguration: type: String description: License Configuration for SQL Web Edition or leave it as - default: '-' SQLServerDEVLicenseConfiguration: type: String description: License Configuration for SQL Developer Edition or leave it as - default: '-' AutomationAssumeRole: type: String description: '(Required) The IAM role for this execution. 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 import json import botocore def script_handler(events, context): lmClient = boto3.client('license-manager') accountId = events['accountId'] region = events['region'] instanceId = events['instanceId'] sqlServerEELicenseConfig = events['sqlServerEELicenseConfig'] sqlServerSTDLicenseConfig = events['sqlServerSTDLicenseConfig'] sqlServerEXPLicenseConfig = events['sqlServerEXPLicenseConfig'] sqlServerWEBLicenseConfig = events['sqlServerWEBLicenseConfig'] sqlServerDEVLicenseConfig = events['sqlServerDEVLicenseConfig'] ec2ARN = f"arn:aws:ec2:{region}:{accountId}:instance/{instanceId}" licenseConfigARN = '' numberOfUpdates = 0 try: lmResponse = lmClient.list_license_specifications_for_resource( ResourceArn=ec2ARN, MaxResults=10 ) if lmResponse['LicenseSpecifications']: for spec in lmResponse['LicenseSpecifications']: if spec['LicenseConfigurationArn']: if (sqlServerEELicenseConfig and spec['LicenseConfigurationArn'] == sqlServerEELicenseConfig): licenseConfigARN = sqlServerEELicenseConfig elif (sqlServerSTDLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerSTDLicenseConfig): licenseConfigARN = sqlServerSTDLicenseConfig elif (sqlServerEXPLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerEXPLicenseConfig): licenseConfigARN = sqlServerEXPLicenseConfig elif (sqlServerWEBLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerWEBLicenseConfig): licenseConfigARN = sqlServerWEBLicenseConfig elif (sqlServerDEVLicenseConfig and spec['LicenseConfigurationArn'] == sqlServerDEVLicenseConfig): licenseConfigARN = sqlServerDEVLicenseConfig if licenseConfigARN: updateResponse = lmClient.update_license_specifications_for_resource( ResourceArn=ec2ARN, RemoveLicenseSpecifications=[ { 'LicenseConfigurationArn': licenseConfigARN }]); numberOfUpdates = numberOfUpdates + 1 licenseConfigARN = '' except botocore.exceptions.ClientError as error: if 'Resource ARN is invalid' in error.response['Error']['Message']: print('EC2 does not exist for this resource. Skipping this step') 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 import boto3 import json import botocore def script_handler(events, context): instanceId = events['instanceId'] client = boto3.client('ssm') response = client.describe_instance_information( Filters=[ { 'Key': 'InstanceIds', 'Values': [ instanceId, ] }, ] ) if response["InstanceInformationList"]: if response["InstanceInformationList"][0]['PingStatus'] == 'Online' and response["InstanceInformationList"][0]['PlatformType'] == 'Windows': status = f"{instanceId} is eligible for this automation execution" else: raise Exception(f"{instanceId} currently appears to be unavilable for this automation") else: raise Exception(f"{instanceId} is ineligible for this automation") 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" try { $webrequest = [System.Net.WebRequest]::Create("http://169.254.169.254/latest/dynamic/instance-identity/document") $response = $webrequest.GetResponse() $stream = $response.GetResponseStream() $sr = new-object System.IO.StreamReader($stream) $content = $sr.ReadToEnd(); [System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions") | out-null $ser = New-Object System.Web.Script.Serialization.JavaScriptSerializer $billingProduct = $ser.DeserializeObject($content).billingProducts if (($billingProduct -eq "bp-6aa54003") -or ($billingProduct -eq "bp-62a5400b") -or ($billingProduct -eq "bp-65a5400c")) { return $found } } catch { $ErrorMessage = $Error[0].Exception.ErrorRecord.Exception.Message; } $list = New-Object System.Collections.ArrayList $pathExists = Test-Path -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server" if ($pathExists) { $found = "true" $server = (get-itemproperty -ErrorAction Stop 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances foreach ($s in $server) { $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$s $o = [pscustomobject]@{ Version = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version Edition = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition Name = $p } $server_info_list = $list.Add($o) } $jsonList = ConvertTo-Json -InputObject $list return $jsonList } else { return $found } 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 import json from datetime import datetime, timezone ssm = boto3.client('ssm') def script_handler(events, context): sqlInfo = events['sqlInfo'] infoList = list(eval(sqlInfo)) timestamp = datetime.now(timezone.utc).isoformat()[:-13] + 'Z' response = ssm.put_inventory( InstanceId=events['instanceId'], Items=[ { 'TypeName': 'Custom:SQLServer', 'SchemaVersion': '1.0', 'CaptureTime': timestamp, 'Content': infoList }, ] ) if "HTTPStatusCode" in response["ResponseMetadata"]: httpStatusCode = response["ResponseMetadata"]["HTTPStatusCode"] if httpStatusCode == 200: status = 'Successfully updated Inventory' else: status = 'Failed to update Inventory' else: raise Exception(f"It appears that this step couldn't be completed due to an unknown error. Please check the logs for more details") 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 import json import re from re import search def script_handler(events, context): lmClient = boto3.client('license-manager') sqlInfo = events['sqlInfo'] jsonSqlInfo = json.loads(sqlInfo) editions = {1: 'Enterprise Edition', 2: 'Standard Edition', 3: 'Web Edition', 4: 'Express Edition', 5: 'Developer Edition'} highestEdition = '' for key, value in editions.items(): for instance in jsonSqlInfo: if search(value,instance["Edition"]): highestEdition = value break if highestEdition: break accountId = events['accountId'] region = events['region'] instanceId = events['instanceId'] sqlServerEELicenseConfig = events['sqlServerEELicenseConfig'] sqlServerSTDLicenseConfig = events['sqlServerSTDLicenseConfig'] sqlServerEXPLicenseConfig = events['sqlServerEXPLicenseConfig'] sqlServerWEBLicenseConfig = events['sqlServerWEBLicenseConfig'] sqlServerDEVLicenseConfig = events['sqlServerDEVLicenseConfig'] ec2ARN = f"arn:aws:ec2:{region}:{accountId}:instance/{instanceId}" licenseConfigARN = '' if highestEdition == "Enterprise Edition" and sqlServerEELicenseConfig: licenseConfigARN = sqlServerEELicenseConfig elif highestEdition == "Standard Edition" and sqlServerSTDLicenseConfig: licenseConfigARN = sqlServerSTDLicenseConfig elif highestEdition == "Web Edition" and sqlServerWEBLicenseConfig: licenseConfigARN = sqlServerWEBLicenseConfig elif highestEdition == "Express Edition" and sqlServerEXPLicenseConfig: licenseConfigARN = sqlServerEXPLicenseConfig elif highestEdition == "Developer Edition" and sqlServerDEVLicenseConfig: licenseConfigARN = sqlServerDEVLicenseConfig else: raise Exception(f"Sorry, No {highestEdition} License Configuration ARN has been supplied") if licenseConfigARN: response = lmClient.update_license_specifications_for_resource( ResourceArn=ec2ARN, AddLicenseSpecifications=[ { 'LicenseConfigurationArn': licenseConfigARN }]); if "HTTPStatusCode" in response["ResponseMetadata"]: httpStatusCode = response["ResponseMetadata"]["HTTPStatusCode"] if httpStatusCode == 200: status = f"Succesfully updated {licenseConfigARN} with {ec2ARN}" else: status = f"Failed to update {licenseConfigARN} with {ec2ARN}" else: raise Exception(f"It appears that this step couldn't be completed due to an unknown error. Please check the logs for more details") 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' return $message InstanceIds: - '{{ InstanceId }}' outputs: - Name: message Selector: $.Output Type: String description: This step is triggered if there are no SQL servers found.