Thursday, September 21, 2017

Excel - Useful tricks

Most of the people use excel to calculate some very basic statistics. Sums, Medians, Averages ....
But Excel can do much more and can help also IT admins, below are few commands and procedures I learned over the time and which helps me with day to day work.

Parsing the input

I'm using a lot of scripts to generate data which I then need to report to someone else. Nice example are my connectivity testing scripts those are generating CSV output which I ussualy forward to file. For example here I have the CSV output of my test_icmp script (testing if devices in the -inputfile respond to ping)

PS Microsoft.PowerShell.Core\FileSystem::\\NSA320\public\scripts\connectivity-testing> .\test_icmp.ps1 -inputfile .\devices2test.txt
IP,ICMP
judo.local,Success
nas.local,Success
nsa320.local,Success
brother.local,Success
printer.local,Exception calling "Send" with "1" argument(s): "An exception occurred during a Ping request."
heating.local,Exception calling "Send" with "1" argument(s): "An exception occurred during a Ping request."
turris.local,Success

If I forward the output to csv file for example:
.\test_icmp.ps1 -inputfile .\devices2test.txt > icmpTestResults.csv
I can open it in excel. I can also copy and past the output to excel.
Now to parse it and get the IP in column A and ICMP in column B

  • Mark whole A Column and go to Data -> Text to columns Choose Delimited 

  • Set Comma as delimiter and click Finish


TEXTJOIN

The command joins the fields and create one text string which is connected by the character(s) we specify.
For example let's say I got the list of devices in excel and I have Hostname in column A and domain in B and I want to get in C the FQDN (hostname.domain) to get it I can use following formula:
=TEXTJOIN(".";TRUE;A2:B2)

first parameter (".") is delimiter the character or even string connecting the text in fields
second parameter is say if we want to ignore empty fields
third is range of fields to join


CONCAT

Concatenate (CONCAT) I'm using where I need to generate text which is combination of static and dynamic text.
Let's say I have a config file which shall contain a list of unix commands I can use and each command shall be on one line starting with key word "command="
=CONCAT("command=";A2)


COUNTIF

Is useful to create simple statistics, or to verify that something from one list is in another list.

=COUNTIF(C:C;"TRUE")
This count how many lines in Column C is TRUE

=COUNTIF(C:C;"*")-1
This count how many lines in Column C contain any text. -1 Because header is also count.

In sheet "List of Linux Servers" I have my linux servers and I want to check if they all are in summary sheet.

=COUNTIF(Summary!A:A;'List of Linux Servers'!A2)
This actually counts howmany times text/value in cell 'List of Linux Servers'!A2 is in sheet Summary column A.
You can also verify if there are duplicates (obviously)

I also want to see in summary if device is linux. So I can "reverse" the formula.

=COUNTIF('List of Linux Servers'!A:A;Summary!A2)

VLOOKUP

I often need to compile summary from several tests so what I ussualy do is run my testing scripts, for example test_icmp and test_tcp.
I put the results to the sheets (ICMP Results, TCP 22 Results) and one summary sheet which contains the list of IPs or Hostnames I used as inputfile for my scripts. And then two columns which will use formula to get corresponding ICMP and TCP result.
=VLOOKUP(A2;'TCP 22 Results'!A:B;2;FALSE)
and
=VLOOKUP(A2;'ICMP Results'!A:B;2;FALSE)

  • The first argument is what I'm searching for (the value in first Column)
  • The second argument is Where to search (sheet and range) but it actually goes through the first column in range
  • The third argument is which value to display as result, in our case 2 which means we want to display second column value in range, hence column B.
  • The fourth argument is if we are looking for Exact match (FALSE) or just partial match (TRUE)


AND, OR, IF

I use logical expresion to find out if sertain conditions are met and IF to just make output nice. (but IF can be used in moer clever ways). For example to be able onboard devices to some monitoring tool, I need to have all connectivity and access in place. That can be checked by scripts, but I have very simple to test one thing at time ;-). I use AND in example to combine two conditions to see if both ICMP and SSH works hence I can onboard the device.
=IF(AND(B2="Success";C2=TRUE);"Ready";"Not Ready")


Let's make it more complicated and add WMI test for windows based devices. Now if device is Linux we test B and C column, else we check B and D

=IF(E2=1;AND(B2="Success";C2=TRUE);AND(B2="Success";D2=TRUE))


Filters

Filtering of data is very usefull to get what you need, but be very carefull, the more complicated filtering you try to do the better chance you filter out something you don't want to filter out. Especially keep in mind that two filters (filters on two columns) works as AND. Hence if you if you need OR condition you are not able to achive it with filters.


Substring

Most IT (programmers, admin which can script) knows function substring or its variant. But in excel you don't find it instead you can use, LEFT, RIGHT. To get specified number of characters from beginning(LEFT) or end(RIGTH) of string.

=LEFT(string;number of chars)


However sometimes it's not fixed but you want sting up to some character(s). Favourite examlpe hostname versus FQDN. I got the FQDN and need hostname. So I need substring from left to fisrt dot. Function FIND returns the position (index - starting from 1) of character(or substring).

=FIND("what I search";"in which string")


Now le's combine both to get the hostname from FQDN in row A

=LEFT(A2;FIND(".";A2)-1)

EXAMPLE: Crafte RegEx

I've got list of FileSystems to monitor and need to craft RegEx for monitoring tool template which would tell the tool to monitor only the FileSystems from that list.
In the tool that "monitored object" starts with FileSystem\ then is mount point and then \Use%

Hence if wanna monitor /opt, then need something like this:

/^FileSystems\\/opt\\Use\%$/

If need /opt and/or /usr then it would be
/^FileSystems\\((/opt)|(/usr))\\Use\%$

But if there is more of them it's better to put them in excel (column A in this case starting by A2) and use nice excel formula like this:
=CONCAT("/^FileSystems\\((";TEXTJOIN(")|(";TRUE;A2:A100);"))\\Use\%$/")

EXAMPLE: Check that FQDN match the reverse DNS Lookup

