Alternate solution for O365 – Kopano

I’m trying to experiment myself with alternative solutions for O365. The first good candidate I think is Kopano Groupware.

• Kopano page: https://kopano.com/
• Download Univention server + Kopano appliance: https://kopano.com/downloads-demo/?demo=Kopano+ONE&headline=Appliances&target=Univention
• The good thing is Kopano solution is integrated with Univention Corporate Server (UCS) which also allows us to set up an Directory service like AD, setup DNS, Filesharing etc. And all are managed via Web interface.

​I set it up on a Virtual Box VM and played around. With Kopano, I tested email service and Video/Audio conference

Email and Calendar seems good, I’m using Web app but there’s also Desktop app version.

Completed SETC Senior I

Dec 10th was the day I passed the Committee review for SETC Senior. It took almost a month since I submitted my PSE, almost a year since I focused on writing my PSE and almost 3 years since I started my PSE, exactly 5 years from my last promotion. From December last year, I spared at least 30 minutes every morning to work on my SETC: reading guidelines, writing PSE, completing PATH self assessment and other tasks whatever contribute to my nomination.Seeing my PSE longer and longer each day was my motivation during this time.

Summary of my process:

April 2020, my PSE was 80% completed: still missing 2 or 3 examples and I’m in process of writing each example completely, I engaged with my direct manager about my SETC plan. My manager shared with me his PSE many years ago and encouraged me to continue.

Since then, my manager and I just talked about once a month to update my progress, I was still spending at least 30 minutes every day for SETC.

Jul 27, I completed my PATH assessment and sent to my manager for verification. It took me about 2 months to complete and almost 2 weeks for my manager to check and verify.

Since Aug, we had 2 or 3 calls to review my PSE, I adjusted my PSE according to my manager’s advice.

Oct 14th, my manager decided “Let’s have it a go” so I started to submit my PSE into SETC system right away, with a hope to complete it before year end. If I my nomination is stuck to next year, I’ll have problem because some important examples were from 2015, and next year they will be expired.

Since then, my manager and I follow up to get green light from N+2 manager, chasing the Reference letters to be completed ASAP. I lost valuable reference from a Managing Director because he can’t log in to SETC system.

Technical review – where the pain started

Nov 3rd, after collected 2 reference letters, my PSE were sent to 2 technical reviewers. The reviewer 2 gave me a pass quite quickly, just the day after. The reviewer 1 worked on it very carefully, for a week, and then held me back and said I need to rework on Solution Experience and Input to Business Strategy pillars, my writing in those pillars didn’t satisfy him/her.​ After knowing that, I rewrote the PSE according to his comments, and submitted again on Nov 11th. 

Nov 13th, the reviewer 1 held me back again with more detailed comments in those 2 pillars and asked me to find a SETC mentor to help rewriting. Additionally, HR manager said I had 1 last shot to take. My feeling was bad after being “rejected” 2 times by this reviewer. I didn’t know who he/she was but I wished I could talk to him/her to protect my points. But that was the game! I disappointed, closed everything and didn’t touch SETC for 3 days. On day four, I decided I had to move on, the year was ending and I would be in worse trouble if I couldn’t make it this year, so I tried to find a PTE champion in Thailand to help me rewrite it. It’s lucky that I had a good relationship with TH colleagues so he supported me very well. I called him, sent him my PSE, he reviewed and sent back with comments, the process repeated 3 times in 1 week before we came to the final version and I submitted again on Nov 25th. The PTE champion and I worked days and nights for this version.

The reviewer was on vacation so I had to wait until Dec 7th to see it’s updated, he/she gave me a pass. On Dec 10th, the committee review was conducted. The commitee review had other SETC Principals​​ together with GeoUnit Managers to walk through my PSE, challenge my manager about my technical contributions, without my presence. It’s completed the day just 1 day before when my manager took vacation for the rest 2 weeks of the year, so I can call it, in last minute. Just 1 day later, it could take me some more years to complete.

I was proud to read the Committee Review Feedbacks:
Good PSE from IT function, well prepared though sometimes exceeding the required number of examples. Wide knowledge and exposure, not soloed into specific sub-domain. Good examples on supporting other functions and various campaigns. ​

That was a challenging process, sometimes I thought I would stop but finally, I made it. From a technical view, I think this challgenging process made the recognition fair, transparent and worth to experience.

Matching Excel files with PowerShell – Part 2

The next day, I was in good mood and decided to learn about function. I would change my code into a function.

