Archive for category Technology

IPsec VPN Server Auto Setup Scripts

First, prepare your Linux server* with a fresh install of Ubuntu LTS, Debian or CentOS.

Use this one-liner to set up an IPsec VPN server:

wget -O && sudo sh
If using CentOS, replace the link above with

Your VPN login details will be randomly generated, and displayed on the screen when finished.

For other installation options and how to set up VPN clients, read the sections below.

* A dedicated server or virtual private server (VPS). OpenVZ VPS is not supported.


No Comments

Installing and running shadowsocks on Ubuntu Server

Shadowsocks-libev is a lightweight secured SOCKS5 proxy for embedded devices and low-end boxes.

It is a port of Shadowsocks created by @clowwindy, and maintained by @madeye and @linusyang.

Install the the shadowsocks-libev package from apt repository.

sudo apt update
sudo apt install shadowsocks-libev
Save ss.json as /etc/shadowsocks-libev/config.json.

Replace server_port and password in ss.json with your own choices.

Restart the shadowsocks-libev service.

sudo systemctl restart shadowsocks-libev
sudo systemctl status shadowsocks-libev

No Comments

BOBJ InfoView Generate File Name with Date


No Comments

MS SQL Replace Line Break

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

No Comments

Find the first number’s position in a text string with formulas

Select a blank cell where you will return the first number’s position, and enter the formula =MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (A2 is the text cell where you will look for the first number’s position) into it, and then drag its Fill Handle to the range as you need. See below screenshots:

No Comments