One of the script for connectivity checking I mentioned earlier is to do DNS Lookup and reverse DNS Lookup. It's useful to check that if I can resolve all hosts which customer provides me and also verify the reverse resolution works. However it's also important to check (at least for me and tools I'm administrating) that reverse resolution correspond to given name.

So I run two scripts one to resolve to IP and then I've took those IPs put them to new file and use it as imput to run the script with -reverse switch:
PS C:\Users\j.kindl\Documents\scripts\connectivity-testing> .\test_dns.ps1 -inputfile .\devices2test.txt
Name,IP
judo.local,10.0.0.37
nas.local,10.0.0.33
nsa320.local,10.0.0.33
brother.local,10.0.0.39
printer.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
heating.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
turris.local,10.0.0.1
yun.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
evolve.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
tv.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
dvb-t.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"
switch.livingroom.local,Exception calling "GetHostAddresses" with "1" argument(s): "No such host is known"

PS C:\Users\j.kindl\Documents\scripts\connectivity-testing> .\test_dns.ps1 -reverse -inputfile .\ip2test.txt
Name,IP
10.0.0.37,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.33,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.33,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.39,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
10.0.0.1,Exception calling "GetHostEntry" with "1" argument(s): "No such host is known"
PS C:\Users\j.kindl\Documents\scripts\connectivity-testing>

I create in Excel two sheets one with results resolution of FQDN to IP (FQDN2IP) and one with results of resolving IP to FQDN (IP2FQDN). In FQDN2IP added two columns one is using vlookup to get the FQDN based on IP from sheet IP2FQDN.
=VLOOKUP(B2;IP2FQDN!A:B;2;FALSE)

Second column to Check if original FQDN match the reverse resolution. It just compares Colum A and C.
=EXACT(A2;C2)


UPDATE:
EXACT - is case sensitive and as such can mark false even the original FQDN matches the reverse resolution, they just differ in Upper/Lower cases.
To over come this you shall rather use folowing formula:
=AND(A2=C2)

Thursday, March 30, 2017

PJLink - network control for projector

PJLink is a protocol developed by JBMIA for controlling projectors (and presentation displays for example from NEC) via LAN connections
The protocol works over TCP/IP, by default it use port 4352. Protocol supports authentification, however as I didn't set password for PJLink on my NEC, it works without authentification. (Not sure if that would be same on other venodrs as well).
It use get and set commands. You can get or set several thinks, Power on/off or get the status. Input for video and audio source. Volume and if it's projector you can work also with lens and apertur.
The structure is prety easy header followed by command and parametr:

HeaderClassCommandSpaceParameter
%1POWR?

Detail protocol specification: http://pjlink.jbmia.or.jp/english/data/5-1_PJLink_eng_20131210.pdf
More documentation can be find here: http://pjlink.jbmia.or.jp/english/

Below examples and test was done on my NEC MultiSync V423, PJLink listen on port 4352 as standard suggest the other ports are 80 http for other settings and 7142 which is NEC prorietary remote control protocol, more can be found here.

root@raspberrypi:/home/pi# nmap -sT 10.0.0.40 -p 1-65535

Starting Nmap 6.00 ( http://nmap.org ) at 2017-03-24 21:00 CET
Nmap scan report for 10.0.0.40
Host is up (0.018s latency).
Not shown: 65532 closed ports
PORT     STATE SERVICE
80/tcp   open  http
4352/tcp open  unknown
7142/tcp open  unknown
MAC Address: 58:C2:32:97:3A:48 (Unknown)

Below are example use telnet to switch on the device, switch it off and get status:

Connection closed by foreign host.
root@raspberrypi:/home/pi# telnet 10.0.0.40 4352
Trying 10.0.0.40...
Connected to 10.0.0.40.
Escape character is '^]'.
PJLINK 0
%1POWR 1
%1POWR=OK
%1POWR 0
%1POWR=OK
%1POWR ?
%1POWR=0
^]
telnet> quit
Connection closed.

Here is Python example for Power On/Off
#! /usr/bin/python

#takes two arguments IP and Power ON|OFF
#example: ./PJLinkPower.py 10.0.0.40 OFF

import sys
import cgi
import socket

if len(sys.argv) == 1:
  form = cgi.FieldStorage()
  monitor_ip = form.getvalue('monitor_ip')
else:
  monitor_ip = sys.argv[1]
  power = sys.argv[2]

#define some constanst to make life easier
port = 4352
buffer_size = 1024

if (power == 'ON'):
  command = '%1POWR 1\r'
elif (power == 'OFF'):
  command = '%1POWR 0\r'
else:
  command = '%1POWR ?\r'
  print "Invalid command."
  print "Use: IP ON|OFF"
  print "getting current status of device:"

new = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
new.connect((monitor_ip, port))
recv_data = new.recv(buffer_size)
print recv_data
print "sending: ", command
new.send(command)
recv_data = new.recv(buffer_size)
print recv_data
new.close()

Here is example of use:
root@raspberrypi:/var/www/cgi-bin# ./PJLinkPower.py 10.0.0.40 ON
PJLINK 0
sending:  %1POWR 1
%1POWR=OK
root@raspberrypi:/var/www/cgi-bin# ./PJLinkPower.py 10.0.0.40 OFF
PJLINK 0
sending:  %1POWR 0
%1POWR=OK
root@raspberrypi:/var/www/cgi-bin#
root@raspberrypi:/var/www/cgi-bin# ./PJLinkPower.py 10.0.0.40 TEST
Invalid command.
Use: IP ON|OFF
getting current status of device:
PJLINK 0
sending:  %1POWR ?
%1POWR=0

Thursday, March 9, 2017

Troubleshooting with powershell

I like do things easy and effectively that's why I hate all the clicking back and there in windows opening log files in notepad and searching it... opening taskmgr, services.msi ...

I find lot of those informations can be obtaine using powershell quickly and searching for info is easier.
So I created for my self this list of thing I'm trying to do most during troubleshooting, I also add name of corresponding program in linux.

List of open ports (netstat)

When we need to check the network communication we need to check if server/services is listening netstat -aon give us the list of open ports, but important part is that it also provide the PID, process ID, which means that using Get-Process -id PID (see below) we can check if it's also the correct service listening on the port. It's also powerfull to combine with Select-string to get just what you search for

PS C:\Users\j.kindl> netstat -aon

Active Connections

  Proto  Local Address          Foreign Address        State           PID
  TCP    0.0.0.0:80             0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       888
  TCP    0.0.0.0:443            0.0.0.0:0              LISTENING       708
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:902            0.0.0.0:0              LISTENING       2524
  TCP    0.0.0.0:912            0.0.0.0:0              LISTENING       2524
  TCP    0.0.0.0:1801           0.0.0.0:0              LISTENING       2180
  TCP    0.0.0.0:2103           0.0.0.0:0              LISTENING       2180
...

Process (ps, kill)

Get-Process

PS C:\Users\j.kindl> Get-Process

Handles  NPM(K)    PM(K)      WS(K)     CPU(s)     Id  SI ProcessName
-------  ------    -----      -----     ------     --  -- -----------
    727      35    25096      19708      19.86   7436   1 ApplicationFrameHost
    516      21    67336      71352              7868   0 aswidsagenta
    160      12     9068      14400       8.02   8016   0 audiodg
   3415     121   228576      40692               944   0 AvastSvc
    993      50    20092      33280     213.58  10612   1 AvastUI
    388      16     4864      11956      77.81   4888   1 BingSvc
    357      17     5256      10596       4.30   8216   1 browser_broker
    390      22    14064         36       0.63  10080   1 Calculator
    253      12     3084       1152       0.61    352   1 chrome
    252      25    68844      64728       1.27   1200   1 chrome
    284      32    54984      28428     646.75   1428   1 chrome
   2624     103   390592     156660   6,416.77   1836   1 chrome
    302      39    71356      35524   2,329.05   2592   1 chrome
    416     195   240512     241824   6,598.47   4124   1 chrome
...


Get-Process -id NUMBER
PS C:\Users\j.kindl> Get-Process -id 2180

Handles  NPM(K)    PM(K)      WS(K)     CPU(s)     Id  SI ProcessName
-------  ------    -----      -----     ------     --  -- -----------
    323      29     3836       1396              2180   0 mqsvc

To stop/kill the process there is command Stop-Procces -id NUMBER[-Force]


Services(service)

The list and status of services can be obtained using Get-Service
PS C:\Users\j.kindl\Documents> Get-Service

Status   Name               DisplayName
------   ----               -----------
Stopped  AJRouter           AllJoyn Router Service
Stopped  ALG                Application Layer Gateway Service
Running  AppHostSvc         Application Host Helper Service
Stopped  AppIDSvc           Application Identity
Running  Appinfo            Application Information
Stopped  AppReadiness       App Readiness
...

To stop or start service there are commands Stop-Service , Start-Service


Uptime

net statistics server
PS C:\Users\j.kindl\Documents> net statistics server
Server Statistics for \\G33KSBOOK


Statistics since 2/23/2017 20:13:04


Sessions accepted                  0
Sessions timed-out                 0
Sessions errored-out               0

...

The command completed successfully.


Searching text (grep)

Searching through text is very usefull to filter just what we want (searching for) or filter out the noise we are not interested in. For that porpose Select-string is perfect. In most cases the just what you search and file(s) will be fine enough, but select-stirng is very versatile so check the help if you need something more robust.

Seraching in file:
select string "what I Seacrch" file.txt

Searching in files (all .txt in current folder):
selesc-string "what I Seacrch" *.txt

Searching in nestat output for UPD:
netstat -aon | Select-string 'UDP'

Fitlering out UPD from netstat output:
netstat -aon | Select-string 'UDP' -NotMatch

You can also use more pipes to create more complex filters
netstat -aon | Select-String '0.0.0.0' | Select-string 'UDP' -NotMatch

Searching through Eventlog (note that you need to use -InputObject as Get-EventLog return objects not string):
Get-EventLog -logname System | select-string -InputObject {$_.message} reboot

Print out the file (cat)

To print out the file content to screen you can use old classic type or Get-Content.

type file(s)
type file.txt
type *.txt

Get-content file(s)
Get-Content file.txt
Get-Content *.txt

Specific lines (head, tail)

Sometimes you just need few lines from file/output, to just check the structure of log or format of the date to create proper filter with for select-string.
Or you just need the last events from the log file.
For this you can use Get-Content or Select-Object. I hope the examples are self explanatory.

Get-Content -TotalCount 10
type file | Select-object -first 10

Get-Content -Tail 10
type file | Select-object -last 10

Sorting (sort,uniq)

Sorting and getting unique records are two very handy abilities. In powershel for both there is one command Sort-Object [-unique].
If you are sorting objcets, like output of ls, Get-ChildItem, GetWmiObject .... you can also use -property NAME to sort based on object property you want.

Sorting based on the file size:
PS C:\Users\j.kindl\Documents> ls | Sort-Object -property Length


    Directory: C:\Users\j.kindl\Documents


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         3/9/2017     10:49             22 20170000
-a----        11/1/2016     12:06           1977 ShareSize.vbs
-a----       10/19/2016     20:34          11475 Test-Port.ps1
-a----        8/22/2016     12:23         182711 HorskaVyzva2016Sumava.gpx
-a----        8/23/2016     20:31         233030 HorskaVyzva2016Sumava.pdf
-a----         6/5/2016     18:54         276947 DVB-T_AfterStart.txt
-a----        4/15/2016      0:39         302900 DVB-T_TVRunning.txt
-a----        10/5/2015     14:19         731520 UPnP_Cast_to_Xbox360.pcapng
-a----        8/16/2016     22:55        9283351 WhichPokemon.xcf
d-----        1/25/2017     19:58                GitHub
....
d-----         1/8/2016     14:02                Rodokmen

Sorting text file:
PS C:\Users\j.kindl\Documents> type .\software.txt |Sort-Object -Descending
Vendor                  InstallDate
Skype Technologies S.A. 20170224
Microsoft Corporation   20170305
Microsoft Corporation   20170305
Microsoft Corporation   20170305
Microsoft Corporation   20170305
------                  -----------

PS C:\Users\j.kindl\Documents> type .\software.txt |Sort-Object -Descending -Unique
Vendor                  InstallDate
Skype Technologies S.A. 20170224
Microsoft Corporation   20170305
------                  -----------

Counting lines, words, chars (wc)

Another tiny commandlet/utility which is increadibly useful is Measure-Object. It will be able to count the words (chars separated by white spaces), chars and most important lines.
This comes very handy to find the number of log records in given time period and compare it with abilites of software to process it.(or not and crash/freeze)

example below actaully counts how many open UDP ports is there:
PS C:\Users\j.kindl\Documents> netstat -aon | Select-string 'UDP' -NotMatch | Measure-Object -Line

Lines Words Characters Property
----- ----- ---------- --------
  118

Searching in EventLog

Very important source of information what and when happend is event log it self.
To access eventlog powershell have command Get-Eventlog


PS C:\Users\j.kindl> Get-EventLog -logname System -newest 1

   Index Time          EntryType   Source                 InstanceID Message
   ----- ----          ---------   ------                 ---------- -------
   17673 Mar 04 09:25  Information Microsoft-Windows...            1 The system has returned from a low power state....


If want to be effective in searching in event log we want to know the properties on which we can query we can use command Format-list to obtain the properties.

PS C:\Users\j.kindl> Get-EventLog -logname System -newest 1 | Format-List -property *


EventID            : 1
MachineName        : g33ksbook
Data               : {}
Index              : 17673
Category           : (0)
CategoryNumber     : 0
EntryType          : Information
Message            : The system has returned from a low power state.

                     Sleep Time: 2017-03-03T23:01:26.365204100Z
                     Wake Time: 2017-03-04T08:25:28.112939900Z

                     Wake Source: 0
Source             : Microsoft-Windows-Power-Troubleshooter
ReplacementStrings : {2017-03-03T23:01:26.365204100Z, 2017-03-04T08:25:28.112939900Z, 33998, 11531...}
InstanceId         : 1
TimeGenerated      : 3/4/2017 9:25:29
TimeWritten        : 3/4/2017 9:25:29
UserName           : NT AUTHORITY\LOCAL SERVICE
Site               :
Container          :

To search when my PC was rebooted (Respectively when Event Log service started, similarly you can search when it stopped with eventID 6006) I use "command" where to specify query in $_ links to the event(s) return by Get-EventLog.

PS C:\Users\j.kindl> Get-EventLog -logname System | where {$_.eventID -eq 6005}

   Index Time          EntryType   Source                 InstanceID Message
   ----- ----          ---------   ------                 ---------- -------
   16931 Feb 23 20:13  Information EventLog               2147489653 The Event log service was started.
   13372 Jan 11 23:57  Information EventLog               2147489653 The Event log service was started.
   11730 Dec 17 12:50  Information EventLog               2147489653 The Event log service was started.
   11213 Dec 11 10:24  Information EventLog               2147489653 The Event log service was started.
   10160 Nov 28 17:13  Information EventLog               2147489653 The Event log service was started.
    9198 Nov 14 14:53  Information EventLog               2147489653 The Event log service was started.
    9007 Nov 13 17:55  Information EventLog               2147489653 The Event log service was started.
    8104 Nov 01 18:16  Information EventLog               2147489653 The Event log service was started.
    7991 Nov 01 18:10  Information EventLog               2147489653 The Event log service was started.
    7683 Oct 31 19:55  Information EventLog               2147489653 The Event log service was started.
    6593 Oct 18 09:35  Information EventLog               2147489653 The Event log service was started.
    5451 Oct 04 19:24  Information EventLog               2147489653 The Event log service was started.
    5365 Oct 04 19:18  Information EventLog               2147489653 The Event log service was started.
    3623 Sep 16 15:47  Information EventLog               2147489653 The Event log service was started.
    2417 Sep 02 23:18  Information EventLog               2147489653 The Event log service was started.
    1416 Aug 24 02:08  Information EventLog               2147489653 The Event log service was started.
     535 Aug 11 15:50  Information EventLog               2147489653 The Event log service was started.
     220 Aug 10 22:57  Information EventLog               2147489653 The Event log service was started.
       3 Aug 10 22:44  Information EventLog               2147489653 The Event log service was started.


WMI

WMi is great source of information about you system, OS version, software installed, processes running, services, disks, network interfaces, name it... and probably it's there.

Using Get-WmiObject you can access the WMI information, either by addressing the Class and work with return object or use WQL (WMI Query language), which is actaully subset of SQL (so when you know SQL you know WQL).

To get all from class (Win32_OperatingSystem), it gives more info then query "select * from  Win32_OperatingSystem"
Get-WmiObject Win32_OperatingSystem | select-object -property *

To get just some info you can use select-object -property NAME(S)
PS C:\Users\j.kindl\Documents> Get-WmiObject Win32_OperatingSystem | select-object -property Name, OSArchitecture | format-list

Name           : Microsoft Windows 10 Home|C:\WINDOWS|\Device\Harddisk0\Partition2
OSArchitecture : 64-bit

Same but using WQL query:
PS C:\Users\j.kindl\Documents> Get-WmiObject -Query "select Name, OSArchitecture from Win32_OperatingSystem"


__GENUS          : 2
__CLASS          : Win32_OperatingSystem
__SUPERCLASS     :
__DYNASTY        :
__RELPATH        : Win32_OperatingSystem=@
__PROPERTY_COUNT : 2
__DERIVATION     : {}
__SERVER         :
__NAMESPACE      :
__PATH           :
Name             : Microsoft Windows 10 Home|C:\WINDOWS|\Device\Harddisk0\Partition2
OSArchitecture   : 64-bit
PSComputerName   :

You can also run the Get-WmiObject remotly:
Get-WmiObject -computername IP Win32_Computersystem

or
Get-WmiObject -computername HOST -Credential Domain\User -Query "Select * from Win32_Bios"


Usefull WMI queries

If your software is crashing and hard to find why it's good to what was installed with this query you can get the list of installed software including dates, when it was installed:
PS C:\Users\j.kindl\Documents> Get-WmiObject Win32_Product | select-object -property Name,Version,Vendor,InstallDate

Name                                                                                                       Version        Vendor                  InstallDate
----                                                                                                       -------        ------                  -----------
digiCamControl                                                                                             2.0.0.0        Duka Istvan             20160619

Microsoft Application Error Reporting                                                                      12.0.6015.5000 Microsoft Corporation   20150702
Office 16 Click-to-Run Extensibility Component                                                             16.0.7766.2047 Microsoft Corporation   20170305
Office 16 Click-to-Run Localization Component                                                              16.0.7668.2066 Microsoft Corporation   20170305
Office 16 Click-to-Run Extensibility Component 64-bit Registration                                         16.0.7766.2047 Microsoft Corporation   20170305
Office 16 Click-to-Run Licensing Component                                                                 16.0.7766.2047 Microsoft Corporation   20170305

Now as the date is in format YYYYMMDD it's easily sortable and comparable. The higher number the newer installation. So to get software installed since beginig of Feb 2017 you would use where InstallDate > 20170200 the Select-Object command in example below is doing output more readable:
PS C:\Users\j.kindl\Documents> Get-WmiObject -query "Select * from Win32_Product where InstallDate > 20170200" | Select-Object -property Name,Version,Vendor,InstallDate

Name                                                               Version        Vendor                  InstallDate
----                                                               -------        ------                  -----------
Office 16 Click-to-Run Extensibility Component                     16.0.7766.2047 Microsoft Corporation   20170305
Office 16 Click-to-Run Localization Component                      16.0.7668.2066 Microsoft Corporation   20170305
Office 16 Click-to-Run Extensibility Component 64-bit Registration 16.0.7766.2047 Microsoft Corporation   20170305
Office 16 Click-to-Run Licensing Component                         16.0.7766.2047 Microsoft Corporation   20170305
Skype™ 7.32                                                        7.32.104       Skype Technologies S.A. 20170224

Enviromental variable(env)

Enviromental variables among others include also shortcats to certain folders, so to get there without remebering the path you can use $env:VARIABLE (in explorer you can use %VARIABLE%)
For example:
cd $env:HOMEPATH

the list of variables can be obtained using Get-ChildItem Env:
PS C:\Users\j.kindl\Documents> Get-ChildItem Env:

Name                           Value
----                           -----
ALLUSERSPROFILE                C:\ProgramData
APPDATA                        C:\Users\j.kindl\AppData\Roaming
CommonProgramFiles             C:\Program Files\Common Files
CommonProgramFiles(x86)        C:\Program Files (x86)\Common Files
CommonProgramW6432             C:\Program Files\Common Files
COMPUTERNAME                   G33KSBOOK
ComSpec                        C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK               NO
HOMEDRIVE                      C:
HOMEPATH                       \Users\j.kindl
...
SystemDrive                    C:
SystemRoot                     C:\WINDOWS
TEMP                           C:\Users\JC340~1.KIN\AppData\Local\Temp
TMP                            C:\Users\JC340~1.KIN\AppData\Local\Temp
USERDOMAIN                     G33KSBOOK
USERDOMAIN_ROAMINGPROFILE      G33KSBOOK
USERNAME                       j.kindl
USERPROFILE                    C:\Users\j.kindl
windir                         C:\WINDOWS
windows_tracing_flags          3
windows_tracing_logfile        C:\BVTBin\Tests\installpackage\csilogfile.log

Tuesday, December 27, 2016

Connectivity troubleshooting 101

Theory

Lets's start with necessary theory

All connectivity troubleshooting is around 3 protocols which will help us to find if connectivity works. We will not discuss if application/server/deamon running on the port works correctly.
Those protocol are ICMP, TCP and UDP and there understanding is crucial to correctly work with testing tools and scripts and understand results.
We however will cover just necessary aspects

ICMP

Internet Control Message Protocol is useful to find out if routing works correctly. Among a lot of messages we will concentrate on 3 echo request, echo reply, and TTL exceeded.

Echo request and reply 

Is pair of messages they are used in ping you send Echo request and the destination devices shall reply with echo reply.

TTL Exceeded

Each packet (not only ICMP but also TCP and UDP) have set TTL (time to live) as packet is routed through the network each device doing routing(firewall, router, computer, switch with routing module) will inspect the packet and check TTL if it's higher then 0 it will decrease it by one and send packet further based on routing table. If it's 0 it shall send the ICMP message TTL exceed to source of the packet this behavior is utilized by traceroute.

TCP

Transmission Control Protocol is used to transfer data. For us is important to understand it connection oriented. So there is something we call session. Session begins with so called 3 way hand shake which if successful establish the session. Then is data transfer. And then connection is closed.

For easier understanding you can imagine it as call.You call, somebody answers it. For each question you ask you got "some" answer, if you don't understand you ask to repeat and before hangup you say goodbye.

To specify session TCP use ports 0 - 65535 so the session is identified by source IP, source Port and destination IP and destination Port. Source port is assigned dynamically and is usually higher then 1024. Destination port depends on setting of service on destination servers, but there are standard ports which are reserved for specific services. For example port 80 for http, 443 for https, 22 SSH, 21 FTP, 25 SMTP...

3 way handshake

Client starts connection with SYN packet server reply with SYN-ACK and client sends ACK. That's how connection is established.
If server doesn't want or cannot accept the connection it shall send RST packet instead of SYN-ACK.
Most of the testing utilities will show message "Connection refused" or "Connection closed by foriegn host"

Joke to easier remember :-)
- Can I tell you TCP joke?
+ Yes. please tell me TCP joke.
- OK. I will tell you TCP joke.

