{"action":"closed","number":1384,"pull_request":{"url":"https://api.github.com/repos/Normation/rudder/pulls/1384","id":94641319,"html_url":"https://github.com/Normation/rudder/pull/1384","diff_url":"https://github.com/Normation/rudder/pull/1384.diff","patch_url":"https://github.com/Normation/rudder/pull/1384.patch","issue_url":"https://api.github.com/repos/Normation/rudder/issues/1384","number":1384,"state":"closed","locked":false,"title":"Fixes #9645: Add a table for node compliance","user":{"login":"fanf","id":44649,"avatar_url":"https://avatars.githubusercontent.com/u/44649?v=3","gravatar_id":"","url":"https://api.github.com/users/fanf","html_url":"https://github.com/fanf","followers_url":"https://api.github.com/users/fanf/followers","following_url":"https://api.github.com/users/fanf/following{/other_user}","gists_url":"https://api.github.com/users/fanf/gists{/gist_id}","starred_url":"https://api.github.com/users/fanf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fanf/subscriptions","organizations_url":"https://api.github.com/users/fanf/orgs","repos_url":"https://api.github.com/users/fanf/repos","events_url":"https://api.github.com/users/fanf/events{/privacy}","received_events_url":"https://api.github.com/users/fanf/received_events","type":"User","site_admin":false},"body":"https://www.rudder-project.org/redmine/issues/9645\r\n\r\nThis PR add the storage of the compliance by node by run in a new Postgres Table. \r\n\r\nThe only interesting things are: \r\n- when we call the store action: in ReportsExecutionService, just after having updated the node compliance cache (we use the result, all calculus are done)\r\n- the database structure. \r\n\r\n## Database structure\r\n\r\n### column\r\n\r\nThere is a good description on the table declaration of field. Some more \"what/why\":\r\n\r\n- nodeId, runTimestamp, nodeConfigId: what we are interested in to do queries. We would much likely want to get the last line by runTimestamp for a nodeId. \r\n- endOfLife: date after which the compliance is not valide anymore. If no new runs, it means the node doesn't send reports anymore. Perhaps the name is not that good, and \"endDate\" should be used. \r\n- runAnalysis: information about the run, the same that are used in the node details screen, on the colored header (but without information already in the 3 first columns). It does not contains the \"expected node configuration\", because 1/ it's huge, 2/ we can get it by looking to run status, and if the run had a configId, find back the expected config from theire 3/ we almost have all the information from the compliance appart in certain (hill) cases\r\n- summary: compliance summary at the node level - to query only that on the future for node list or in debug\r\n- details: the whole compliance details down to the rule/directive/component/value/message. It should be sufficient to any use case in the future (ie no information destroyed, everything is kept)\r\n\r\n**Question**: should we add a dedicated column for nodeConfigId ? It seems rather useful, even if it may be null in pathological cases. \r\n\r\n### Compliance Values in percent - aggregated needed ?\r\n\r\nIn Scala, we store compliance level with a count of each type of reports (and of course we have the total with the sum of each kind). That allows to manipulate ints, which is better than double for rounding error :)\r\nA chose to store the compliance level in percent + the total number of report because if data are not normalized, it will be impossible to do queries in the future like \"give me all the directive with success < 50%\". \r\n\r\nAnd **I'm wondering** if we should not go even further, and store in addition to the completely detailed version also aggregated result for success (ie report success + report compliant + repaired + n/a), non-compliant, error, etc - i.e all the top level status\r\n\r\n### it is by node by run, not by node-rule by run or aggregated on several runs. \r\n\r\nAggregation is much more complexe to achieve on a node level. It needs a \"Delta\" logic (ie what change between a snapshot point and following runs) to be efficient. All that should be done in a different store or different module. \r\n\r\nSo why not just use a by run by node/rule/directive(/component) table? Because it would lead to much more redondancies in the first columns between lines, and many, many more lines. \r\nAs an example, we have ~300 runs by node by day. Addind rule and directives, we add (low estimate) a x20 number of lines. Adding componenent, and it's (low estimation) a x100.  \r\n\r\nAnd it felt more logical / consistent with other part of Rudder to have a by-run-by-node result.  \r\n\r\n### disk size\r\n\r\nThe produce json for 12 directives, including systems (so some with a large number of components), is around 75kB. \r\n\r\nThe corresponding stored json seems to amortize toward 2.5kB - Postgres is compressing like hell :)\r\nWith that value, we get ~700MB of data by day for 1000 nodes. \r\n\r\nUsing the JSON type format doesn't change AT ALL the disk size (certainly because postgres store json as text, only checking it's valid json). \r\nUsing JSONB would improve the space taken, but it's only on Postgres 9.5 and up. \r\n\r\n","created_at":"2016-11-21T15:52:31Z","updated_at":"2016-12-07T16:18:51Z","closed_at":"2016-12-07T16:18:51Z","merged_at":"2016-12-07T16:18:51Z","merge_commit_sha":"7cf89b0c4501a31ecb457f80f6b51a7b2d7240b1","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/Normation/rudder/pulls/1384/commits","review_comments_url":"https://api.github.com/repos/Normation/rudder/pulls/1384/comments","review_comment_url":"https://api.github.com/repos/Normation/rudder/pulls/comments{/number}","comments_url":"https://api.github.com/repos/Normation/rudder/issues/1384/comments","statuses_url":"https://api.github.com/repos/Normation/rudder/statuses/7cf89b0c4501a31ecb457f80f6b51a7b2d7240b1","head":{"label":"fanf:arch_9645/add_a_table_for_node_compliance","ref":"arch_9645/add_a_table_for_node_compliance","sha":"7cf89b0c4501a31ecb457f80f6b51a7b2d7240b1","user":{"login":"fanf","id":44649,"avatar_url":"https://avatars.githubusercontent.com/u/44649?v=3","gravatar_id":"","url":"https://api.github.com/users/fanf","html_url":"https://github.com/fanf","followers_url":"https://api.github.com/users/fanf/followers","following_url":"https://api.github.com/users/fanf/following{/other_user}","gists_url":"https://api.github.com/users/fanf/gists{/gist_id}","starred_url":"https://api.github.com/users/fanf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fanf/subscriptions","organizations_url":"https://api.github.com/users/fanf/orgs","repos_url":"https://api.github.com/users/fanf/repos","events_url":"https://api.github.com/users/fanf/events{/privacy}","received_events_url":"https://api.github.com/users/fanf/received_events","type":"User","site_admin":false},"repo":{"id":6705258,"name":"rudder","full_name":"fanf/rudder","owner":{"login":"fanf","id":44649,"avatar_url":"https://avatars.githubusercontent.com/u/44649?v=3","gravatar_id":"","url":"https://api.github.com/users/fanf","html_url":"https://github.com/fanf","followers_url":"https://api.github.com/users/fanf/followers","following_url":"https://api.github.com/users/fanf/following{/other_user}","gists_url":"https://api.github.com/users/fanf/gists{/gist_id}","starred_url":"https://api.github.com/users/fanf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fanf/subscriptions","organizations_url":"https://api.github.com/users/fanf/orgs","repos_url":"https://api.github.com/users/fanf/repos","events_url":"https://api.github.com/users/fanf/events{/privacy}","received_events_url":"https://api.github.com/users/fanf/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/fanf/rudder","description":null,"fork":true,"url":"https://api.github.com/repos/fanf/rudder","forks_url":"https://api.github.com/repos/fanf/rudder/forks","keys_url":"https://api.github.com/repos/fanf/rudder/keys{/key_id}","collaborators_url":"https://api.github.com/repos/fanf/rudder/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/fanf/rudder/teams","hooks_url":"https://api.github.com/repos/fanf/rudder/hooks","issue_events_url":"https://api.github.com/repos/fanf/rudder/issues/events{/number}","events_url":"https://api.github.com/repos/fanf/rudder/events","assignees_url":"https://api.github.com/repos/fanf/rudder/assignees{/user}","branches_url":"https://api.github.com/repos/fanf/rudder/branches{/branch}","tags_url":"https://api.github.com/repos/fanf/rudder/tags","blobs_url":"https://api.github.com/repos/fanf/rudder/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/fanf/rudder/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/fanf/rudder/git/refs{/sha}","trees_url":"https://api.github.com/repos/fanf/rudder/git/trees{/sha}","statuses_url":"https://api.github.com/repos/fanf/rudder/statuses/{sha}","languages_url":"https://api.github.com/repos/fanf/rudder/languages","stargazers_url":"https://api.github.com/repos/fanf/rudder/stargazers","contributors_url":"https://api.github.com/repos/fanf/rudder/contributors","subscribers_url":"https://api.github.com/repos/fanf/rudder/subscribers","subscription_url":"https://api.github.com/repos/fanf/rudder/subscription","commits_url":"https://api.github.com/repos/fanf/rudder/commits{/sha}","git_commits_url":"https://api.github.com/repos/fanf/rudder/git/commits{/sha}","comments_url":"https://api.github.com/repos/fanf/rudder/comments{/number}","issue_comment_url":"https://api.github.com/repos/fanf/rudder/issues/comments{/number}","contents_url":"https://api.github.com/repos/fanf/rudder/contents/{+path}","compare_url":"https://api.github.com/repos/fanf/rudder/compare/{base}...{head}","merges_url":"https://api.github.com/repos/fanf/rudder/merges","archive_url":"https://api.github.com/repos/fanf/rudder/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/fanf/rudder/downloads","issues_url":"https://api.github.com/repos/fanf/rudder/issues{/number}","pulls_url":"https://api.github.com/repos/fanf/rudder/pulls{/number}","milestones_url":"https://api.github.com/repos/fanf/rudder/milestones{/number}","notifications_url":"https://api.github.com/repos/fanf/rudder/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/fanf/rudder/labels{/name}","releases_url":"https://api.github.com/repos/fanf/rudder/releases{/id}","deployments_url":"https://api.github.com/repos/fanf/rudder/deployments","created_at":"2012-11-15T13:39:53Z","updated_at":"2014-06-06T15:06:41Z","pushed_at":"2016-12-06T09:24:08Z","git_url":"git://github.com/fanf/rudder.git","ssh_url":"git@github.com:fanf/rudder.git","clone_url":"https://github.com/fanf/rudder.git","svn_url":"https://github.com/fanf/rudder","homepage":"http://www.rudder-project.org/","size":24599,"stargazers_count":0,"watchers_count":0,"language":"Scala","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"Normation:master","ref":"master","sha":"06cf01bb6c0a903f49a4d05d077eb14f91634de4","user":{"login":"Normation","id":695105,"avatar_url":"https://avatars.githubusercontent.com/u/695105?v=3","gravatar_id":"","url":"https://api.github.com/users/Normation","html_url":"https://github.com/Normation","followers_url":"https://api.github.com/users/Normation/followers","following_url":"https://api.github.com/users/Normation/following{/other_user}","gists_url":"https://api.github.com/users/Normation/gists{/gist_id}","starred_url":"https://api.github.com/users/Normation/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Normation/subscriptions","organizations_url":"https://api.github.com/users/Normation/orgs","repos_url":"https://api.github.com/users/Normation/repos","events_url":"https://api.github.com/users/Normation/events{/privacy}","received_events_url":"https://api.github.com/users/Normation/received_events","type":"Organization","site_admin":false},"repo":{"id":2528254,"name":"rudder","full_name":"Normation/rudder","owner":{"login":"Normation","id":695105,"avatar_url":"https://avatars.githubusercontent.com/u/695105?v=3","gravatar_id":"","url":"https://api.github.com/users/Normation","html_url":"https://github.com/Normation","followers_url":"https://api.github.com/users/Normation/followers","following_url":"https://api.github.com/users/Normation/following{/other_user}","gists_url":"https://api.github.com/users/Normation/gists{/gist_id}","starred_url":"https://api.github.com/users/Normation/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Normation/subscriptions","organizations_url":"https://api.github.com/users/Normation/orgs","repos_url":"https://api.github.com/users/Normation/repos","events_url":"https://api.github.com/users/Normation/events{/privacy}","received_events_url":"https://api.github.com/users/Normation/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/Normation/rudder","description":"Continuous configuration for effective compliance","fork":false,"url":"https://api.github.com/repos/Normation/rudder","forks_url":"https://api.github.com/repos/Normation/rudder/forks","keys_url":"https://api.github.com/repos/Normation/rudder/keys{/key_id}","collaborators_url":"https://api.github.com/repos/Normation/rudder/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/Normation/rudder/teams","hooks_url":"https://api.github.com/repos/Normation/rudder/hooks","issue_events_url":"https://api.github.com/repos/Normation/rudder/issues/events{/number}","events_url":"https://api.github.com/repos/Normation/rudder/events","assignees_url":"https://api.github.com/repos/Normation/rudder/assignees{/user}","branches_url":"https://api.github.com/repos/Normation/rudder/branches{/branch}","tags_url":"https://api.github.com/repos/Normation/rudder/tags","blobs_url":"https://api.github.com/repos/Normation/rudder/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/Normation/rudder/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/Normation/rudder/git/refs{/sha}","trees_url":"https://api.github.com/repos/Normation/rudder/git/trees{/sha}","statuses_url":"https://api.github.com/repos/Normation/rudder/statuses/{sha}","languages_url":"https://api.github.com/repos/Normation/rudder/languages","stargazers_url":"https://api.github.com/repos/Normation/rudder/stargazers","contributors_url":"https://api.github.com/repos/Normation/rudder/contributors","subscribers_url":"https://api.github.com/repos/Normation/rudder/subscribers","subscription_url":"https://api.github.com/repos/Normation/rudder/subscription","commits_url":"https://api.github.com/repos/Normation/rudder/commits{/sha}","git_commits_url":"https://api.github.com/repos/Normation/rudder/git/commits{/sha}","comments_url":"https://api.github.com/repos/Normation/rudder/comments{/number}","issue_comment_url":"https://api.github.com/repos/Normation/rudder/issues/comments{/number}","contents_url":"https://api.github.com/repos/Normation/rudder/contents/{+path}","compare_url":"https://api.github.com/repos/Normation/rudder/compare/{base}...{head}","merges_url":"https://api.github.com/repos/Normation/rudder/merges","archive_url":"https://api.github.com/repos/Normation/rudder/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/Normation/rudder/downloads","issues_url":"https://api.github.com/repos/Normation/rudder/issues{/number}","pulls_url":"https://api.github.com/repos/Normation/rudder/pulls{/number}","milestones_url":"https://api.github.com/repos/Normation/rudder/milestones{/number}","notifications_url":"https://api.github.com/repos/Normation/rudder/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/Normation/rudder/labels{/name}","releases_url":"https://api.github.com/repos/Normation/rudder/releases{/id}","deployments_url":"https://api.github.com/repos/Normation/rudder/deployments","created_at":"2011-10-06T19:27:50Z","updated_at":"2016-12-04T20:47:27Z","pushed_at":"2016-12-07T16:18:50Z","git_url":"git://github.com/Normation/rudder.git","ssh_url":"git@github.com:Normation/rudder.git","clone_url":"https://github.com/Normation/rudder.git","svn_url":"https://github.com/Normation/rudder","homepage":"http://www.rudder-project.org/","size":23755,"stargazers_count":116,"watchers_count":116,"language":"Scala","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":24,"mirror_url":null,"open_issues_count":5,"forks":24,"open_issues":5,"watchers":116,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/Normation/rudder/pulls/1384"},"html":{"href":"https://github.com/Normation/rudder/pull/1384"},"issue":{"href":"https://api.github.com/repos/Normation/rudder/issues/1384"},"comments":{"href":"https://api.github.com/repos/Normation/rudder/issues/1384/comments"},"review_comments":{"href":"https://api.github.com/repos/Normation/rudder/pulls/1384/comments"},"review_comment":{"href":"https://api.github.com/repos/Normation/rudder/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/Normation/rudder/pulls/1384/commits"},"statuses":{"href":"https://api.github.com/repos/Normation/rudder/statuses/7cf89b0c4501a31ecb457f80f6b51a7b2d7240b1"}},"merged":true,"mergeable":null,"mergeable_state":"unknown","merged_by":{"login":"Normation-Quality-Assistant","id":1324592,"avatar_url":"https://avatars.githubusercontent.com/u/1324592?v=3","gravatar_id":"","url":"https://api.github.com/users/Normation-Quality-Assistant","html_url":"https://github.com/Normation-Quality-Assistant","followers_url":"https://api.github.com/users/Normation-Quality-Assistant/followers","following_url":"https://api.github.com/users/Normation-Quality-Assistant/following{/other_user}","gists_url":"https://api.github.com/users/Normation-Quality-Assistant/gists{/gist_id}","starred_url":"https://api.github.com/users/Normation-Quality-Assistant/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Normation-Quality-Assistant/subscriptions","organizations_url":"https://api.github.com/users/Normation-Quality-Assistant/orgs","repos_url":"https://api.github.com/users/Normation-Quality-Assistant/repos","events_url":"https://api.github.com/users/Normation-Quality-Assistant/events{/privacy}","received_events_url":"https://api.github.com/users/Normation-Quality-Assistant/received_events","type":"User","site_admin":false},"comments":5,"review_comments":12,"commits":1,"additions":608,"deletions":57,"changed_files":14}}
{"actor":{"display_login":"Normation-Quality-Assistant"}}