Delete infinite rows and columns in MS Excel / Google Sheets

Excel is a pretty powerful tool, but in order to get the most of it, you need to customize your sheets.
By default it might have so many rows and columns that you might want to get rid of.
This is how to do it

•  Ctrl+Shift+Right arrow to select all columns on the right from the current one
•  Ctrl+Shift+Down arrow to select all rows below the current one

Then just simply chose "Delete columns"or "Delete Rows".

That's it!

References:

• https://www.got-it.ai/solutions/excel-chat/excel-tutorial/delete-columns/how-to-delete-columns-and-rows-in-google-sheets-and-excel

AWS: Get AWS AccountID from CLI

Here's the way to quickly find your AWS details, including account ID, from CLI

aws sts get-caller-identity

To get AccountID only, use queries and customize output text

aws sts get-caller-identity --output text --query 'Account'

That's it!

References:

• https://www.edureka.co/community/9846/quick-way-to-get-aws-account-number-from-the-cli-tools
• https://docs.aws.amazon.com/cli/latest/reference/sts/get-caller-identity.html

Properly configuring an external monitor via HDMI (disable HDMI overscan)

My primary monitor is having some bad times, so I needed to connect my old good backup monitor. Tried both ports, but picture looked cropped, colors were crazy and I was almost mad. Tried some OS software, but result was pretty crappy.

Then, eventually, after reading one of the guides from software found that HDMI overscan needs to be disabled. Some newer models might automatically detect what is conencted, and set proper mode, disabling HDMI overscan, but as I told, my backup device is pretty old. Checked its settings and did not find anything. Then finally googled a solution - I needed to rename source name in TV settings! Menu => Source List => Edit name. Renamed it to "DVI PC" (chosen from list of predefined names), and finally - picture became nice and properly sized :)

References:

• https://support.displaylink.com/knowledgebase/articles/611877
• https://www.engadget.com/2010/05/27/hd-101-overscan-and-why-all-tvs-do-it/
• http://www.storageforum.net/forum/showthread.php/8905-How-to-Disable-overscan-on-a-Samsung-quot-SMART-quot-tv-solved

Pasting text without formatting (Windows)

Everyone is working with text and using clipboard nowadays. And I often need to paste text without formatting. Shame to me, but I've used a notepad as a mediator. But today decided to search for a shortcut and found it pretty quickly

Ctrl+Shift+V will do the trick on Windows.

References:

• http://blog.gilbertconsulting.com/2008/08/paste-without-formatting.html

WiFi settings backup on Windows 8

One of things that I hate is when you ask to provide you WiFi password in cafe but they simply type it on your device and connect instead of providing the password itself. And on some devices it may be really tricky just to find out existing settings.

Windows OS offers pretty good and simple way for having all WiFi networks dumped and backed up. You can easily find credentials to any network you've been connected before and use it on your another device.

WiFi_Backup.sh

set BACKUP_DIR="C:\Backup"set TMP_DIR="%BACKUP_DIR%\WiFI"set CUR_DATE=%date:~-4%_%date:~3,2%_%date:~0,2%mkdir %TMP_DIR%cd %BACKUP_DIR% echo Performing WiFI backup...netsh wlan export profile key=clear folder="%TMP_DIR%"
echo Adding to archive..."c:\Program Files\7-Zip\7z.exe" a -t7z "%BACKUP_DIR%\WiFi_Backup_%CUR_DATE%.7z" "%BACKUP_DIR%\WiFi\"
echo Removing files...del /F /Q "%TMP_DIR%\*.*" rmdir %TMP_DIR%

References:

• https://winaero.com/blog/how-to-backup-your-wireless-network-profiles-in-windows-8-1-and-windows-8/
• https://stackoverflow.com/questions/1192476/format-date-and-time-in-a-windows-batch-script

WinSCP: General failure

WinSCP is a very nice tool for managing your server content form desktop OS. Meantime it sometimes can provide you very confusing error reports. For example, look at this:

General failure (server should provide error description).
Error code: 4
Error message from server:
Failure Request code: 14 

And who knows what's wrong?! Quick googling show that issue is caused by 100% full drive, so all you need is just clean up some space. And this confusing message is gone :)

References:
http://winscp.net/forum/viewtopic.php?t=1668

Ubuntu: Removing all legacy unused kernels

Sometimes you need to care about each MB of disk space. You are starting to cleanup drives, but there's nothing obviously can be deleted. In such cases you an free-up some space (from dozens to hundreds of MB) by removing all legacy system kernels.
In such case please make sure you are running the latest kernel version
Compare outputs for

uname -r

and

dpkg -l | grep linux-image

Probably you may need to reboot! Then just issue the following command:

dpkg -l 'linux-*' | sed '/^ii/!d;/'"$(uname -r | sed "s/\(.*\)-\([^0-9]\+\)/\1/")"'/d;s/^[^ ]* [^ ]* \([^ ]*\).*/\1/;/[0-9]/!d' | xargs sudo apt-get purge

References:
http://ubuntugenius.wordpress.com/2011/01/08/ubuntu-cleanup-how-to-remove-all-unused-linux-kernel-headers-images-and-modules/