UDP

User Datagram Protocol similar as TCP is used for transfer of data also use ports i same way as TCP. The difference is that UDP is connection less. You send data and you don't care if it's received or if it's in correct order.

Again analogy for easier understanding. You can imagine it as pigeon post. You send message and you don't know if it was received or not and you don't even care.

Joke to easier remember :-)
- I would tell you UDP joke, but I'm not sure if you would get it.

General Process of troubleshooting

There are few steps to troubleshoot and get to quickly to conclusion point 1) you can do on your own, if you have access to destination machine, points 2) and 3) needs to be resolved by network admin using info from traceroutes.

1) is port open at destination(is there some service listening)?
 - try connect locally (telnet, nc)
 - check open ports (netstat, lsof, ps)
 - kill the service running and set dummy listener (nc)
 - check firewall allows the traffic (if windows is part of Domain it's possible that even you disable firewall it's still active because of domain policy)
2) is routing working correctly?
 - try ping
 - do the traceroute
3) are all firewalls allowing traffic?
 - do tcp or udp based traceroute
 - run packet capture (tcpdump, wireshark) and test connectivity, check for RST packet
 - check for possible asymetric routing.


Below you have flowchart which help you the troubleshooting path:


Tools

Ping

Ping use ICMP echo request/reply. It send request and wait for reply or other message. If none is received it will show timeout.

