Ben Hamilton

How to

Add 2 Years to Date in Excel

I wanted to add two years to a date in Excel. This is the formula you need

Hat tip to www.mrexcel.com, assuming your original date is in column A.

=DATE(YEAR(A1)+2,MONTH(A1),DAY(A1))

| Tagged | Comments Off on Add 2 Years to Date in Excel
General

Excel DATEVALUE Function

When a Microsoft Excel spread sheet has a column of date data in this format:

Nov 24 1995

Then the Excel function you’ll find useful is DATEVALUE

DATEVALUE function.

This formula here:

=DATEVALUE((TRIM(MID(R2,5,2))&"-"&TRIM(LEFT(R2,3))&"-"&TRIM(RIGHT(R2,4)))

Basically grabs the Day, then the Month, then the Year from the column, then converts it to a serial number that Excel recognises as a date. Then format this to display however you want it to.

| Tagged | Comments Off on Excel DATEVALUE Function
General

Find JIRA Issues with Attachments

When you just know you’ve attached a file to a JIRA issue, but can’t find it, this is the search to do in Issues in JIRA

Replace PKEY with your project key.:

project = PKEY AND type != Epic and attachments is not EMPTY ORDER BY Status ASC
| Tagged | Comments Off on Find JIRA Issues with Attachments
General

SSH and Private Key usage on Mac OS X

It’s very important that your private keys are kept secure and away from prying eyes.

A good place to keep your private keys is in the folder ~/.ssh as this makes it easier to manage (this is a subfolder of your home folder).

You then want to set the permissions of the private key file to 600.

chmod 600 ~/.ssh/privatekeyfile.pem

Then you can ssh to the server from the command line using:

ssh -i ~/.ssh/privatekeyfile.pem.pem root@fqdn -p 22
| Tagged | Comments Off on SSH and Private Key usage on Mac OS X
General

Tufte WP Theme

I’m currently testing

This is a test side note.
(as at Thursday 19 May 2016) a WordPress theme Naturaily/tufte-wp-theme that uses the CSS from edwardtufte/tufte-css. You can read more about it

This is a second test side note.
from Dave Liepmann.

| Comments Off on Tufte WP Theme
SQL, SugarCRM

When SugarCRM Case Closed

Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed.
Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited.

select
  CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
  accounts.name as 'account',
  cases.case_number,
  cases.name as 'case_id'
from
  cases_audit
  join cases on cases.id = cases_audit.parent_id
  join accounts on accounts.id = cases.account_id
where
  cases_audit.field_name = "status"
  and cases_audit.after_value_string = "Closed"
order by
  accounts.name asc,
  cases_audit.date_created desc;

Note, you might get more then one row returned per case if the case was re-opened, also my code above assumes a timezone of UTC+10

I happen to be in the UTC+10 timezone :).

| Tagged , | Comments Off on When SugarCRM Case Closed
How to, Productivity

Export Evernote Notes to Apple Notes

Larry Salibra has a post on how to move notes from Evernote to Apple iCloud Notes.

His Applescript does a fantastic job of this, the only issue I had was that my iCloud account was named differently to ‘iCloud’, all I did to resolve that was to rename my account in Apple Preferences > Accounts back to iCloud while I did the export.

Found this via ipadinsight.com.

Now I’m as happy as Larry :)

| Tagged , , , | Comments Off on Export Evernote Notes to Apple Notes
*nix, How to, Productivity

Get the Weather From the Command Line

On Mac OS X or Linux (in fact anywhere you get the finger command) you can quickly get the weather.
Jared, in our office here, just showed this to me, neato! :)


finger brisbane@graph.no

I particularly like the one line weather report


finger o:brisbane@graph.no

graph.no

To get more info on how to use this, run this from your command line:


finger @graph.no

| Tagged , , | Comments Off on Get the Weather From the Command Line
*nix, How to

Find Process to Kill Process

Need to find a process so you can kill it?

Find the process first, replace NAMEOFPROCESS with the name or part thereof of the process you’re trying to find:

ps -ax | grep NAMEOFPROCESS

The first column lists the process id.

Then kill the process, replacing PROCESSID with the actual number of the process:

kill PROCESSID
| Tagged , , | Comments Off on Find Process to Kill Process
*nix, How to

Find an IP address in files

Ever been looking for a config file that contains an incorrect IP address?

If the IP address you’re looking for is 8.8.8.8

Then this command is for you:

find . -type f -exec grep -l 8.8.8.8 {} \;

It’ll find all occurrences of 8.8.8.8 that appear in files in the current directory and folders below it. With that short list you should be able to find where you need to edit.

| Tagged , | Comments Off on Find an IP address in files
Productivity

Binge Learning is Failure

Good info from Scott Young:

A great deal of psychological research shows that studying in a burst is less effective than study sessions spaced out over time. Blogs naturally embody the latter method, dripping out ideas over weeks and months instead of in a burst.

Bolding mine.

This aligns with what I was listening to on  Tim Ferris’s podcast where he talks with Luis von Ahn where Luis says that users of Duolingo get best results with 20-30 minutes per day rather than bingeing.

| Comments Off on Binge Learning is Failure
General, Hardware

Olixar Universal Bike Phone Mount

I commute to work every day on my motorcycle, it’s a 52 kilometre trip each way. Wearing my headphones, Waze lets me know of any accidents or hazards ahead but because the phone is in my jacket pocket, I only hear such warnings, I don’t see them. It would be nice to see the Waze screen.