Dropping all databases in MongoDB

Just used mongorestore for database transfer to another server and noticed that resulting database have more records. I may assume mongorestore does not delete existing data. Hence after googling a bit I've found a way to clear all databases first and then didn't have any problems with restore.

So here's the recipe how to drop all databases in MongoDB:

var dbs = db.getMongo().getDBNames()
for(var i in dbs){
    db = db.getMongo().getDB( dbs[i] );
    print( "dropping db " + db.getName() );
    db.dropDatabase();
}

References:
http://stackoverflow.com/questions/6376436/mongodb-drop-every-database

VIM: Syntax highlight for cron files

No one can doubt how much easiness can vi(m) bring to your life :) Very important (and useful) thing is syntax highlight. I just realized that if you edit cron tasks, there is no highligh (excluding /etc/crontab, which has automatic highlight).

So first just open needful file with vim. Don't see helpful highlights, right?

Then just use the following command to make it eye-candy:
:set ft=crontab 

And enjoy the editing :)

Good luck!

References:
http://unix.stackexchange.com/questions/68972/no-syntax-highlighting-when-editing-crontab
http://vimdoc.sourceforge.net/htmldoc/filetype.html

Find last logon time on Windows

The easiest and the best way to get last logon time for particular user's login in Windows:

net user  username | findstr /B /C:"Last logon"

References:
http://www.windows-commandline.com/last-logon-time-of-user/

Resetting a Sandisk Sansa Clip+ player

Freeze - this sometimes happen to hardware! Especially it's critical when you favorite devices stuck. Luckily there's almast always a way to deal with it. THis is how I just restored my stuck Sandisk Sansa Clip+ player:

1. Press and hold the Power/Menu button for approximately 20 seconds.

2. Release the Power/Menu button.

3. Press the Power/Menu button again to turn on the player.

And I can continue enjoying my music, yeah! :)

Reference:

http://kb.sandisk.com/app/answers/detail/a_id/188/kw/reset%20a%20sansa%20player

Nginx - setting different root folder for different subdomains

Just found useful way to make nginx config more flexible and create as many subdomains as you need without a need to change vhost configuration.

Here is this sample config:

server {
        server_name ~^(?.+)\.example\.com$;
        root /www/$subdomain.example.com;
.....

Then just create needful folders in /www/.

References:

http://stackoverflow.com/questions/9233368/nginx-server-configuration-subdomain-to-folder

AWS: IAM policy for renaming S3 objects

Here are the minimal needful list of permissions needed to access, create, delete and rename S3 objects within particular S3 bucket

{
"Version" : "2012-10-17",
"Statement" : [{
"Sid" : "Stmt1000000000001",
"Effect" : "Allow",
"Action" : [
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource" : [
"arn:aws:s3:::bucket-name"
]
}, {
"Sid" : "Stmt1000000000002",
"Effect" : "Allow",
"Action" : [
"s3:PutObject",
"s3:GetObject",
"s3:GetObjectVersion",
"s3:DeleteObject",
"s3:DeleteObjectVersion",
"s3:PutObjectAcl",
"s3:GetObjectAcl"
],
"Resource" : [
"arn:aws:s3:::bucket-name",
"arn:aws:s3:::bucket-name/*"
]
}
]
}

Do not forget to replace  bucket-name with actual name of needed bucket and change Sid for your policies. Feel free to apply this policy to needful group/user/bucket :)

Determining version of PHP MongoDB driver.

This is the probably the simplest way to determine version of PHP MongoDB driver:

php --ri mongo | grep Version 

You'll get the answer immidiately:

Version => 1.4.5

Reference:

 http://stackoverflow.com/questions/19034006/how-to-check-mongodb-php-driver-version

A bit of (idiotic) UI design from MS.

Sometimes I really thinks that a lot of idiots works at Microsoft. Sometimes I even get the proofs :)
Just take a look at this UI of Office365.

No comments ;)

Changing timezone for nginx

Amazing guide on how to change timezone for your nginx server:
http://www.oneminuteinfo.com/2013/06/how-to-change-timezone-of-nginx-web-log.html

Foobar2000:Properties/Automatically Fill Values

That amazing thing!
Allows you to auto generate track fields from filenames in foobar2000

MySQL - formatting output

Hey!
If you need to preserve fancy table output for your mysql queries, use the following switch: -t.

--table, -t
Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

Reference:
http://stackoverflow.com/questions/9745547/get-mysqls-fancy-table-format-as-stdout

Human friendly time format in dmesg

I have wondered is there any way to represent timings from dmesg in easy and clear, human-readable way. Now I have discovered "-T" switch :-)

     -T, --ctime
              Print human readable timestamps. The timestamp could be  inaccu‐
              rate!
              The  time  source  used for the logs is not updated after system
              SUSPEND/RESUME.

So simply issue:  dmesg -T.

And you're done!


References:
http://serverfault.com/questions/366392/how-to-convert-dmesg-time-format-to-real-time-format

RDP client for Linux

Amazing! I have finally managed to found nice RDP client for Linux:

Now enjoying it :)