We at FAIR Consulting Group, primarily use Google Cloud Platform, for our internal infrastructure for various projects and opportunities, and it goes without saying that if proper analysis is not done on the monthly billing, you would end up asking yourself, where is all the money going?
Now GCP does provide an excellent billing dashboard, but can you identify what is one particular thing is costing you, or identify some dangling load balancer in the midst of thousands? or create far more complex queries to satisfy your whims, it is safe to say, there is room for improvement, why then should we wait for GCP to catch up when we can venture on our own?
So we played around with it a bit and realised there were a couple of places we were throwing money, we were not always able to keep an eye on everything, that was going on in our cloud, somehow if we could glimpse all the information at once.
We enabled the feature of export billing details to GCP Big query, which can be found here. After that, we waited so data could be accumulated to make queries a bit more interesting. I will try to break the queries down so you can replicate and evolve as you see fit for your own environments and infrastructures.
First 3 are the ones on the top right corner Month to Date, Quarter to Date and Year to Date spend;
All three of them are almost same, a simple SQL sum operation of the cost, the manipulation was done with the date, get the sum of each duration, EXTRACT functions seemed the easiest way to get them, but there were other ways, which you can explore on your own
Then came a simple query which is also available in the GCP dashboard as well, but to have everything in one place is what we are hoping to get.
This one got a bit out of hand had two call couple of functions to replicate something similar to the one found in GCP, we had to use parse the date, we were not able to just extract month out of invoice.month for some reason, we had too first convert it into something acceptable for extraction when we tried direct EXTRACT(MONTH invoice.month), it was not working as expected, but this query has issues, PARSE_DATE(“%Y%m”,invoice.month))- %YI am not sure what this is returning could be 1990 or 2019, but since we are extracting the month we might get away with this…
This was no effort at all but serves a very important purpose when you are tracing a sudden change in cost, it helps you narrow down on the dates you must look at, narrowing down on the usage.
This query is particularly helpful, showing how heavily dependent we are on VMs when we could avail different platform services to perform a similar task.
In the previous query, we realized we were heavily using compute engines, which led me to investigate what in compute engines were costing us so much, not interestingly it was the instance, not the storage. Though it helped me to identify a few Load balancers running uselessly and static IP not assigned to anything, saved a few hundred cents, I hope my boss is not reading this…
One more query which was quite useful, was in which regions are my resources because that load balancer which I was talking about earlier was in the EU region, some intelligent lifeform decided to forget to delete it, and we never go there some were not able to identify in midst of so many projects.
We are FAIR mostly use one internal cloud for demo’s or internal upscaling, and we were able to save quite a lot by creating these custom reports, and it goes without saying, how much it could help those who actually are using at an enterprise level, maybe this is what FinOps is all about, I was quite impressed with the level flexibility available by GCP to satisfy my curiosity, And I plan to keep these custom reports running for while internally so managers in FAIR, could keep track of the projects under their supervision and how much are they costing them.
And if you wish to see the report in action, please click this link to view, and drop feedback and suggestions to make it better.