Description of problem: Unable to get accurate result of failed service requests via API but we are getting correct data from database command. Version-Release number of selected component (if applicable): 5.8.1.5 How reproducible: Always Steps to Reproduce: 1. Navigate to cloudforms CLI appliance. #vmdb #rails db ( to login to database) password: <enter the database password> Execute below queries: vmdb_production=# select count(status) from miq_requests where status='Error' and fulfilled_on>'2017-11-14'; -[ RECORD 1 ] count | 14 vmdb_production=# select id,status,fulfilled_on from miq_requests where status='Error' and fulfilled_on>'2017-11-14'; -[ RECORD 1 ]+--------------------------- id | 1000000000002 status | Error fulfilled_on | 2017-11-14 09:32:56.597239 -[ RECORD 2 ]+--------------------------- id | 1000000000003 status | Error fulfilled_on | 2017-11-24 10:29:23.557903 -[ RECORD 3 ]+--------------------------- id | 1000000000004 status | Error fulfilled_on | 2017-11-24 10:29:23.57356 -[ RECORD 4 ]+--------------------------- id | 1000000000022 status | Error fulfilled_on | 2017-12-22 11:42:02.458342 -[ RECORD 5 ]+--------------------------- id | 1000000000017 status | Error fulfilled_on | 2017-12-15 13:31:19.814898 -[ RECORD 6 ]+--------------------------- id | 1000000000005 status | Error fulfilled_on | 2017-12-11 10:04:32.041609 -[ RECORD 7 ]+--------------------------- id | 1000000000016 status | Error fulfilled_on | 2017-12-15 13:17:14.627672 -[ RECORD 8 ]+--------------------------- id | 1000000000018 status | Error fulfilled_on | 2017-12-15 13:36:30.05148 -[ RECORD 9 ]+--------------------------- id | 1000000000010 status | Error fulfilled_on | 2017-12-15 12:50:00.929548 -[ RECORD 10 ]--------------------------- id | 1000000000013 status | Error fulfilled_on | 2017-12-15 13:01:13.617116 -[ RECORD 11 ]--------------------------- id | 1000000000012 status | Error fulfilled_on | 2017-12-15 12:59:18.46943 -[ RECORD 12 ]--------------------------- id | 1000000000011 status | Error fulfilled_on | 2017-12-15 12:54:40.788815 -[ RECORD 13 ]--------------------------- id | 1000000000014 status | Error fulfilled_on | 2017-12-15 13:09:07.851429 -[ RECORD 14 ]--------------------------- id | 1000000000015 status | Error fulfilled_on | 2017-12-15 13:12:55.348362 But when I execute same query via API, I got 13 failed requests in compare to 14 in vmdb. While comparing both the results, I found service request id : 1000000000005 was not fetched via API but through database command. PFB the result from API: https://cfme/api/service_requests?expand=resources&attributes=fulfilled_on,status&filter[]=status='Error'&filter[]=fulfilled_on>"2017-11-14" { "name": "service_requests", "count": 18, "subcount": 13, "resources": [ { "href": "https://cfme/api/service_requests/1000000000002", "id": 1000000000002, "fulfilled_on": "2017-11-14T09:32:56Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000003", "id": 1000000000003, "fulfilled_on": "2017-11-24T10:29:23Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000004", "id": 1000000000004, "fulfilled_on": "2017-11-24T10:29:23Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000022", "id": 1000000000022, "fulfilled_on": "2017-12-22T11:42:02Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000017", "id": 1000000000017, "fulfilled_on": "2017-12-15T13:31:19Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000016", "id": 1000000000016, "fulfilled_on": "2017-12-15T13:17:14Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000018", "id": 1000000000018, "fulfilled_on": "2017-12-15T13:36:30Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000010", "id": 1000000000010, "fulfilled_on": "2017-12-15T12:50:00Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000013", "id": 1000000000013, "fulfilled_on": "2017-12-15T13:01:13Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000012", "id": 1000000000012, "fulfilled_on": "2017-12-15T12:59:18Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000011", "id": 1000000000011, "fulfilled_on": "2017-12-15T12:54:40Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000014", "id": 1000000000014, "fulfilled_on": "2017-12-15T13:09:07Z", "status": "Error" }, { "href": "https://cfme/api/service_requests/1000000000015", "id": 1000000000015, "fulfilled_on": "2017-12-15T13:12:55Z", "status": "Error" } ], } There is ambiguity in both the results. Actual results: The results from database command is not the same as via API. API unable to fetch all the details. Expected results: API should fetch the correct details. Additional info:
In general, results of SQL and api are different since result of api could be filtered (RBAC, tagging) for user executing api call. DB dump would help to investigate
Hello Yuri, The above queries output is from my test lab and I am using administrator user so no RBAC filtering has been done. Let me know if you are able to reproduce the issue at your test environment, if not will share the db dump of my test lab or the customer's environment whichever you require. Regards, Neha Chugh
Brad, Summary: -------- Please consider removing the blocker flag, lowering the priority and removing the cfme-5.8.z flag from this BZ. Details: -------- The originally reported high priority issue was addressed. This bug was then closed. After which it was then reopened to report a different lower priority, "4 (Low)", issue. The new problem is simply that, although the service_requests count is being correctly reported, the "subcount" is reported as "0" but only when being queried by non-admin user. The customer has changed the priority to "4 (Low)". I propose the blocker flag be removed. I also propose the "CFME-5.8.z" flag be removed. Thank you. JoeV
This issue has been fixed in Master. There has been a lot of work done to address this and related issues. By agreement with Keenan Brock I'm going to mark this BZ as a duplicate of 1608554. *** This bug has been marked as a duplicate of bug 1608554 ***