Virtualbox

Running linux as a non root user in virtualbox. To mount VirtualBox shared folder on Linux and access everything within the shared folder as the user, in this example htpcbeginner, you will have to manually add the user to vboxsf group...then log out and log in.

PostgresSql option for creating table like another table

In MSSQL there is an easy, code-based, way to create a table with the same DDL and data as another table.
This is simply by running a query similar to this:

select * INTO "NEW TABLE" FROM "SOURCE TABLE"

I needed to do something similar in PostgreSQL and found this to be a nice way to recreate a table using another as a templet

CREATE TABLE TESTTABLE
AS
  SELECT *
  FROM "SOURCE TABLE"
  WHERE false;

psycopg2 on MacOS

I have recently been having some issues with install the python module psycopg2 on a mac os Mojave. No matter what modules I installed in the virtual environment, the pip install of psycopg2 would not find a suitable candidate.

I tried all sorts of ways to get around this, like installing the entire Postgresql engine via brew, all to no avail.

Eventually, I found this pip module (which I should have found earlier to be honest if I had been more attentive :) )

pip install psycopg2-binary

Locale issues when installing MSSQL on Redhat 8

Today I was installing Microsoft SQL Server on Redhat 8, followning these instructions:
Install Link

During the setup of the server, I was having issues due to unknown locale UTF 8

I had to set the locale to a specific one, which I did using the following:

export LC_CTYPE=en_ZA.utf8

After that, it worked fine.

MicroStrategy Metadata Version

This is an example of how to tell the version of a metadata backup, using a SQL query:

For MySql:

select
CONCAT((SELECT LEFT(PROP_VAL,2)  FROM DSSMDSYSPROP WHERE `NAME` = 'Version'),'.',(select LEFT(PROP_VAL,2)  FROM DSSMDSYSPROP WHERE `NAME` = 'SCHEMAVERSION')) AS `Schema Version`;

Or SELECT * FROM DSSMDSYSPROP ; (This will show all properties)

Postgres -- Tables and Column Length

To generate the following outoput in PostgresSQl

ID	Table Name	Schema	Count of Columns	TotalRows
1	Test1	public	4	600
2	Test2	public	12	1268

The following code works well:

SELECT   t.table_name, t.table_schema, count(columns.column_name), n_live_tup AS totalRowCount  FROM   information_schema.tables t   LEFT OUTER JOIN  information_schema.columns on  columns.table_name = t.table_name   LEFT OUTER JOIN  pg_catalog.pg_stat_user_tables pc on t.table_name = pc.relname   WHERE t.table_schema ='public' GROUP BY t.table_name, t.table_schema, n_live_tup

Python Development Gui - IDLE on CentOS

Many python books, articles and learning guides refer to a python development GUI named IDLE. Why its recommended is that it comes with features that many other text editors - such as VI and gedit - have such as highlighting and closing (where it shows if you have not closed off a string for example) but IDLE also comes with the ability to show you where you may be making mistakes.

I have been working in VI, gedit, notepad++ and even the python shell of late. While all are usable - and yes I still revert to VI a lot - with all the references being made to IDLE I thought I would take a look.

I first tried it on Windows 7 - all good and an easy install as it is included in the installer exe.
I then tried it on Ubuntu - again, easy to install as you can just search for it in the software installer.

Alas, I then tried CentOS and came up short. In fairness I was using the cmdline more, as I often do in CentOS, but I could not find a package named IDLE.

For any others who need IDLE on CentOS and also didnt find many articles about it, its really very easy.

bash#: sudo yum install python-tools

its as easy as that!

Just out of interest

While researching a SQL date problem, I came across this and as it is quite interesting and informative, I have decided to post it here as well.

This is not my or the Research Team's work, so credit is given to the original source:
http://www.karaszi.com/sqlserver/info_datetime.asp#RecommendationsInput


Why is 1753 the earliest date for datetime?
Good question. It is for historical reasons. In what we sometimes refer to as the western world, we have had two calendars in modern time: the Julian and the Gregorian calendars. These calendars were a number of days apart (depending on which century you look at), so when a culture that used the Julian calendar moved to the Gregorian calendar, they dropped from 10 to 13 days. Great Britain made this shift in 1752 (1752-09-02 were followed by 1752-09-14). An educated guess why Sybase selected 1753 as earliest date is that if you were to store an earlier date than 1753, you would also have to know which country and also handle this 10-13 day jump. So they decided to not allow dates earlier than 1753. Note, however that other countries did the shift later than 1752. Turkey, for instance, did it as late as 1927.
Being Swedish, I find it a bit amusing that Sweden had the weirdest implementation. They decided to skip the leap day over a period of 40 years (from 1700 to 1740), and Sweden would be in sync with the Gregorian calendar after 1740 (but meanwhile not in sync with anyone). However, in 1704 and 1708 the leap day wasn't skipped for some reason, so in 1712 which was a leap year, they inserted yet an extra day (imagine being born in Feb 30!) and then did the shift over a day like everyone else, in 1753.

