-
Notifications
You must be signed in to change notification settings - Fork 0
/
SSIS jobs and SSISDB log retention.condition
1 lines (1 loc) · 5.41 KB
/
SSIS jobs and SSISDB log retention.condition
1
{"ID":"c49157da-8efe-4085-a7b8-43cef30d5a43","VersionNumber":1,"Name":"SSIS jobs and SSISDB log retention","Description":"<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\r\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\r\n\t<head>\r\n\t\t<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /><title>\r\n\t\t</title>\r\n\t\t<style type=\"text/css\">\r\n\t\t\t.cs2654AE3A{text-align:left;text-indent:0pt;margin:0pt 0pt 0pt 0pt}\r\n\t\t\t.csBEC5BAA2{color:#000000;background-color:transparent;font-family:Tahoma;font-size:8pt;font-weight:normal;font-style:normal;}\r\n\t\t</style>\r\n\t</head>\r\n\t<body>\r\n\t\t<p class=\"cs2654AE3A\"><span class=\"csBEC5BAA2\">check for any frequent ssis jobs + ssisdb retention period</span></p></body>\r\n</html>\r\n","AppliesToObjectTypeID":"0a11a887-823a-4461-87af-321cad1c3623","ConditionCategoryID":"805a84c9-7f1b-4b4d-a9ad-3dddcf9e0f17","OwnerObjectID":"b7c74f24-1a45-4115-8262-d7613878bbd6","OwnerObjectTypeID":"894de672-3fc0-4779-9a0d-880d4c207c77","RuleDefinition":"{\"OperationTypeID\":\"6c3d38f1-9317-4258-972a-cfee0a0d76b5\",\"Children\":[{\"OperationTypeID\":\"8fa3cd6a-960c-43bb-96e0-94092fc2c296\",\"ValueDataTypeID\":\"b35a57f5-b8f6-4e4e-9380-e9d328e3eabf\",\"Left\":{\"ValueTypeID\":\"aa61ee90-8a95-4fdf-a208-b9a4afa26d9d\",\"Database\":\"msdb\",\"Query\":\"IF EXISTS\\r\\n-- print 'check job frequency'\\r\\n(\\r\\nselect 1\\r\\n--jobs.name\\r\\nfrom msdb.dbo.sysjobs as jobs\\r\\ninner join msdb.dbo.sysjobsteps as jobeps on jobeps.job_id = jobs.job_id\\r\\ninner join msdb.dbo.sysjobschedules as jobles on jobs.job_id = jobles.job_id\\r\\ninner join msdb.dbo.sysschedules as les on les.schedule_id = jobles.schedule_id\\r\\nwhere 1=1\\r\\nAND jobs.enabled =1\\r\\nAND les.enabled =1\\r\\nand les.freq_type = 4 -- 4 = Daily\\r\\nand les.freq_interval = 1\\r\\nand les.freq_subday_type IN (2, 4, 8) -- 2 = Seconds, 4 = Minutes, 8 = Hours\\r\\nand les.freq_subday_interval > 0\\r\\nand jobeps.subsystem = 'SSIS'\\r\\n)\\r\\nAND EXISTS(SELECT 1 FROM master.sys.databases WHERE name = 'SSISDB')\\r\\nAND EXISTS\\r\\n(\\r\\n--print 'check ssis retention'\\r\\n\\r\\n\\tselect 1\\r\\n\\tfrom ssisdb.catalog.catalog_properties\\r\\n\\twhere 1=1\\r\\n\\tand property_name = 'RETENTION_WINDOW'\\r\\n\\tand property_value > 30 --'365'\\r\\n\\t\\r\\n)\\r\\nSELECT 1 --log retention window is too high\\r\\nELSE SELECT 0 -- nothing to see, all good\",\"InstanceType\":0},\"ComparisonType\":0,\"Right\":{\"ValueTypeID\":\"07f87b7f-c063-47a2-a5be-772ba85ed827\",\"Value\":\"1\"},\"ID\":\"1\"}],\"BooleanOperationType\":0,\"ID\":\"0\"}","EvaluationFrequency":"00:00:30","IdlePeriod":"00:00:30","MaximumAllowedDuration":"00:00:05","AntiConditionID":"6a9420db-cfa3-4a5e-b998-9ac0c600a83f","MinWindowsVersion":null,"MaxWindowsVersion":null,"MinSQLServerVersion":null,"MaxSQLServerVersion":null,"MinSSASVersion":null,"MaxSSASVersion":null,"MinVmwareVersion":null,"MaxVmwareVersion":null,"MinSqlDbVersion":null,"MaxSqlDbVersion":null,"MaximumInstanceCount":1,"ColorIndicator":null,"Severity":1,"Signature":{"ConditionID":"c49157da-8efe-4085-a7b8-43cef30d5a43","VersionNumber":1,"AppliesToObjectTypeID":"0a11a887-823a-4461-87af-321cad1c3623","RuleDefinition":"{\"OperationTypeID\":\"6c3d38f1-9317-4258-972a-cfee0a0d76b5\",\"Children\":[{\"OperationTypeID\":\"8fa3cd6a-960c-43bb-96e0-94092fc2c296\",\"ValueDataTypeID\":\"b35a57f5-b8f6-4e4e-9380-e9d328e3eabf\",\"Left\":{\"ValueTypeID\":\"aa61ee90-8a95-4fdf-a208-b9a4afa26d9d\",\"Database\":\"msdb\",\"Query\":\"IF EXISTS\\r\\n-- print 'check job frequency'\\r\\n(\\r\\nselect 1\\r\\n--jobs.name\\r\\nfrom msdb.dbo.sysjobs as jobs\\r\\ninner join msdb.dbo.sysjobsteps as jobeps on jobeps.job_id = jobs.job_id\\r\\ninner join msdb.dbo.sysjobschedules as jobles on jobs.job_id = jobles.job_id\\r\\ninner join msdb.dbo.sysschedules as les on les.schedule_id = jobles.schedule_id\\r\\nwhere 1=1\\r\\nAND jobs.enabled =1\\r\\nAND les.enabled =1\\r\\nand les.freq_type = 4 -- 4 = Daily\\r\\nand les.freq_interval = 1\\r\\nand les.freq_subday_type IN (2, 4, 8) -- 2 = Seconds, 4 = Minutes, 8 = Hours\\r\\nand les.freq_subday_interval > 0\\r\\nand jobeps.subsystem = 'SSIS'\\r\\n)\\r\\nAND EXISTS(SELECT 1 FROM master.sys.databases WHERE name = 'SSISDB')\\r\\nAND EXISTS\\r\\n(\\r\\n--print 'check ssis retention'\\r\\n\\r\\n\\tselect 1\\r\\n\\tfrom ssisdb.catalog.catalog_properties\\r\\n\\twhere 1=1\\r\\n\\tand property_name = 'RETENTION_WINDOW'\\r\\n\\tand property_value > 30 --'365'\\r\\n\\t\\r\\n)\\r\\nSELECT 1 --log retention window is too high\\r\\nELSE SELECT 0 -- nothing to see, all good\",\"InstanceType\":0},\"ComparisonType\":0,\"Right\":{\"ValueTypeID\":\"07f87b7f-c063-47a2-a5be-772ba85ed827\",\"Value\":\"1\"},\"ID\":\"1\"}],\"BooleanOperationType\":0,\"ID\":\"0\"}","PublisherID":0,"PublishDateUtc":"2020-10-09T04:54:25","Rights":0,"SignatureVersion":1,"SignaturePublicKey":"<RSAKeyValue><Modulus>uzJQ9gzevXFwOgw/hkcAtD+cA/bBbD1PRzhEZCxbZ6YwjJ1c9bbfXFItLQNwnm8bdWh2k57//qbpEj5DFHOW2EAjHc2Zw5m/vACm6OzelubPS5hbWvzshlaBJKm7KrpWQpPZClx/5eVvUVzOtlz+44RRTiOszObT58acJAQwA70=</Modulus><Exponent>AQAB</Exponent></RSAKeyValue>","Signature":"WbAd0TVPgGcctcx4qUtWFpFi496ufjfIDgjVoEzS8QagdtBSmH68NUkUaN/qc+HI38XVD/Da9OkW0yYfMIwy14GfMlyHWLmnYb/Xo7TXnconTpYG5S96UR7bxnDf1zwXIGzEuhsmj2lM3IWsZr4VDoZAqo/cE1xIWbJcXdTCZDA=","IsSelfPublishedCondition":true},"Tags":null,"Areas":[],"ConditionSystemVersion":3,"MinDBSchemaVersion":null,"MaxDBSchemaVersion":null,"Items":[]}