function Identify-Leaver {
    # 3 parameters: Leaver Exel path and sheet name, PCOwner Excel path
    Param ([String]$LeaverExcel,[String]$LeaverSheet,[String]$PCOwnerExcel,[String]$NewSheet)
    #log file
    $logFile = 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\Log.txt'
    $totalscan = 0
    $foundcount = 0
    $notfoundcount = 0
    $LeaverList = Import-Excel -Path $LeaverExcel -WorksheetName $LeaverSheet
    $PCOwner = Import-Excel -Path $PCOwnerExcel
    Write-Output "Start scanning $LeaverExcel sheet $LeaverSheet and match with $NewSheet owner list" | Out-File $logFile -Append
    foreach ($Leaver in $LeaverList) {
         $isfound = 0
         $totalscan = $totalscan + 1
         foreach ($Owner in $PCOwner) {
            # if found, record to Found sheet, break loop
            if (($Leaver).GIN -like ($Owner).EmployeeNumber){
                $isfound = 1
                $foundcount = $foundcount + 1
                $found = $Owner | Export-Excel 'C:\Scripts\LeaverConfirm\Found.xlsx' -WorkSheetname $NewSheet -Append  
                break
            }

        }
    
        if ($isfound -eq 0){
            $notfoundcount = $notfoundcount + 1
            $notfound = $Leaver | Export-Excel 'C:\Scripts\LeaverConfirm\NotFound.xlsx' -WorkSheetname $NewSheet -Append
        }    
    
    }
    
    Write-Output "Scanned with $NewSheet Done" | Out-File $logFile -Append
    Write-Output "++ Total leaver scanned: $totalscan" | Out-File $logFile -Append
    Write-Output "++Found: $foundcount" | Out-File $logFile -Append
    Write-Output "++ Not Found: $notfoundcount" | Out-File $logFile -Append
    Write-Output "Found items recorded in C:\Scripts\LeaverConfirm\Found.xlsx sheet $NewSheet" | Out-File $logFile -Append
    Write-Output "Not Found items recorded in C:\Scripts\LeaverConfirm\Not Found.xlsx sheet $NewSheet" | Out-File $logFile -Append
    Write-Output "================================================" | Out-File $logFile -Append
}

Now to make it easier as I have many PCOwner files to compare, I placed all those files in to a PCOwner folder and run a script to automatically feed those files to my function:

$OwnerFolder = 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\PCOwner'
$OwnerFileList = Get-ChildItem $OwnerFolder | %{$_.FullName}
$numberoffiles = $OwnerFileList.Length
Write-Output "Found $numberoffiles files in Owner directory"
Write-Output "Scan file 0 "
Identify-Leaver -LeaverExcel 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\LeaverList.xlsx' -LeaverSheet 'Leavers' -PCOwnerExcel $OwnerFileList[0] -NewSheet '1' 
for ($i=1; $i -lt $numberoffiles; $i++){
    Write-Output "Scan file $i"
    $j = $i + 1
    Identify-Leaver -LeaverExcel 'C:\Users\lddthao\OneDrive - Schlumberger\Scripts\LeaverConfirm\NotFound.xlsx' -LeaverSheet $i -PCOwnerExcel $OwnerFileList[$i] -NewSheet $j 
}

It worked just fine with the screen output:

A piece of the log file:

The script run well but I couldn’t fix the challenge, there’re still 2xx GINs couldn’t be identified.

Matching Excel files with PowerShell – Part 1

I received a list of leavers in an Excel sheet with 2 columns: GIN and Country, I need to provide LDAP aliases of those GINs. The challenge was those GINs were already removed from LDAP system so LDAP queries couldn’t be done to get aliases. Luckily, our IT reporter kept records of PC owners every month, back to the last 18 months. The PC owner reports had GIN and LDAP aliases so I decided to use PowerShell script to match the GINs in the leaver list with the GINs in the PC owner lists and returned to me a list of aliases of the leavers.

---

I downloaded the Mar 2020 PC Owner list as this is the month that had the most number of owners, so I hoped I can find most of the aliases. I used the ImportExcel module to handle Excel files – first of all it needs to be installed from the repository: Start n privileged PowerShell prompt and use this command:

Install ImportExcel module

Then opened PowerShell ISE and started writing the script. I wanted to match 2 files: the leaver list and the PC owner list so I open them into 2 variables with Import-Excel command:

$PCOwnerMar = Import-Excel -Path ‘<Full Path to Excel file>’
$LeaverList = Import-Excel -Path ‘<Full Path to Excel file>‘

Import-Excel

Next step I want to get GIN in each row of the Leaver and try to check if any row in the PC Owner matched with it:

foreach ($Leaver in $LeaverList) {
     $isfound = 0
     $totalscan = $totalscan + 1
     foreach ($Owner in $PCOwnerMar) {
        # if found, record to Found Excel file with a specific sheet, break loop
        if (($Leaver).GIN -like ($Owner).EmployeeNumber){
            $isfound = 1
            $foundcount = $foundcount + 1
            $found = $Owner | Export-Excel 'C:\Scripts\LeaverConfirm\Found.xlsx' -WorkSheetname "Mar" -Append  
            break
        }

    }
    # if NOT found, record to NotFound Excel file with a specific sheet, break loop
    if ($isfound -eq 0){
        $notfoundcount = $notfoundcount + 1
        $notfound = $Leaver | Export-Excel 'C:\Scripts\LeaverConfirm\NotFound.xlsx' -WorkSheetname "Mar" -Append
    }    
    
}
# Display something on screen
Write-Output "Scanned with MAR Done"
Write-Output "++ Total leaver scanned: $totalscan"
Write-Output "++Found: $foundcount"
Write-Output "++ Not Found: $notfoundcount"

I used the $isfound flag variable to mark if a matched GIN can be found or not. I also want to know how many entries were scanned, how many found and not found. So I declared these variables:

$totalscan = 0
$foundcount = 0
$notfoundcount = 0

Then I run the script and saw that it scanned 732 entries of the Leaver list and could find only 396 matched entries. It meant I need to use PC Owner lists from other months to see if the rest of GINs could be found. Now I had 2 options:

• Use the previous code, replace the path to the PC Owner list with the list of other months and replace the leaver list with the NotFound Excel file to limit the scan to the unfound entries only.
• Write a function so that I can call it many times and put the file names as parameters

I tried the first option as at that time I didn’t have time to learn about function. After tried with other 7 or 8 months and saw that there were still about 3xx entries couldn’t be found, I called it a day and went to football.

Open BitLocker drive with Linux

Using Dislocker on Kali Linux

install: apt install dislocker

Step 1: Copy the BitLocker encrypted volume.
% dd if=/dev/sda2 of = encrypted.bitlocker
This will copy the entire volume located into /dev/sda2 to encrypted.bitlocker

Step 2: Unlock Bitlocker encrypted drive.
% dislocker-file -V encrypted.bitlocker -p563200-557084-108284-218900-019151-415437-694144-239976 — decrypted.ntfs
This will decrypt Bitlocker encrypted drive into decrypted.ntfs using the recovery key.
% dislocker-fuse -V encrypted.bitlocker -f /path/to/usb/file.BEK — /mnt/ntfs
This will create a file into /mnt/ntfs named dislocker-file if the encrypted volume has a clear key enabled.

Step 3: Mount Bitlocker encrypted drive once it is decrypted.
% mount -o loop ntfs/dislocker-file /mnt
Then mount it on a file into /Volumes for instance:
% mkdir /Volumes/blah && mount -t ntfs /dev/disk1 /Volumes/blah

Summary 31 Mar 2020

• Watched Full Ethical Hacking Course – The Cyber Mentor till 6:18:49
  • Scanning tool: Nmap
  • Intensive scan all ports: Nmap -T4 -A -p- <IP>
• Should review the previous sessions to do summary notes
• Kali Virtual Box crashed! Removed VB to reinstall
• Installed Nmap on Windows
• Vietnam PM announced “Social Quarrantine” from tomorrow in 14 days

Back to coding

From September, when I got Scrum Master cert, I got involved into 2 PowerApps projects. I had the autonomy to define my role in those projects so I decided to be Scrum Master of 1 project to apply the new knowledge, and to be coder for the more complex one, which I believe my teammates will struggle with.

In 3 months, I spent most of my time for the projects, and more for coder role because I had to built the framework for the first project and help my teammates through out the project timeline. 13 years since I left the university, I was back to coding work. My feeling was quite good, I was back to the time struggling with bugs, feeling happy after working a problem out, typing hours and hours without time awareness – it was really like I was a student.

And finally my 02 projects were completed successfully, delivered what my product owners expected. I felt a bit bad when they didn’t win any prize from the company contest, but anyway, we worked really hard and learnt many things from SCRUM project management and PowerApps coding.

How “free” wifi hotspots track your location

My post in the company Cyber Security Yammer group.

The price for “free” Wifi hotspot

Nowadays, “free” Wifi hotspot can be found everywhere, in shopping malls, restaurants, cafes, airports etc. This of course helps people get online easier, which becomes a vital demand in modern life. But nothing is “free”, this convenience often come with a price: your personal data and privacy.