Linux/Windows
On both platforms ping s work the same only difference is that on linux by default it run continues ping and you need to stop it by CTRL-C and on windows it do just 4 probes.
You can change the behavior by using options, read the help of those for more details.

Examples:

PS C:\Users\j.kindl> ping 192.168.0.1
Pinging 192.168.0.1 with 32 bytes of data:
Reply from 192.168.0.1: bytes=32 time=5ms TTL=254
Reply from 192.168.0.1: bytes=32 time=26ms TTL=254
Reply from 192.168.0.1: bytes=32 time=26ms TTL=254
Reply from 192.168.0.1: bytes=32 time=1ms TTL=254

Ping statistics for 192.168.0.1:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 1ms, Maximum = 26ms, Average = 14ms

PS C:\Users\j.kindl> ping 192.168.0.3

Pinging 192.168.0.3 with 32 bytes of data:
Reply from 192.168.0.10: Destination host unreachable.
Reply from 192.168.0.10: Destination host unreachable.
Reply from 192.168.0.10: Destination host unreachable.
Reply from 192.168.0.10: Destination host unreachable.

Ping statistics for 192.168.0.3:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

traceroute/tracert


Traceroute features TTL field in packet, it start be sending probe where TTL = 1, hence first router shall respond that TTL expires. second probe with TTL = 2 so second router respond TTL expired, 3rd probe TTL=3 and 3rd router will respond ... and so one it keep increasing TTL till it reach destination or limit of probes, which by default is 30.

