Pacific Power Hourly Energy Savings

On the North West coast of the United States is a company called Pacific Power.  They are the government approved power provider.

There is a special program they have that purports to incentivize people to shift their power usage to off peak periods.  This is a very good idea to help balance out the power grid, and I am a huge fan of the idea.  However, it does cause a great deal of work for those who shift their usage and they do deserve to be incentivized to do so.

Unfortunately, it turns out that the program Pacific Power runs is a scam.  Their documentation, and their representatives who try and explain the documentation, claims that the normal household is charged 3 cents per kilowatt hour.  Once switched to the new program the household will be charged 6 cents per kilowatt hour for on peak usage, and only 1 cent per kilowatt hour for off peak usage.  This seems reasonable, and a hard working person should be able to save some money by changing their habits fairly drastically.
( One of the documents actually says it will be a negative 1 cent, the reps just seem to explain it poorly )

The reality of this program is far different from what is advertised.

In reality, Pacific Power does not stop charging the household for anything, they continue charging the standard electrical fees, and then they tack on an ADDITIONAL 6 cents per kilowatt hour for peak usage, and a -1 cent per kilowatt hour for off peak usage.

So, by completely revamping your lifestyle, you might save $1-2 a month if you are lucky, if you slip then you will suddenly be paying a massively inflated bill on top of the normal electrical bill you were already paying.

Microsoft TFS Server Custom SQL Query

Microsoft's TFS 2017 has come a long ways, but is still a pretty terrible product.  That said, it is still the best option for many things as a Microsoft developer.

One thing I have seen a lot of people on the Internet asking about is various ways to get at the TFS information stored in SQL.  Microsoft obviously refuses to answer these questions just pointing people to their APIs.  However, sometimes it is just easier to run a quick SQL query.

For those people who are just wondering where Microsoft is hiding all the various fields, I have spent a good bit of time tracking some of them down.  Remember this code is for SQL 2017 and is unlikely to work for other versions, but it might point you in the right direction.  All this is is one of my queries with some comments, I will leave it up to you to go look at the tables and see how I use them to get the data.

The very last join I do is for a custom HTML field that I created in TFS, a very similar join on that table could be used to get other HTML fields as well, such as comments or description fields.

// Dashboards.tbl_Widget stores the Dashboard settings, you can hack changes here
// dbo.QueryItems has the settings for each query
// tbl_ChangeSet stores the change sets
// vw_WorkItemComments stores all the work item comments, including comments generated when changesets are submitted
// tbl_Version holds actual file references, can be linked to changesets using VersionFrom column
// WorkItemFiles are links between workitems and the changesets
// dbo.WorkItemLongTexts stores the long texts from bugs, like the main body
// dbo.tbl_TagDefinition contains the tag names themselves joins dbo.tbl_PropertyDefinition.Name on TagId
// dbo.tbl_Field contains the descriptions for the FieldId's from tbl_WorkItemCustomLatest
// dbo.tbl_PropertyDefinition string guid table join on dbo.tbl_PropertyValue on PropertyId integer value, ArtifactId is lookup key which is WorkItemId number converted to hex

select [System.Id] ID, l_title.[TextValue] [Title], l_approvedby.[StringValue] [ApprovedBy], l_valuearea.[StringValue] [ValueArea], l_priority.[IntValue] [Priority], l_waitingon.StringValue [WaitingOn]
-- Sub Select for Tags
,(select substring((select distinct ', ' + td.[Name]
from dbo.tbl_PropertyValue pv
Inner Join dbo.tbl_PropertyDefinition pd on pv.PropertyId = pd.PropertyId
Inner Join dbo.tbl_TagDefinition td on pd.[Name] = 'Microsoft.TeamFoundation.Tagging.TagDefinition.' + cast(td.TagId as varchar(100))
    Where pv.InternalKindId = 16 And pv.ArtifactId = CONVERT(VARBINARY(8), l.[System.Id])
for xml path('')), 3, 9000)) as Tags
,l_deploy.Words DeployNotes
from vw_denorm_WorkItemCoreLatest l
    inner join vw_denorm_WorkItemCustomLatest l_title on l.[System.Id] = l_title.Id And l_title.FieldId = 1
    left join vw_denorm_WorkItemCustomLatest l_approvedby on l.[System.Id] = l_approvedby.Id And l_approvedby.FieldId = 10133
