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.

Posted in Windows Tutorial | Leave a comment

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.

Posted in Windows Tutorial | Leave a comment

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

Posted in Linux Tutorial | Tagged ,

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
Posted in Working Diary

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.

Posted in Working Diary | Tagged ,

How “free” wifi hotspots track your location

My post in the company Cyber Security Yammer group.

How “free” wifi hotspots track your location

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.
Posted in Security

SCRUM Master Certification

ThaoCertificate

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

Posted in Working Diary | Tagged

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:

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

Posted in Working Diary | Tagged

Enhanced IT Disposal app

I created my first PowerApps in this post, to help me in building IT disposal assets list. It’s a very simple app with very basic PowerApps functions.

Then in this Jul I joined the PowerApps Hackathon contest organized by the company. I built an app to provide customer details for the Sales team while they’re traveling to customer meetings. Even though I didn’t have any prize in this contest, I learnt a lot in PowerApps, from designing to development. After the contest, I applied what I’ve learnt to enhance my IT disposal app, with new updates:

  • New appearance theme
  • Revise source code, apply naming convention
  • New features:
    • Select Country and Site
    • Build and manage Disposal list for many sites. This is helpful for IT persons who manage many sites. I applied what I learnt about using Collections in this function, it worked very well.
    • Send Email with the list of items in the selected site.
    • Help screen

Some screenshots:

Posted in Programming, Working Diary | Tagged

PowerApps Hackathon

I registered for my TMV team to join the PowerApps Hackathon contest on 18-19 June 2019. Our app named Customer X-Ray. Some information about the app:

Problem Description

Currently it is very difficult (especially for Sales community) to check the real-time status of our clients (current credit limit, applicable payment terms and open/ overdue balance). This is an information that Sales community does not have access to or it takes time to obtain it. Management and Sales are actually blindsided when they are visiting a customer and discussing/ negotiating an opportunity or contract because they don’t have this useful an critical information on hand which can make the difference in the negotiation process.

Why do you think the app will solve the problem?

The proposed solution is to have a mobile app developed. The Sales community (and anyone else in need) can install the app on their mobile and with one click they can get the full picture of the client that they are visiting or negotiating with.

They can instantly see their current assessment (credit limit and payment terms), current open balance, outstanding balance and remaining credit not consumed.

The data can be directly fed from Sales system which is updating daily.

The app will deliver notifications to the user’s mobile when a client in their portfolio has breached the credit limit ceiling, when the credit limit for a client has been upgraded or downgraded, when different payment terms have been applied. Continue reading

Posted in Programming, Working Diary | Tagged