Linux/windows:
On linux it's traceroute and it use UDP on high ports it keeps increasing the UDP port along with TTL which makes it denied by almost any firewall.
The solution is easy, you can use -I option to make it use ICMP instead. However you have to run it with root rights. (Same tcp traceroute).
To omit the problem with ever increasing UDP port I wrote simple script which use possibility to start at custom UDP port and start from certain hop, hence it going in loop and for each hop it's doin traceroute with one probe starting from custom hop and using custom port. It's bit of scratching right ear with left hand, but it works.
Here you can find my shell script for UDP Trace with not increasing UDP port: udptrace
Windows version, tracert is using ICMP by default.

Examples:
Here you can see one succesful traceroute (it's from linux using -I, hence use ICMP as probe packets). Those lines containing * * * are router or firewalls, which are set not to answer with TTL.

root@raspberrypi:/home/pi# traceroute -I lifehacker.com
traceroute to lifehacker.com (151.101.1.34), 30 hops max, 60 byte packets
 1  10.0.0.1 (10.0.0.1)  0.368 ms  0.513 ms *
 2  * * *
 3  * * *
 4  * * *
 5  * * *
 6  * 124.197.broadband16.iol.cz (90.183.197.124)  30.275 ms  29.408 ms
 7  * * *
 8  * * *
 9  * * *
10  * * *
11  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
12  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
13  * * *
14  * * *
15  * * *
16  * * *
17  * 151.101.1.34 (151.101.1.34)  47.523 ms *

Now let's say network admin do the change routing change on ffm-b1-link.telia.net and by mistake enter wrong route and create routing loop below is how the traceroute will look like

root@raspberrypi:/home/pi# traceroute -I lifehacker.com
traceroute to lifehacker.com (151.101.1.34), 30 hops max, 60 byte packets
 1  10.0.0.1 (10.0.0.1)  0.368 ms  0.513 ms *
 2  * * *
 3  * * *
 4  * * *
 5  * * *
 6  * 124.197.broadband16.iol.cz (90.183.197.124)  30.275 ms  29.408 ms
 7  * * *
 8  * * *
 9  * * *
10  * * *
11  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
12  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
13  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
14  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
15  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
16  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
17  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
18  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
...
29  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
30  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *

In case above you can clearly identify the problem, however your traceroute might just "stop in the midle" and then traffic need to be checked hop by hop by network admins, and traceroute is just the hint for them.
The problems which produce output below can be:
Firewall or access-list is blocking the traffic
Devices are set not to send ICMP error codes. (In this case the traffic might go further then we see)

root@raspberrypi:/home/pi# traceroute -I lifehacker.com
traceroute to lifehacker.com (151.101.1.34), 30 hops max, 60 byte packets
 1  10.0.0.1 (10.0.0.1)  0.368 ms  0.513 ms *
 2  * * *
 3  * * *
 4  * * *
 5  * * *
 6  * 124.197.broadband16.iol.cz (90.183.197.124)  30.275 ms  29.408 ms
 7  * * *
 8  * * *
 9  * * *
10  * * *
11  * * ffm-bb4-link.telia.net (62.115.113.114)  48.533 ms
12  ffm-b1-link.telia.net (62.115.116.160)  48.519 ms * *
13  * * *
14  * * *
15  * * *
...
30  * * *

nslookup

Use to check your DNS is working. Even though it's not related with connectivity for some applications it's important that name resolution works.

Examples:
root@NSA320:~# nslookup judo.local
Server:    10.0.0.1
Address 1: 10.0.0.1

nslookup: can't resolve 'judo.local'

telnet

Telnet is TCP client, and it can be used to test connectivity to any TCP Port. If the server we connect to is not using encryption, it can be even used to test higher protocol.
It's avalable on and work same for linux and windows enviroment, in Windows you just need to "add
feature: Telnet client". In linux you need to install it, it's usually part of main repository.

Examples:

This is how succesfull connection looks like
root@raspberrypi:/home/pi# telnet 10.0.0.34 80
Trying 10.0.0.34...
Connected to 10.0.0.34.
Escape character is '^]'.
^]
telnet> quit
Connection closed.


