MSSQL JOIN Selection Queries

/ MSSQL Server /

INNER JOIN: Only records with joing fields in both tables.
LEFT OUTER JOIN: All records from table 1 and only matching fields from table 2 that match on join field.
RIGHT OUTER JOIN: All records from table 2 and only matchin records from table 2 that match on join field

Below an example…

SELECT DISTINCT [Database].[dbo].[Table1].[Field1] AS [Table1-Field1]
,[Database].[dbo].[Table1].[Field2] AS [Table1-Field2]
,[Database].[dbo].[Table2].[Field3] AS [Table2-Field3]
FROM [Database].[dbo].[Table1]
INNER JOIN [Database].[dbo].[Table2] ON [Database].[dbo].[Table2].[Field0] = [Database].[dbo].[Table1].[Field0]
WHERE [Database].[dbo].[Table1].[Field1]  [Database].[dbo].[Table2].[Field3]
ORDER BY [Database].[dbo].[Table1].[Field1],[Database].[dbo].[Table1].[Field1];

 

MSAD Move FSMO Roles

/ MS Windows Server /

Commands or tools to move or view Active Directory Flexible Single Master Operations roles.

Schema Master (1 per forrest)

regsvr32 schmmgmt.dll
mmc -> Schema

Domain Naming Master (1 per forrest)

AD Domains and Trusts

RID Master (uitgave SID’s) (1 per domain)

AD Users and Computers

Infrastructure Master (AD infrastructuur) (1 per domain)

AD Users and Computers

PDC Emulator (Time Server, log-on NT4 clients, Password chenges) (1 per domain)

AD Users and Computers

 

Netwerk IPv4 Subnetting

/ Other / 
max aantal subnetten: 2^n (-2) // n = aantal masked bits *subnet* mask
max aantal hosts in subnet = 2^n -2 // n = ongemaskeerde bits

next multiple of 8 = next mult. of 8 greater or eq. to CIDR netmask not.
interesting value = (next multiple of 8) / 8

incremental value = 2^ (next multiple of 8 - CIDR netmask notation)

bijv. 192.168.10.50/27:
incremental value = 2^(32-27) = 2^5 = 32
interesting value = 32 / 8 = 4
netwerkadressen: 192.168.10.0, 192.168.10.32, 192.168.10.64, enz.
broadcastadressen: 192.168.10.31, 192.168.10.63, 192.168.10.95, enz.
netwerk 192.168.10.32
broadcast 192.168.1.63 (192.168.10.(64-1))

