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)

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:

Header	Class	Command	Space	Parameter
%	1	POWR		?

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

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