left join vw_denorm_WorkItemCustomLatest l_valuearea on l.[System.Id] = l_valuearea.Id And l_valuearea.FieldId = 10055
    left join vw_denorm_WorkItemCustomLatest l_priority on l.[System.Id] = l_priority.Id And l_priority.FieldId = 10029
    left join vw_denorm_WorkItemCustomLatest l_waitingon on l.[System.Id] = l_waitingon.Id And l_waitingon.FieldId = 10134
    left join (select Max(AddedDate) AddedDate, Id from WorkItemLongTexts Where FldID = 11135 Group By Id ) l_deploy_grp on l.[System.Id] = l_deploy_grp.Id
left join WorkItemLongTexts l_deploy on l.[System.Id] = l_deploy.Id And l_deploy.FldID = 11135 And l_deploy_grp.AddedDate = l_deploy.AddedDate
Where
l.[System.IsDeleted] = 0
    And l.[System.State] = '20 Testing'
    And l.[System.AreaPath] = '\VerizonRAB'
And ( l.[System.WorkItemType] = 'Feature' Or l.[System.WorkItemType] = 'Bug' Or l.[System.WorkItemType] = 'Task' )

WatchGuard FireBox Phone VPN Configuration

We recently migrated from a very old Cisco ASA firewall to a couple of WatchGuard FireBox M200 devices.  So far my assessment is that they are very capable devices with lots of features, but inadequately documented and the company has poor support.

Part of my negative experience in regards to support may be from buying through a third party re-seller.  WatchGuard does not want to talk to you directly, they want to talk to your re-seller.

Most of the configuration and setup went fairly smoothly.  Due to poor documentation there were some gotcha's when setting up active directory authentication in regards to case sensitivity.  The little documentation WatchGuard does have is well formatted and reads nicely.  Unfortunately, due to there being so little of it you end up trying to reference the wrong documentation to help fill in the blanks for missing documentation.  Many companies try and fill this gap by providing help forums that they monitor and respond to questions on which then fills in all the missing information.  WatchGuard seems to be missing a good implementation of this, and they do not seem to respond when people reach out to them.

You can see a perfect example of all of WatchGuard's issues by looking at their android vpn app in the Google Play store.  The app is poorly written, functions horribly, and they ignore all the people providing negative feedback who are essentially begging for help.

Towards the end of my own WatchGuard configuration I intended to setup an IPSec configuration for phones to connect to as indicated in their documentation.  Unfortunately their own phone vpn client is utterly worthless leaving you to try and find something else.

I did use WatchGuard's iPhone configuration instructions to get the native Android VPN client to connect.  Unfortunately it ended up having two major issues that caused me to dump it.  The first is that WatchGuard has a limitation or perhaps a bug in it's IPSec implementation for Active Directory authentication.  As near as I can tell, either SSL or IPSec can authenticate via Active Directory but not both.  It is possible that by deleting the SSL config, setting up IPSec, then re-creating the SSL config I could get around this bug, but it was not worth attempting at the moment.  When using AD authentication in the configuration I had setup, it would connect for a few seconds, then disconnect.  Which tells me there must me some sort of timing mismatch.  You would think that would be simple to adjust and overcome, but again the lack of documentation means I either have to pay for support or just deal with it for now.

The second problem, is that even when using the WatchGuard database native authentication, the native client on android does not seem to hold in a connection when the phone goes to sleep.  So if I set my phone down for a few minutes and come back to it I have to re-connect every time.

Fortunately WatchGuard does support OpenVPN.  So I was able to download a third party client that works with the existing SSL configuration successfully.  Not ideal, but it works for now.

- 1: download the client.ovpn attachment to your phone.  You can find this by going to the ip address of your firewall in a browser, authenticate, and download the "Mobile VPN with SSL client profile".
- 2: Open up the App store on your respective device and find the app OpenVPN Connect (not just OpenVPN, it has to say Connect).
    o Install the app and open it.
- 3: Click on the menu in the upper right, the three dots.
- 4: Click the menu item Import Profile from SD card.
- 5: navigate to the folder containing the client.ovpn attachment that you downloaded in step 1.  For me it was in my Download folder.
- 6: Click on the client.ovpn file and click the Select button.  It may be confusing because the client.ovpn line you selected will not highlight.
- 7: If you chose the correct file you should now see an IP address in the OpenVPN Profile area.
    o Enter your Active Directory username and password into the provided fields.
    o Click Connect