Powershell and special characters

For all those that are writing powershell scripts. I came across a unique scenario where I had to call an external application - in this scenario curl - and had to pass certain details to the application via a powershell script.

The character is a simple :

(Note: for all those wondering why I don't use build in powershell commands for downloading files from the internet, this scenario is to upload specific files to a HTTP site where a username and password is required)

Curl requires that username and password are submitted like this

<username>:<password>

Unfortunately, this caused problems with the powershell script where it would only read the username and not the password. After trying all sorts of solutions, such as ' " / \ [] {} before the : I finally found the solution!

The solution was to place the : in ' '

example: curl.exe -v --user gtlg':'randompassword --data-binary @filename https://somesite.com

Linux AD Integration with Powerbroker

Linux and Window Authentication is a topic that comes up often and there are various ways of achieving this. We have been testing the Powerbroker Identity Services Open Source Edition (PBIS) and have found it very easy to install and use.

PBIS comes with a GUI and CLI installation interface. In order to test it, we decided that the CLI would be the better approach and give us a better insight into the tool. For the test we used Centos 6.0 and Fedora 16 as the Linux hosts.

The installation is simple and straight forward. Just type the following cmd and answer yes to the questions:


BASH# sh pbis-open-6.5.561.linux.x86_64.rpm.sh


The installation may require a reboot, but we found it was not always necessary. The default installation will create the directory /opt/pbis for the installation.

Once installed its time to add the linux host to the active directory domain and gain, using CLI, this is easily done. For this example we were joining the linux host to the domain example.com. We used the account administrator but this can be any account with privilege on the active directory domain.


BASH# /opt/pbis/bin/domainjoin-cli join EXAMPLE.COM administrator


That completed the installation and the linux host was then part of the domain. A straight forward and easy installation. To test we then logged in via SSH with the following:


BASH# ssh "example\gtlg"@192.168.1.2


by default pbis however does not use the BASH shell and creates the home diretory as follows:

/home/local/EXAMPLE/gtlg

This was not in-line with our normal standards and led us to dig a bit further into pbis to see how we could make it match our policies. Again pbis turned out to be very easy to use and very straight forward in editing the configuration. To change the default settings requires simple cmd's from pbis to edit the pbis "registry".

To change the default shell to the BASH shell was as follows:
in /opt/pbis/bin: ./config LoginShellTemplate /bin/bash

To change the default home directory is match this standard - /home/EXAMPLE/gtlg
in /opt/pbis/bin: ./config HomeDirTemplate %H/%D/%U

%H = /home
%D= Domain Name
%U = Username

At this point we had working logins via console or ssh, the use of the BASH shell by default and the directory structure that we wanted. Our last test was to see how to add users to groups and visudo. Again we are pleased to say that it was very simple.

in /opt/pbis/bin: use the command enum-groups and find a group in Active Directory that you want to use. In our case we used the group Domain Admins which was displayed by pbis as EXAMPLE\domain^admins. Adding this to our linux host was as simple as editing /etc/group and adding EXAMPLE\domain^admins into a group.

adding a group to sudo is just as easy and is as follows:

EXAMPLE\\domain^admins       ALL=(ALL)      ALL


Powerbroker Identity Services is great and works exactly as described.We have tested the file permissions and sudo rights successfully.

Cheap NAS Storage - Part 1

A client has a requirement for cheap storage that will be used for backups, file storage, ISO/Image storage and VM Storage. The client has a small budget and therefore cannot afford to purchase a propriety solution due to budget constraints. Our team is going to be investigating this and to start off with began research into creating a custom NAS solution with inexpensive hardware and opensource products.

There a few solutions available on the net for this. The most notable being FreeNAS and Openfiler which both provide multiple services, a easy to use GUI and provide some HA and Replication. During the research phase we also came across this custom built solution:

http://eugenyho.blogspot.com/

Seeing fantastic work from FreeNAS, Openfiler and now this blogger is great to see and shows how much cheap storage is a requirement for other businesses too. The only issue is that these solutions do not seem to be scalable and only provide mirror functionality between two servers. As with most IT staff, we always want more :)

EugenyHo attempted to find a scalable solution using GlusterFS but was not able to do so. This project we will be embarking on will be to expand on the original idea and see if there is a way to approach a scalable, HA enabled SAN solution through other methods (without being overly complex)

Openswan (ClearOS) - Cisco PIX IPSEC VPN