I was talking with the folks over at MobileZap, who said I should try out a Olixar Universal Bike Phone Mount on the bike. So for the last month I’ve been quite happily riding around, able to see the screen with an easy glance. An added side benefit is that when I’ve received a phone call, I can see who it is and decide if I’ll pull over and talk or not. Previously I’d just ignore all phone calls whilst riding.

photo of the Olixar bike phone mount

The holder itself I thought looked a bit flimsy, however it has held up really well while I’ve ridden through both really hot days (34°C) and heavy rain.

After placing the phone between the two grippy holders, simply squeeze them together and the phone is securely held. I’m using an iPhone 6 in an Otterbox Defender case and it accommodates the larger case quite well. There is a small fold-out piece at the bottom if you’re worried about it falling out the bottom, but I’ve found I don’t need it, the phone doesn’t slip or move while in the holder at all.

To release the phone, there is a small button on the lower right hand side, pressing it causes the two holders to release their grip. Although I was concerned initially that it wouldn’t hold my phone securely, it turns out that it does hold it really well, I’m now quite confident that the phone won’t come loose and fall out while I’m riding.

If you’re looking for a phone holder to fit to your road bike, I can happily recommend this one.

Thanks to MobileZap for providing this product for me to test out.

| Tagged , , , , | Comments Off on Olixar Universal Bike Phone Mount
General

Set Windows power states from the command line

Use powercfg to set Windows power states from a script or command line.

| Tagged , | Comments Off on Set Windows power states from the command line
*nix, SQL, SugarCRM

Tracking down long running queries in SQL

I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over.

By logging into the MySQL command line, we were able to see that there were some long running queries that never end.

show processlist;

returns


| Id | User | Host | db | Command | Time | State | Info |
+———+———-+——————-+————–+———+——+————–+——————————————————————————————————+
| 2322386 | ssgadmin | 10.21.1.149:58526 | sugarcrm_ssg | Query | 2202 | Sending data | SELECT IFNULL(hr_humanresources.last_name,’’) hr_humanresources_last_name ,l3_cstm.verification_c l3 |

The key part here is that hr_humanresources_last_name has an underscore between the table name when it should be a period i.e. hr_humanresources.last_name. Knowing the table name was enough to tell me it was caused by something to do with SugarCRM that’s running on that server. But we don’t yet know what action caused these queries.

Knowing it was 2202 seconds ago from the time we ran the query we are able to pinpoint a time the action occurred. Looking through the SugarCRM log files did turn up that the error was often caused by a single user, but didn’t show up anything to help us figure out exactly what was the causal issue. Talking with that user, getting them to do what they’d done at that time didn’t turn up anything, the error wasn’t reproducing on demand.

So I turned to looking in the tracker table in the SugarCRM MySQL database for entries around the time of the error. Turns out that there is a Dashlet being loaded, that Dashlet uses a Report.

Each time I load the Dashlet or run the Report I get a corresponding long running query turn up. We’ve found our culprit, and we’re now able to recreate the report.

| Tagged , | Comments Off on Tracking down long running queries in SQL
General

Location of the SugarCRM log file

By default it’s in the same base folder as the SugarCRM application files. But on occasion we’ll move it. Quickly find it’s location by looking in the config.php file. The two relevant lines are:


‘log_dir’ => ‘/var/log/sugarcrm/’,
‘log_file’ => ‘sugarcrm.log’,

If you’re at the command line, then this makes it easy:


grep 'log_dir' config.php

| Tagged , , | Comments Off on Location of the SugarCRM log file
General

Find a file on a linux server

From the command line

find / -name 'index.html' 2>/dev/null

This will find and display all occurances of index.html and all the errors will not display.

| Tagged | Comments Off on Find a file on a linux server
General

Testing 1, 2, 3

Testing 1, 2, 3

Nothing to see, move along.

| Tagged | Comments Off on Testing 1, 2, 3
General

Offtopic: Slackline World Record

ACROSS THE SKY – a world record slackline in the utah desert from Camp 4 Collective on Vimeo

| Tagged , | Comments Off on Offtopic: Slackline World Record
*nix, SQL

How long running are those queries?

At the MySQL commandline, you can enter
show processlist;
and see what processes are running.
If you’re tracking down some problematic code and you’ve got a query (or set of queries) that don’t end, consume CPU cycles and generally bog everything down to a crawl, it can be helpful to know what time you’ve run the command above. Thusly, I now run this:

show processlist; select now as current_date_time;

and it rather nicely tells me the current date/time on the MySQL server, using the Time column from the processlist, you can work out a tad more accurately when a query started, giving you a starting point to look in other logs.
Now those long running sql queries are easier to track down.

| Tagged , | Comments Off on How long running are those queries?
Productivity

Hotkey for New Day One entry from selection

I use Day One to journal. There is a service that creates a new Day One entry from the selection, but it didn’t have a hotkey associated with it. So I’ve set one up.

First (optional) step is to use to make your CAPS LOCK key useful. Big thanks to Brett Terpstra for that, I use that for loads of other shortcuts.

Next step is to add a shortcut for the New Day One Entry With Selection using the instructions to create keyboard shortcuts in the Apple knowledge base.

I used CAPSLOCK D as the hotkey to send whatever I’ve got currently selected to Day One as a new entry, works a treat.

Note: this is ^⇧⌥⌘D using the optional first step, so it is the same as pressing Control, Shift, Option, Command, D all at the same time.

| Tagged , | Comments Off on Hotkey for New Day One entry from selection