{"action":"created","issue":{"url":"https://api.github.com/repos/Netflix/ice/issues/143","labels_url":"https://api.github.com/repos/Netflix/ice/issues/143/labels{/name}","comments_url":"https://api.github.com/repos/Netflix/ice/issues/143/comments","events_url":"https://api.github.com/repos/Netflix/ice/issues/143/events","html_url":"https://github.com/Netflix/ice/issues/143","id":56416654,"number":143,"title":"Ice totals off","user":{"login":"ckelner","id":133862,"avatar_url":"https://avatars.githubusercontent.com/u/133862?v=3","gravatar_id":"","url":"https://api.github.com/users/ckelner","html_url":"https://github.com/ckelner","followers_url":"https://api.github.com/users/ckelner/followers","following_url":"https://api.github.com/users/ckelner/following{/other_user}","gists_url":"https://api.github.com/users/ckelner/gists{/gist_id}","starred_url":"https://api.github.com/users/ckelner/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ckelner/subscriptions","organizations_url":"https://api.github.com/users/ckelner/orgs","repos_url":"https://api.github.com/users/ckelner/repos","events_url":"https://api.github.com/users/ckelner/events{/privacy}","received_events_url":"https://api.github.com/users/ckelner/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":27,"created_at":"2015-02-03T18:25:58Z","updated_at":"2015-02-23T21:19:24Z","closed_at":null,"body":"Hello, our Ice total is off by a large amount when totaled over the month, for individual days it is obviously much smaller (1/30th or thereabouts).  I can't share the exact amount, but suffice to say it is large enough that it is not easily missed.\r\n\r\n![screen shot 2015-02-03 at 1 07 34 pm](https://cloud.githubusercontent.com/assets/133862/6026251/4ded6b0c-aba7-11e4-9081-16e5e1a80d3c.png)\r\n\r\nI wrote a dumb py script (see https://gist.github.com/ckelner/962b9e52db11fc73cf68#file-aws_csv_totaler-py) to total our CSV by LineItem (our CSV is 17million lines and about 7Gb, so it is hard to handle any other way).  The value it spits out is equal to the value AWS shows in the billing console.  The totals at the bottom of the CSV also line up w/ the AWS billing console and the py script.\r\n\r\nMy colleague, @AlexHawk31 has done some deep dive comparisons on the numbers (she's really good at that), and it looks like charges for unused reserved instances aren't showing up for us.  Every account that has unused RIs is off by some value.\r\n\r\nTo provide some more context, that may be helpful, we have 70+ AWS accounts under a single master account.  We also have several hundred RI purchases (not in this month, just overall).  We also added two new accounts under the master account to consolidated billing this month.\r\n\r\nAdditionally we also run more than one processor for redundancy, uptime, etc, as seen in https://github.com/Netflix/ice/issues/141.  Not sure if that matters or not (attempting to figure that out in issue 141).\r\n\r\nI've seen a lot of fixes which involve \"dumping local ice files and ice s3 files\" -- I'd like to avoid doing that and rather find the root of the problem.  I'm happy to make code changes, but I wanted to come here first and start a conversation about it."},"comment":{"url":"https://api.github.com/repos/Netflix/ice/issues/comments/75635839","html_url":"https://github.com/Netflix/ice/issues/143#issuecomment-75635839","issue_url":"https://api.github.com/repos/Netflix/ice/issues/143","id":75635839,"user":{"login":"ckelner","id":133862,"avatar_url":"https://avatars.githubusercontent.com/u/133862?v=3","gravatar_id":"","url":"https://api.github.com/users/ckelner","html_url":"https://github.com/ckelner","followers_url":"https://api.github.com/users/ckelner/followers","following_url":"https://api.github.com/users/ckelner/following{/other_user}","gists_url":"https://api.github.com/users/ckelner/gists{/gist_id}","starred_url":"https://api.github.com/users/ckelner/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ckelner/subscriptions","organizations_url":"https://api.github.com/users/ckelner/orgs","repos_url":"https://api.github.com/users/ckelner/repos","events_url":"https://api.github.com/users/ckelner/events{/privacy}","received_events_url":"https://api.github.com/users/ckelner/received_events","type":"User","site_admin":false},"created_at":"2015-02-23T21:19:24Z","updated_at":"2015-02-23T21:19:24Z","body":"More information, I've been in touch with AWS support regarding this, because it seems that our ECSV/DBR (as AWS calls it) is wrong if this logic is to be trusted.\r\n\r\nHere's my most recent comms with AWS in chronological order, the last two communications are most important, citing specific problems found with the ECSV/DBR.  TL;DR: When adding up every line item (including the \"utilization\" summarizations we've talked about in this GitHub issue), outside of ice, it equals our consolidated bill total.  When excluding those values it falls short in the same way ice does.\r\n\r\n*The Weather Channel\r\nFeb 20, 2015\r\n09:15 AM -0500*\r\n> So I've raised this flag previously, but it was due to line items being in the us-east-1a zone.  Now I am seeing several line items which are not in us-east-1 (per the UsageType column).\r\n> \r\n> For example:\r\n```\r\nInvoiceID,PayerAccountId,LinkedAccountId,RecordType,RecordId,ProductName,RateId,SubscriptionId,PricingPlanId,UsageType,Operation,AvailabilityZone,ReservedInstance,ItemDescription,UsageStartDate,UsageEndDate,UsageQuantity,BlendedRate,BlendedCost,UnBlendedRate,UnBlendedCost,ResourceId,user:Billing,user:Owner\r\n\"Estimated\",\"013328811177\",\"682134577783\",\"LineItem\",\"400000000223405011\",\"Amazon Elastic Compute Cloud\",\"3207476\",,,\"EU-HeavyUsage:r3.large\",\"RunInstances\",,\"Y\",\"USD 0.0323 hourly fee per Linux/UNIX (Amazon VPC), r3.large instance (1488.0 hours purchased, 1488.0 hours used)\",\"2015-01-01 00:00:00\",\"2015-01-31 23:59:59\",\"0\",,\"48.060000\",,\"0\",,,\r\n\"Estimated\",\"013328811177\",\"682134577783\",\"LineItem\",\"400000000223404865\",\"Amazon Elastic Compute Cloud\",\"2321982\",,,\"EU-HeavyUsage:r3.large\",\"RunInstances\",,\"Y\",\"USD 0.034 hourly fee per Linux/UNIX (Amazon VPC), r3.large instance (2232.0 hours purchased, 2232.0 hours used)\",\"2015-01-01 00:00:00\",\"2015-01-31 23:59:59\",\"0\",,\"75.890000\",,\"0.00200000000\",,,\r\n\"Estimated\",\"013328811177\",\"682134577783\",\"LineItem\",\"400000000223404943\",\"Amazon Elastic Compute Cloud\",\"3207476\",,,\"EU-HeavyUsage:r3.large\",\"RunInstances\",,\"Y\",\"USD 0.0323 hourly fee per Linux/UNIX (Amazon VPC), r3.large instance (1488.0 hours purchased, 1488.0 hours used)\",\"2015-01-01 00:00:00\",\"2015-01-31 23:59:59\",\"0\",,\"48.060000\",,\"0\",,,\r\n\"Estimated\",\"013328811177\",\"682134577783\",\"LineItem\",\"400000000223404871\",\"Amazon Elastic Compute Cloud\",\"3373535\",,,\"EU-HeavyUsage:r3.large\",\"RunInstances\",,\"Y\",\"USD 0.0323 hourly fee per Linux/UNIX (Amazon VPC), r3.large instance (1488.0 hours purchased, 715.00000000 hours used)\",\"2015-01-01 00:00:00\",\"2015-01-31 23:59:59\",\"773.00000000\",,\"48.060000\",,\"24.965500000000\",,,\r\n\"Estimated\",\"013328811177\",\"682134577783\",\"LineItem\",\"400000000223404866\",\"Amazon Elastic Compute Cloud\",\"3259269\",,,\"EU-HeavyUsage:r3.large\",\"RunInstances\",,\"Y\",\"USD 0.0323 hourly fee per Linux/UNIX (Amazon VPC), r3.large instance (1488.0 hours purchased, 1488.0 hours used)\",\"2015-01-01 00:00:00\",\"2015-01-31 23:59:59\",\"0\",,\"48.060000\",,\"0\",,,\r\n```\r\n> \r\n> Can you explain this to me please?  We need to be able to reconcile these costs outside of the AWS Billing dashboard.\r\n\r\n*Amazon Web Services\r\nFeb 20, 2015\r\n01:27 PM -0500*\r\n> I'm going to submit a feature request to have the AZs reported in this section of the DBR, but I want to clarify a few things as well to help reconcile these costs outside of the AWS Billing dashboard. As I'm sure you may be aware, this section is used to determine whether or not a Reserved Instance has been fully utilized throughout the month and does not come with associated charges. It will give you a basic snapshot of how many hours these RIs are being utilized throughout a given month (which is a good reason why it might be useful to include the AZs as well). To allocate costs, there are a few options you can use, rather than relying on the section you indicated:\r\n> \r\n> 1. You can allocate the costs to the RI owner\r\n> 2. You can allocate hourly usage by using the unblended rates in the individual line items that are using your RIs\r\n> \r\n> \r\n> I will send this feature request to our EC2 team to see if they can include the AZ for this section of your DBR. Thank you for your patience and please let us know if you have any further questions.\r\n\r\n*The Weather Channel\r\nFeb 20, 2015\r\n01:59 PM -0500*\r\n> To be certain I want to further clarify what you've stated.\r\n> \r\n> To me it sounds like these lines are duplicated charges that also appear as LineItems elsewhere.  It sounds like they serve as a means to express underutilized reservations.  Is that correct?\r\n> \r\n> If that is correct, then can you tell me if there is a line item elsewhere in the CSV that charged 100% of the cost for a reservation month rather than an hourly cost?  The majority of what we have at TWC are HEAVY 3yr which we get charged for regardless of usage.   I need to be able to reconcile full cost.\r\n\r\n*Amazon Web Services\r\nFeb 20, 2015\r\n04:28 PM -0500*\r\n> The items that you included in your first email are basically a summary, including the utilization and the total cost at the bottom of your Detailed Billing Report. It is also broken out by the hour. In other words, both the line items and the summary are two ways of expressing your RI utilization. You were not charged twice, but the Detailed Billing Report includes both views to give you both a summary and a more granular view. In the individual line items, you'll be able to see which specific RI was used, which instance it was applied to and which account owned that instance. The summary will express how much the total RI cost, as well as how many hours it was in use throughout the month. \r\n> \r\n> Regrading your second question, you should find that information within the line items that you provided. In the example that you provided, for an RI that was 100% utilized, you will see something like \"1488.0 hours purchased, 1488.0 hours used.\" For an RI that was not entirely used, you will see something like \"1488.0 hours purchased, 715.00000000 hours used.\"\r\n> \r\n> I hope that makes sense. If anything I said wasn't clear, or if you have any further questions, please let us know.\r\n\r\n*The Weather Channel\r\nFeb 23, 2015\r\n10:33 AM -0500*\r\n> So this stumps me a little bit... let me explain why.  \r\n> \r\n> I wrote a very simple python script which takes each row of the csv that includes \"LineItem\" in the \"RecordType\" column.  This includes both the \"hour-by-hour\" charges per account, and the aforementioned \"utilization\" rows that we discussed earlier in this ticket.  So here's the rub, the total comes out to be the same total that is printed at the end of the bill.\r\n> \r\n> You can find the py script here: https://gist.github.com/ckelner/962b9e52db11fc73cf68\r\n> \r\n> Here's a run on our january bill:\r\n> $ python aws_csv_reader.py 013328811177-aws-billing-detailed-line-items-with-resources-and-tags-2015-01.csv \r\n> Rows processed:  17229196\r\n> LineItem UnBlendedCost total:  XXXXXX.XX <redacted for github>\r\n> \r\n> And from the AWS billing console it reports: $XXX,XXX.XX <redacted for github>\r\n> \r\n> So I dug out the \"utilization\" lines from the csv and created a smaller csv so these could be examined.  That csv can be seen here: <redacted for github> -- as you can see the script I wrote will pick up those line items just fine, and pull the unblended cost for those lines.\r\n> \r\n> This smaller csv was created using:\r\n> $ head -n 1 013328811177-aws-billing-detailed-line-items-with-resources-and-tags-2015-01.csv > jan_utilization.csv\r\n> $ grep -Eiw 'HeavyUsage.*purchased' ~/kelner_temp/013328811177-aws-billing-detailed-line-items-with-resources-and-tags-2015-01.csv >> jan_utilization.csv \r\n> \r\n> Without those values, the total is not correct.\r\n> I wrote a second script (seen here: https://gist.github.com/ckelner/878d653928d476d22fb9) that excludes those line items (as seen in the csv provided from dropbox) from the total.  The total then comes out to:\r\n> \r\n> $ python aws_csv_reader_v2.py 013328811177-aws-billing-detailed-line-items-with-resources-and-tags-2015-01.csv \r\n> Rows processed:  17229196\r\n> LineItem UnBlendedCost total:  XXXXXX.XXX <redacted for github>\r\n> \r\n> So this value is $14,000 off from the correct total.\r\n> \r\n> So can you help me understand why that is?  As I understand it, counting those \"utilization\" lines would effectively have me counting resources twice, but given that the total is the same, I don't see how that is possible?\r\n\r\n*Amazon Web Services\r\nFeb 23, 2015\r\n04:02 PM -0500*\r\n> Thank you again for reaching out.\r\n> \r\n> We do want to give a direct, clear and concise answer regarding this to make sure we can figure out the issue and come up with an accurate solution.\r\n> \r\n> I do apologize for the length of time on this case, but I will coordinate with the first Concierge Agent to properly research and clarify what can be done. So you are aware, we are escalating to our internal Billing Platform service team to make sure we know exactly why this is occurring.\r\n> \r\n> Any recommendations made by the team will be passed along to you as soon as we receive them.\r\n> \r\n> We strongly appreciate your patience through this, and please let us know if there is anything further we can assist with at this time.\r\n> \r\n> Will reach out again soon!"}}