This is what you will get if the connectivity is blocked by firewall (and firewall is talkative) or nothing is listening on that port.
root@raspberrypi:/home/pi# telnet 10.0.0.34 8080
Trying 10.0.0.34...
telnet: Unable to connect to remote host: Connection refused

nc/netcat

Netcat or nc is networking Swiss knife.
It can be used as dummy listener on port or bound the command or script to port, to create simple server.
It can be used as a client as well.
It operates over UDP and TCP.
In linux you need to install it it's usually in main repository. For windows you need to download compiled version for eaxample from here (https://eternallybored.org/misc/netcat/)
Or for creating "dummy" TCP server / listener you can use powershell script like this https://github.com/kindljiri/connectivity-testing/blob/master/PortListener.ps1
Below are examples of creating dummy listener and connecting to it in order to test end to end connectivity.

Examples:

First we set on my raspberry pi netcat to listen on port 160 TCP

root@raspberrypi:/home/pi# nc -l 160
hello raspi from turris
root@raspberrypi:/home/pi#

Second we connect from my turris router to my raspberry pi to port 160 TCP and write the test message you see same message shows on the Raspberry as well, which means connection works perfectly.

root@turris:~# telnet 10.0.0.37 160
hello raspi from turris
^]

Console escape. Commands are:
 l      go to line mode
 c      go to character mode
 z      suspend telnet
 e      exit telnet

e

root@turris:~#

Now we do same think but with UDP port 162.
root@raspberrypi:/home/pi# nc -u -l 162

hello raspi from NSA320

^C
root@raspberrypi:/home/pi#

root@NSA320:~# nc -u 10.0.0.37 162
hello raspi from NSA320
^Cpunt!

root@NSA320:~#


tcpdump

TCPDump is tool for capturing packets. So you can see exactly what's going on.
TCPDump is avalable on linux, on windows it's better to use wireshark, even though you can find and download tcpdump for windows as well.
You shall learn few basic filters and combine them:
tcpdump host  - to filter just traffic to and from specific device
tcpdump port - to filter traffic on specific port


Below are two examples:
1) is succesfull connection to port 80 at host 10.0.0.39 in output I added few empty lines to separated so you better see.
First 3 packets are 3 way handshare we discussed in theory part.
Next two is data and ack that is was recieved. (in longer connections you'll see the traffic coming both ways)
Last 3 packets are closeing of connection.

root@raspberrypi:/home/pi# tcpdump -n host 10.0.0.39
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

18:57:12.524518 IP 10.0.0.37.48472 > 10.0.0.39.80: Flags [S], seq 1769757482, win 29200, options [mss 1460,sackOK,TS val 63103684 ecr 0,nop,wscale 6], length 0
18:57:12.528361 IP 10.0.0.39.80 > 10.0.0.37.48472: Flags [S.], seq 2965564486, ack 1769757483, win 8688, options [mss 1460,nop,wscale 0,nop,nop,sackOK,nop,nop,TS val 631341150 ecr 63103684], length 0
18:57:12.528754 IP 10.0.0.37.48472 > 10.0.0.39.80: Flags [.], ack 1, win 457, options [nop,nop,TS val 63103684 ecr 631341150], length 0

18:57:22.141319 IP 10.0.0.37.48472 > 10.0.0.39.80: Flags [P.], seq 1:11, ack 1, win 457, options [nop,nop,TS val 63104646 ecr 631341150], length 10
18:57:22.144151 IP 10.0.0.39.80 > 10.0.0.37.48472: Flags [.], ack 11, win 8678, options [nop,nop,TS val 631350800 ecr 63104646], length 0

18:57:22.152834 IP 10.0.0.39.80 > 10.0.0.37.48472: Flags [FP.], seq 1, ack 11, win 8678, options [nop,nop,TS val 631350800 ecr 63104646], length 0
18:57:22.153440 IP 10.0.0.37.48472 > 10.0.0.39.80: Flags [F.], seq 11, ack 2, win 457, options [nop,nop,TS val 63104647 ecr 631350800], length 0
18:57:22.156770 IP 10.0.0.39.80 > 10.0.0.37.48472: Flags [.], ack 12, win 8688, options [nop,nop,TS val 631350800 ecr 63104647], length 0

2) is connection refused when trying to connect to port 82 at host 10.0.039. (there is nothing listening there).
You can see that destination device refuse connection with RST packet.