MS Excel: Two-Dimensional Lookup (Example #1)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #1.

Question: I’m trying to reference a particular cell within an xy axis chart and can’t find the formula or function that allows me to do so.

For example A1 needs to equal where row 12 intersects column F on a chart.

I know the lookup function can get me a value from a known array of values located in the corresponding column, but I can’t get it to figure from an array of columns. Can you help?

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a VLOOKUP function and a MATCH function.

Let’s look at an example to see how you would use this function in a worksheet:

Microsoft Excel

In the spreadsheet above, we have a listing of products (Oranges, Apples, Bananas, Pineapples, Watermelons) and a listing of quantity columns (5 lbs, 10 lbs, 15 lbs, and 20 lbs). What we want to do is find the correct value based on a product and quantity combination.

In the first case, we want to find the price/lb for 10 lbs of oranges. To find the price/lb, we’ve entered the following formula into cell D17:

=VLOOKUP(B17, $B$8:$F$13, MATCH(C17, $B$8:$F$8, 0), FALSE)

This formula returns the value of $4.80.

In the second example, we are looking for the price/lb for 5 lbs of bananas. We’ve entered the following formula into cell D18:

=VLOOKUP(B18, $B$8:$F$13, MATCH(C18, $B$8:$F$8, 0), FALSE)

This formula returns the value of $1.50.


, ,

No Comments

Answering a virtual machine related question from the command line (1026835)

This issue may occur if:
You do not have vSphere Client.
vSphere Client cannot connect to the host to answer the initial question.
vSphere Client cannot connect to vCenter Server to answer the initial question.
To resolve this issue, run a vim-cmd command that sends the answer for the startup question to the virtual machine.

To run the command:
Check for the question in the vmware.log file in the virtual machine directory. For example:

Mar 30 06:03:52.988: vmx| Msg_Question:
Mar 30 06:03:52.988: vmx| [msg.uuid.altered] This virtual machine may have been moved or copied.
Mar 30 06:03:52.988: vmx|
Mar 30 06:03:52.988: vmx| In order to configure certain management and networking features VMware ESX needs to know which.
Mar 30 06:03:52.988: vmx|
Mar 30 06:03:52.988: vmx| Did you move this virtual machine, or did you copy it?
Mar 30 06:03:52.988: vmx| If you don’t know, answer “I copied it”.
Mar 30 06:03:52.988: vmx|
Mar 30 06:03:52.988: vmx| —————————————-

If you have received the question, run this command to determine the virtual machine ID:

[root@user ~]# vim-cmd vmsvc/getallvms
80 VC4.1-user [datastore1] VC4.1-user/VC4.1-user.vmx winLonghorn64Guest vmx-07

For example, in this output, the virtual machine ID is 80.

Run this command to get the question ID:

[root@user ~]# vim-cmd vmsvc/message 80

You see output similar to:

Virtual machine message 12:
msg.uuid.altered:This virtual machine may have been moved or copied.

To configure certain management and networking features VMware ESX needs to know which.

Did you move this virtual machine, or did you copy it?
If you don’t know, answer “I copied it”.

0. Cancel (Cancel)
1. I _moved it (I _moved it)
2. I _copied it (I _copied it) [default]

In this example the question ID is 12, and there are three choices: 0, 1, 2.

To answer the question, run this command with the virtual machine ID and the Question ID followed by the choice number.

[root@user ~]# vim-cmd vmsvc/message VM_ID 12 2

For example to answer the question with choice 2, run:

[root@user ~]# vim-cmd vmsvc/message 80 12 2

The parameters in order are: 80 is the virtual machine ID, 12 is the question ID, and 2 is the choice number.

Note: In vSphere Client 5.x, if you see Virtual machine message _vmx1, run the command:

[root@user ~]# vim-cmd vmsvc/message VM_ID _vmx1 2
For example to answer the question with choice 2, run:

[root@user ~]# vim-cmd vmsvc/message 80 _vmx1 2

No Comments

Calculating Date Difference and excluding weekends and public holiday in Excel


No Comments

Lookup Within a Table using the CALCULATE Function

PowerPivot VLOOKUP equivalent

The RELATED function makes it easy to pull data from one table to another, but what about looking up a value within a table?  I came across this issue recently when a client wanted to compare two rows of data that was reported between consecutive months.  After some search engine queries and some trial and error, I found that VLOOKUP could be accomplished with four DAX functions, CALCULATE, DISTINCT, FILTER, and EARLIER.  The resulting formula looked like this:


The following is a breakdown of that formula into 4 parts:

1)     (‘Table1’[LookupID])

  1. Within Table1 I created another calculated column called LookupID.  LookupID represented another row’s CalculatedID.  In my case the CalculatedID followed the format YYYY-MM-### where the first four characters were the year the data was reported, the second two characters were the numerical month the data was reported, and the last three characters represented a unique identifier for the group the data was reported under (called GroupID).  To generate the LookupID column I subtracted the month and year the data was reported by one month using the DATEADD formula (example below).


Then I appended the GroupID field using the CONCATENATE function (example below).

CONCATENATE(FORMAT(DATEADD(‘Table1′[MonthYear],-1,MONTH),”yyyy-mm-“)),’Table1’ [GroupID])

 LookupID now represented the CalculatedID of another row, and this other row contained the previous month’s data.

2)     ‘Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])

  1. The EARLIER function conducts a table scan of Table1 and finds instances where LookupID is equal to the CalculatedID for the current row.

3)     FILTER(‘Table1’,’Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])))

  1. In this equation Table1 is filtered by the statement described in part 2.  Therefore only the rows meeting this statement are returned.

4)     CALCULATE(DISTINCT(‘Table1′[Value])…

  1. This part of the equation returns the distinct values for the column “Value” found in the result set returned by the FILTER function described in part 3.

This formula worked well for our client and we were able to compare groups of data between two months and build some nice reports on their SharePoint site.

Excel 2013 and SQL Server 2012

With PowerPivot for Excel 2013 and for SharePoint 2010 environments using SQL Server 2012, you can avoid this lengthy formula by using the new LOOKUPVALUEfunction in DAX.

John Kerski

No Comments

Splitting An Attribute’s details into columns in MicroStrategy

If they are running the reports in Web, there is button under the Data menu titled Toggle Attribute Forms.  I think that might accomplish what you are after.  Not sure if there is a setting for this in Desktop at the grid level though.

No Comments