bijv. 10.6.127.255/14:
incremental value: 2^(16-14) = 2^2 = 4
interesting value = 16 / 8 = 2
netwerkadres: 10.4.0.0
broadcast: 10.7.255.255 ( 10.(8.0.0-1)

 

OpenSSL Certificate Commands & Examples

/ GNU/Linux /

Below script creates al selfgesigned certificate from a private key and removes the password from the key so you can you can use the key-pair in Apache.

# Create Key and Certificate Signing Request (option -des3 creates a triple des encrypted key)...
openssl genrsa -des3 -out server.key 4096
openssl req -new -key server.key -out server.csr

# Remove password from key and sign certificate with key... 
cp server.key server.key.org
openssl rsa -in server.key.org -out server.key
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

# Re-encrypt private key...
openssl rsa -des -in server.key.org -out server.key
openssl rsa -aes256 -in server.key.org -out server.key

For ease you can add all key and signing options to a config file. This way you can also add a subjectAlternate to the certificate.

>openssl req -new -config server.cnf -key server.key -out server.csr

# server.cnf #
[ req ]
default_bits = 4096
prompt = no
encrypt_key = no
distinguished_name = dn
req_extensions = req_ext

[ dn ]
C = NL
O = Totietoot
CN = examplefqdn.totietoot.nl

[ req_ext ]
subjectAltName = DNS:examplefqdn.totietoot.nl, DNS:examplealtname.totietoot.nl

Use the following command to convert the key-pair to pkcs12 format.

openssl pkcs12 -export -in server.crt -inkey server.key [-name tomcat] -out server.p12 -CAfile chain.pem -caname root -chain

Convert pkcs12 file to java keystore (jks):

keytool -importkeystore -deststorepass <password> -destkeypass <password> -destkeystore server.jks -srckeystore server.p12 -srcstoretype PKCS12 -srcstorepass <password> -alias <name>

Decode a certificate request or a x509 certificate:

openssl req -in server.csr -noout -text
openssl x509 -in server.crt -noout -text

Convert a PFX file to PEM-format (single file)…

openssl pkcs12 -in server.pfx -out key-n-certs.pem -nodes

Check certificate and connection using openssl…

openssl s_client -showcerts -connect f.q.d.n:1234
openssl s_client -starttls smtp -showcerts -connect f.q.d.n:25 -servername f.q.d.n

Convert certificate (PEM) to public key…

openssl x509 -inform pem -in certificate.cer -pubkey -noout > pubkey.pem

Add a (CA) certificate to the JAVA CACerts certificate truststore…

"C:\Java\jdk1.8.0_121\bin\keytool" -import -alias ADCERT-CA-1 -keystore "C:\Java\jdk1.8.0_121\jre\lib\security\cacerts" -trustcacerts -file ADCERT-CA-1.cer

Open-iSCSI Commands

/ GNU/Linux /

Discover, Login & Mount Drives

Display all iSCSI targets on give ip-address.

iscsiadm --mode discovery --type sendtargets --portal 192.168.1.250

Connect to a give target.  

iscsiadm --mode node --targetname iqn.1994-04.org.netbsd.iscsi-target:target0 --portal 192.168.1.250:3260 --login

Close connection to a iSCSI target.

iscsiadm --mode node --targetname iqn.1994-04.org.netbsd.iscsi-target:target0 --portal 192.168.1.250:3260 --logout

Mount a iSCSI disk with lable ISCSI-0 on directory ‘/mnt/iscsi-0’.

mount /dev/disk/by-label/ISCSI-0 /mnt/iscsi-0

 

Node records

Display node records.

iscsiadm --mode node
iscsiadm --mode node --targetname iqn.1994-04.org.netbsd.iscsi-target:target0 --portal 192.168.1.250:3260

 

MySQL basic queries en commands

/ MySQL Server /

Connect to a MySQL instance (commandline)

 Connect to A MySQL instance using named pipe sessies.

>mysql -u root -p

MySQL commands

Some examples to show database objects.

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM naam_van_table;
SHOW STATUS;
SHOW FULL PROCESSLIST;
SHOW PROCEDURE STATUS;

View a stored procedure.

SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES
WHERE SPECIFIC_NAME = '<procedure_name>'
AND ROUTINE_SCHEMA = '<database_name>;

Create, remove and select

CREATE DATABASE naam_van_database;
DROP DATABASE naam_van_database;
USE naam_van_database;

Create and alter tables

CREATE and ALTER table syntax.

CREATE TABLE naam_van_table (column0_id int(10) unsigned NOT NULL primary
key auto_increment,
column1 varchar(75) NOT NULL default '',
column2 varchar(75) NOT NULL default '',
column3 varchar(40) NOT NULL default '',
column4 int(10) unsigned default NULL) engine = InnoDB;
ALTER TABLE naam_van_table ADD COLUMN (column5 bool default 0);
ALTER TABLE naam_van_table ADD COLUMN column6 int unsigned AFTER column2;
ALTER TABLE naam_van_table DROP COLUMN column2;
ALTER TABLE naam_van_table ADD INDEX naam_van_index (naam_van_column);

Queries (Selectie, Insert and Update)

SELECT * FROM naam_van_table WHERE naam_van_column='text';
SELECT column1, column2 FROM naam_van_table WHERE column3='text';
SELECT COUNT(*) FROM naam_van_table;
INSERT INTO naam_van_table VALUES (1, 'Jan', 'Jansen', MD5('zaq123'), 'Docent');
INSERT INTO naam_van_table (voornaam, achternaam, pswd) VALUES
('Jan', 'Jansen', MD5('zaq123')),
('Piet', 'Pieterse', MD5('qaz321'));
UPDATE naam_van_table SET naam_van_column1 = 'X' WHERE naam_van_column2='Y';

Create users and set privileges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT Select ON databasename.tablename TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'%.domain.tld'; 

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

Set file privileges

Add file_priv to account to make it possible to use a bulk import procedure.

USE MYSQL;
UPDATE user SET file_priv = 'Y' WHERE user='username';
FLUSH PRIVILEGES;