When you use “free” Wi-Fi, there’s a good chance it’s managed by a third-party provider—which gets you online in exchange for your valuable sign-on data. The sign-on information that hotspots require will vary, but often includes your email address, phone number, social media profile, and other personal information. All can be used to target you with advertising and gain insights on your habits. That’s probably not a surprise to most Wi-Fi hotspot users. But what might surprise you is that some hotspot providers are taking data collection a step further, and quietly tracking millions of users’ whereabouts even after they’ve left an establishment.

Read the Captive portal

When you connect to public Wi-Fi, you’ll usually be greeted with a sign-in form, also known as a “captive portal.” This is where you provide personal information and consent to terms of service to get online. If you read the portal carefully, you will notice most of the hotspots providers say “by clicking ‘go online,’ you agree to our terms of use and privacy policy,” with the “terms and policy” may allow them to track your location over time. Some will say more explicitly “you agree to provide this device’s location” next to where you can tick a box to consent.

What distinguishes location-based marketing hotspot providers is that the personal information you enter in the captive portal—like your email address, phone number, or social media profile—can be linked to your laptop or smartphone’s Media Access Control (MAC) address. That’s the unique alphanumeric ID that devices broadcast when Wi-Fi is switched on.

You’re followed by your MAC addresses

The hotspot providers often say that this location-tracking function brings benefit to users: users is one-click access to Wi-Fi anywhere under that provider’s coverage after the first establishment. But that’s not the only way it can be used.

MAC addresses alone don’t contain identifying information besides the make of a device, such as whether a smartphone is an iPhone or a Samsung Galaxy. But as long as a device’s MAC address is linked to someone’s profile, and the device’s Wi-Fi is turned on, the movements of its owner can be followed by any hotspot from the same provider.

Analyzing MAC signals from mobile phones can be valuable for retailers and others to calculate wait times, understand peak versus off-hours, or assign staff. However, location data is highly sensitive when linked to an individual over time and across venues. This can reveal a detailed profile of someone’s daily habits. Where they shop, where they live, and what places they frequent at certain times could be laid bare by this data.

How to protect yourself from being tracked by ‘free’ Wi-Fi

If you’re concerned about data being collected by free Wi-Fi hotspots, there are some simple steps you can take to protect your personal information.

• Don’t use “free” Wi-Fi: The most obvious solution to protecting your data from free Wi-Fi networks is not to use them at all. Alternatives include using the data services from your cellular provider.
• Disable Wi-Fi when you’re not using it: Enabling Wi-Fi lets these hotspots track you (and also drains your battery faster).
• Read the privacy policy: It’s tempting to skip reading the privacy policy, but if you take a few minutes to do so, you can learn how the Wi-Fi service is collecting your data and where it might end up. Keywords to look for are “MAC address,” “location,” “collect,” and “share.”
• Opt-out of location tracking and delete your data: Location analytics companies let you opt-out of location tracking and delete your data, though some opt-outs are easier than others. How to opt out can be found in a privacy policy. You’ll be given a chance to review the policy before you sign into a captive portal, or you can find it on the hotspot provider’s website.
• Randomize your MAC address: Since version P, Android has added a feature that allows you to randomize your smartphone’s MAC address to improve privacy. This lets you generate a new MAC address for every Wi-Fi hotspot you connect to, effectively stopping these companies from tracking you. You can switch on MAC randomization under Developer Options. There’s no need to go through a similar process on iPhones and iPads running iOS 11 and up, which automatically randomize their MAC address when scanning for Wi-Fi. “Because a device’s MAC address now changes when disconnected from a Wi-Fi network, it can’t be used to persistently track a device by passive observers of Wi-Fi traffic, even when the device is connected to a cellular network,” according to Apple’s iOS Security Guide. However, Apple also says “Wi-Fi scans that happen while trying to connect to a preferred Wi-Fi Network aren’t randomized,” meaning a hotspot a device has connected to previously will be able to detect the device’s actual MAC address.
• Don’t sign in with social media: It may be convenient and quicker to sign in with Facebook, Twitter, or LinkedIn, but it’s also ideal for data harvesters. Your social profile, especially your Facebook “likes,” reveals a wealth of information about you.

SCRUM Master Certification

I failed the first time and passed the second time, after retaking the mock exam many times and review the materials carefully. Nice experience.

SCRUM Master Training

I’ll attend Professional SCRUM Master class next week in Kuala Lumpur. Time to upgrade my portfolio. To prepare, I’ve studied a pathway in Degreed, and did the Scrum open asssessment.

Here are some good references:

• Open assessments
• Scum Guide
• Tips for PSM I Exam

This ScrumCrazy blog has a lot of useful information about Scrum.