root@raspberrypi:/home/pi# tcpdump -n host 10.0.0.39
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
19:00:02.370831 IP 10.0.0.37.44112 > 10.0.0.39.82: Flags [S], seq 303448752, win 29200, options [mss 1460,sackOK,TS val 63120669 ecr 0,nop,wscale 6], length 0
19:00:02.374124 IP 10.0.0.39.82 > 10.0.0.37.44112: Flags [R.], seq 0, ack 303448753, win 0, length 0

wireshark

As tcpdump wireshark is a packet capturing and analysing tool. For connectivity troubleshooting it's Must to learn the use filters.

Below are few usefull examples:
ip.addr == 10.0.0.39
udp.port == 162

Below is same example as in tcpdump above.


netstat

Netstat will show all kinds of network statistics, as what connections are open, or which are waiting to be closed, how is routing set and for us most important is what ports are open (it also say the PID of process listening on the port so you can kill it if you need to replace it with dummy listener for troubleshooting purpose.) Netstat is default for both Linux and Windows and can be in connection with grep(Linux), select-string (Windows/power shell)  

Windows:
netstat -aon
netstat -aon | select-string

Linux:
netstat -tupln
netstat -tupln | grep

PS C:\Users\j.kindl> netstat -aon

Active Connections

  Proto  Local Address          Foreign Address        State           PID
  TCP    0.0.0.0:80             0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       880
...
  TCP    10.0.0.31:139          0.0.0.0:0              LISTENING       4
  TCP    10.0.0.31:59245        0.0.0.0:0              LISTENING       5676
  TCP    10.0.0.31:62273        149.154.167.91:443     ESTABLISHED     5624
...
  TCP    10.0.0.31:63069        31.13.93.3:443         ESTABLISHED     3092
  TCP    10.0.0.31:63085        52.222.148.86:443      CLOSE_WAIT      9936
  TCP    10.0.0.31:63116        173.194.76.189:443     ESTABLISHED     3092
  TCP    10.0.0.31:63578        52.45.152.106:443      CLOSE_WAIT      9936
  TCP    10.0.0.31:63593        108.160.172.225:443    CLOSE_WAIT      9936
  TCP    10.0.0.31:63596        2.21.74.9:80           TIME_WAIT       0
...
  TCP    127.0.0.1:63054        127.0.0.1:63053        ESTABLISHED     9936
  TCP    192.168.71.1:139       0.0.0.0:0              LISTENING       4
...
  TCP    [::]:49667             [::]:0                 LISTENING       1832
  TCP    [::]:49668             [::]:0                 LISTENING       2108
  TCP    [::]:49670             [::]:0                 LISTENING       732
  TCP    [::]:49681             [::]:0                 LISTENING       724
  UDP    0.0.0.0:443            *:*                                    4560
  UDP    0.0.0.0:3702           *:*                                    696
...
  UDP    0.0.0.0:58363          *:*                                    1832
  UDP    0.0.0.0:60778          *:*                                    1164
  UDP    0.0.0.0:62458          *:*                                    4560
  UDP    10.0.0.31:137          *:*                                    4
  UDP    10.0.0.31:138          *:*                                    4
  UDP    10.0.0.31:1900         *:*                                    3356
  UDP    10.0.0.31:2177         *:*                                    3356
  UDP    10.0.0.31:53304        *:*                                    3356
  UDP    10.0.0.31:59245        *:*                                    5676
  UDP    127.0.0.1:1900         *:*                                    3356
...
  UDP    192.168.136.1:2177     *:*                                    3356
  UDP    192.168.136.1:53302    *:*                                    3356
  UDP    [::]:3702              *:*                                    3356
...
  UDP    [::1]:1900             *:*                                    3356
  UDP    [::1]:53301            *:*                                    3356

Thursday, November 17, 2016

Local DNS zone with unbound

If you have OpenWRT based router you can quite easily set you local DNS and make your life easier. I did.

In config file /etc/config/resolver add section config resolver 'unbound_includes' with line
list include_path '/etc/unbound/local.conf'

It shall look like this:

config resolver 'unbound_includes'
 list include_path '/etc/unbound/local.conf'

Create the file which was included, hence /etc/unbound/local.conf
Below is my setting, you might noticed I'm lame and use several A records instead of CNAME, well actually unbound doesn't support CNAME record.
I created one domain .local and set there few common devices which have global role (central heating, printer, network storage). I also created subdomain .livingroom.local where are devices in living room. I plan other subdomains, because I would like to place several sensors all around the house.

server:
local-zone: 'local.' static
local-data: 'judo.local. IN A 10.0.0.37'
local-data: 'nas.local. IN A 10.0.0.33'
local-data: 'nsa320.local. IN A 10.0.0.33'
local-data: 'brother.local. IN A 10.0.0.39'
local-data: 'printer.local. IN A 10.0.0.39'
local-data: 'heating.local. IN A 10.0.0.42'
local-data: 'turris.local. IN A 10.0.0.1'

local-zone: 'livingroom.local.' static
local-data: 'yun.livingroom.local. IN A 10.0.0.43'
local-data: 'evolve.livingroom.local. IN A 10.0.0.38'
local-data: 'tv.livingroom.local. IN A 10.0.0.38'
local-data: 'dvb-t.livingroom.local. IN A 10.0.0.38'
local-data: 'switch.livingroom.local. IN A 10.0.0.34'
local-data: 'xbox360.livingroom.local. IN A 10.0.0.41'
local-data: 'nec.livingroom.local. IN A 10.0.0.40'
local-data: 'monitor.livingroom.local. IN A 10.0.0.40'
local-data: 'display.livingroom.local. IN A 10.0.0.40'
local-data: 'yamaha.livingroom.local. IN A 10.0.0.36'
local-data: 'av-reciever.livingroom.local. IN A 10.0.0.36'
local-data: 'reciever.livingroom.local. IN A 10.0.0.36'

Now restart unbound service
/etc/init.d/unbound restart
and enjoy

Tuesday, August 9, 2016

Learn music for programmers with Sonci Pi - 04 - Jam Session

In second lesson we created random Chinese music generator just by randomly playing tones from pentatonic scale. But pentatonic scale is not the only one of course.

Theory


Diatonic Scale


The basic and most known is of course diatonic scale. It's the one all of you know C-D-E-F-G-A-B(H)-C. I will not go in to further details. First of all we already touch the basic in first lesson and second you can ready more on wikipedia.

Pentatonic Scale


We touch pentatonic scale in second lesson and we said it's the black keys on the piano, so C# D# F# G# A#. However in the music the difference between tones matter more then the frequencies so if you move all of them by half tones you again get pentatonic scale. If you are interested more you can go on and again wikipedia is good source

Jazz Scale


There is also jazz scale. A great deal of modern jazz harmony arises from the modes of the ascending form of the melodic minor scale, also known as the jazz melodic minor scale C D E-flat F G A B
Well there are many jazz scales and again rather then explain all, you can read wikipedia.

There are also other scales, but for today enough of theory.


Sonic PI


So the question is if  we can create the Chinese music by just randomly playing pentatonic scale can we create Jam session by "randomly" playing jazz scale?

Let's start with little repetition

Function

Just use define :name do |prametr1, parametr2| to define function, if you want it to be function  you must return some value, so then before the end use name = value
Below example is factorial function.

define :fact do |n|
  fact = (1..n).reduce(:*)||1
end

So with factorial and combination functions, I've create ghoose function from second lesson, choose "randomly" with Gaussian probability distribution, but this time it's general for any list.

define :gchoose do |list|
  fl = list.length
  choosen_index = (fl/2).to_i
  base = 2**(fl-1)
  index = 0
  (fl). times do
    #puts (combination (fl-1), index)
    choosen_index = index if one_in((base/(combination (fl-1), index)).to_i)
    index = inc index
  end
  ghoose = list[choosen_index]
end


Now we just play random notes from Jazz scale with random duration

loop do
  play (gchoose jazz_scale)
  sleep (gchoose durations)
end

Here is the complete code on github if you will play it you find out that it's not what you would expect. The problem is that it miss one important think, rhythm.

OK, let's add underlying rhythm (jazz use 4/4 time signature so easiest would be 4 quarter notes, F D D D for example).

#Basic rhythm
in_thread do
  loop do
    play (F-12)
    sleep 1
    3. times do
      play (D-12)
      sleep 1
    end
  end
end

We also need to sync the our "improvisation" part to reflect the time signature. We just set Total Duration to 4 and initiate Remaining Duration to the same. Choose Current Duration, if it's higher then remaining we cut it short and use remaining. Once RD is 0 we reset it to TD.

#Total Duration
TD = 4
#Remaining Duration
RD = TD

loop do
  play (gchoose jazz_scale)
  #Choosen Duration
  CD = gchoose durations
  if CD > RD
    CD = CD - RD
  end
  sleep CD
  RD = RD - CD
  if RD <= 0
    RD = TD
  end
end

The complete code is my github. If you will try to play it I think you find that even we add rhythm and it sound little more like jazz, it's actually terrible. If you use default choose function instead of my gchoose it sound better, but still not jam session.

So let's can we create Jam session by "randomly" playing jazz scale?
No, not even if add rhythm to it.

Tuesday, August 2, 2016

Learn music for programmers with Sonci Pi - 03 - Rythm

Theory


Beat

One beat is basic time unit. One beat equals (most often) 1/4 note (because it's most common time measure in music).

Measure or Bar(line)

Measure or sometimes referred as bar is musical time unit which divides the song into larger units

Time signature

Time signature actually is telling us how many notes (the blue number) of and how long (the red number) we shall play. In example below it 3 quarter notes. if it would be 4/4 it would be 4 quarter notes, 2/2 would be 2 half notes, 4/8 would be 4 eights notes, etc.
Now as you can see in example below you don't have to play quarter notes, but if you summarize you shall get time corresponding to the time signature.
Take example of part between green lines(the end part). It's six 1/8 notes hence 6/8 = 3/4.

Rhythm

Rhythm is event repeating over time. Main beat and unstressed off beat or Accented back beat, repeating in pattern. To distinct them we use different drums, high and low, long and short beats.

Except using the traditional notation to understand and follow the rhythm, instead of linear display we can use circle, so one circle equals to one measure. Hence the measure with 3/4 time signature we can divide into 3 parts which each correspond to 1/4 note. (If tempo is 60 bpm then the full circle is 3 seconds).

In below section I show you few examples of rhythms, how they look on the "rhythm circle" and how we can write that in Sonic PI

Sonic PI


Waltz

Waltz use 3/4 time signature. Below we can see the basic simple waltz rhythm the first tone higher(main beat) next two lower(off and secondary beat).



It divides the rhythm circle below in to 3 equal parts each is for one 1/4 note.
In sonic code example below it corresponds to F and two D


Sonic Pi code:
# Welcome to Sonic Pi v2.10
use_bpm 90
use_synth :piano

#Let first define basic tones
D = 63
F = 66

#Basic rhythm
loop do
  play F
  sleep 1
  play D
  sleep 1
  play D
  sleep 1
end

Polka

Polka use 2/4 time signature. Below we can see it on rhythm circle. It's pretty easy one note (let it be E) starts on "12 o'clock" and last 1/4 as we have 2/4 = full circle, then 1/4 is one half of the circle so second note(let it be F) starts on "6 o'clock"


And here is the same in Sonic Pi

loop do
  play E
  sleep 1
  play F
  sleep 1
end

Let's try something little more interesting.


It's not hard we only need to play more things at once so lets do threads. It's easy syntax:
in_thread do
  commands
end

Complete Sonic PI code for polka rhythm above, the main rhythm(2 quarter F notes) are played in thread 1/8ths in "main" part:

# Welcome to Sonic Pi v2.10
use_bpm 90
use_synth :piano

#Let first define basic tones
C = 61
F = 66

#Basic rhythm
in_thread do
  loop do
    2.times do 
      play F
      sleep 1
    end
 end
end

loop do
  sleep 0.5
  play C+12
  sleep 0.5
  sleep 0.5
  play C+12
  sleep 0.5
end

Tango

Tango is in use 4/4 time signature. If we little simplify it's long, short, short, long. If you learn tango in dancing lesson you might remember the step pattern, slow, slow, quick, quick.

So here we go this one is pretty simple two long C to short E. (The dot behind the C means 1/8 longer)

Here it's on the rhythm circle



And here is the Sonic Pi code:
# Welcome to Sonic Pi v2.10
use_bpm 78
use_synth :piano
loop do
  play 61
  sleep 1
  sleep 0.5
  play 65
  sleep 0.5
  play 65
  sleep 1
  play 61
  sleep 1
end


Here is one little more complected as it's more changing the pitches but the timing is important (not the pitch)


You can try to write Sonic Pi code your self and then compare with my on github