[Case study] Conditional formatting on excel with customized “first day of the week”

Recently, we have a task to highlight the schedule in excel for last week and this week presenting with different color. However, the conditional formatting function come with Excel doesn’t not meeting our requirement. According to international standard ISO 8601, Monday is the first day of the week. It is followed by Tuesday, Wednesday, Thursday, Friday, and Saturday. Sunday is the 7th and last day of the week.

There is not option to customize the predefined function in conditional formatting, that’s said we need to DIY a new function with formatting rule. By using function WEEKNUM([DATE],2), we can get the actual week number of the [DATE] which comes with ISO8601 standard.

Therefore, using =WEEKNUM(A1,2)=WEEKNUM(TODAY(),2) can get the [DATE] result for this week; =WEEKNUM(A1,2)=WEEKNUM(TODAY(),2)-1 can get the [DATE] result for last week.

Assign O365 license by PowerShell

Assigning license to a group of O365 users are always the most headache task for administrators. So why don’t we script it? Let’s start!

First of all, check the license plan and usage of your company.

Connect-AzureAD #Connect to the tenant

Get-AzureADSubscribedSku | Select SkuPartNumber #Show account service plan and grep the SkuPartNumber (e.g, Microsoft 365 E3 = ENTERPRISEPACK, Microsoft 365 F1 = SPE_F1)

#Check SKU Service Plan detail option from the above output. Let's say if there are 22 SkuPartNumber in your service plan, you would like to select the 19th one, fill in 18 in the array of $license
$licenses = Get-AzureADSubscribedSku

Copy the output, so now you got all of the information of your account license that you want to add. The next step is define the service plan you would like to activate for the group of users. Create a csv file, define the license option (The output above); Friendly Name; Add License Option (True or False). Below are the example, we would like to enable Microsoft Search, Skype for Business, Office Online and Exchange Online but disable Microsoft Teams.

MICROSOFT_SEARCH;Microsoft Search;yes
TEAMS1;Microsoft Teams;no
MCOIMP;Skype for Business Online;yes
SHAREPOINTWAC;Office Online;yes
EXCHANGE_S_DESKLESS;Exchange Online Kiosk;yes

Create another csv and import the SMTP addresses that you would like to add the licenses.

[email protected]
[email protected]

Finally, the main PowerShell script.

# Powershell for license assignment                                                                                #
# Author: Wallace Ho                                                                                                     #
# Version: 1.0                                                                                                           #

$users = Import-Csv "C:\Users\wallaceho\Desktop\Licenses\users.csv" -Delimiter ";" # Define SMTP address CSV file path
$additionalOptions = Import-Csv "C:\Users\wallaceho\Desktop\Licenses\licenseplan.csv" -Delimiter ";" | ?{$_.addLicenseOption -eq "no"} #License path filter for disable option

foreach ($user in $users) #for each user in the SMTP address CSV file path
    $opts = New-MsolLicenseOptions -AccountSkuId "<accountid:serviceplan>" –DisabledPlans $additionalOptions.LicenseOption #Define disable option, remember to change the <accountid:serviceplan>
    Set-MsolUserLicense -UserPrincipalName $user.smtpAddress -RemoveLicenses <accountid:serviceplan>  #Remove old license, remember to change the <accountid:serviceplan>
    Set-MsolUserLicense -UserPrincipalName $user.smtpAddress -AddLicenses <accountid:serviceplan> -LicenseOptions $opts #Add new license, remember to change the <accountid:serviceplan>

HTTPS SSL on CloudFlare

Nowadays, many of the server hosts are using CloudFlare to speed up and secure their site, but how can you deploy the SSL certificate after your site being redirected to CloudFlare? Actually there are many ways to do that, some of the options are even for FREE!

Cloudflare SSL operates in different modes depending on the level of security required and the amount of configuration you’re willing to do. Traffic to the end user will always be encrypted, which means your website will always enjoy the benefits of HTTPS. However, traffic between Cloudflare and your origin server can be configured in a variety of ways.

Flexible SSL
Flexible SSL encrypts traffic from Cloudflare to end users of your website, but not from Cloudflare to your origin server. This is the easiest way to enable HTTPS because it doesn’t require installing an SSL certificate on your origin. While not as secure as the other options, Flexible SSL does protect your visitors from a large class of threats including public WiFi snooping and ad injection over HTTP.

Full SSL
Full SSL mode provides encryption from end users to Cloudflare and from Cloudflare to your origin server. This requires an SSL certificate on your origin server. In Full SSL mode, you have three options for certificates to install on your server: one issued by a Certificate Authority (Strict), one issued by Cloudflare (Origin CA), or a self signed certificate. It is recommended that you use a certificate obtained through Cloudflare Origin CA.

Origin CA
Origin CA uses a Cloudflare-issued SSL certificate instead of one issued by a Certificate Authority. This reduces much of the friction around configuring SSL on your origin server, while still securing traffic from your origin to Cloudflare. Instead of having your certificate signed by a CA, you can generate a signed certificate directly in the Cloudflare dashboard.

IT seminar – IBM cloud & AI; China Cyber security law

Recently I went to two quite interesting seminar and both are kind of a very hot topic.

The first one is discuss about the new China cyber security law. Nowadays, most of the registration in China needs to provide real personal information for real-name authentication. It is include but not limited to travelling buses,  Express Rail Link, Free Wi-Fi access, e-payment….. The one we are concerning for IT infrastructure is providing free Wi-Fi for guest. A real-name authentication and log system must be implemented. There are two real-name authentication method: 1. SMS, 2. Wechat authenticate. After the authentication, the system must also store at least 6 months logging including all access logs and DHCP logs that can be traceable. All log must be upload to the government system by daily. Therefore, if any guest use the public Wi-Fi to do any illegal things, the host of the service will not get into any trouble. Continue reading

Video broadcasting with Flowplayer HLS

To support HTML5 live video streaming for iOS device, there are only two options.

  • HLS
  • WebRTC (for limited codecs)

The pros of HLS is supporting the most common browsers and Operating Systems. However, compare with other live streaming methods such as flash, websockets, RTMP… The latency of HLS is much higher, it will have approx 30-45 seconds delay. Due to the requirement, I have no choice to choose HLS as the option.

First of all,  build a web server using IIS or Apache. Install VLC player on the same machine.

Set immediate expiration on the web folder. For IIS web server, open IIS Manager, select your web folder -> HTTP Response Headers -> Set Common Headers -> Expire Web Content Immediately. In addition, as the .m3u8 file is going to be consumed by web players such as Flowplayer, you need to put crossdomain.xml file in your web root folder (for IIS, c:\inetpub\wwwroot) and to enable cross-origin access (CORS) for your HLS web folder by adding “Access-Control-Allow-Origin: *” custom HTTP header. For details please refer to https://enable-cors.org/server.html .

Start streaming RTSP with Xsplit with VLC player (Ref my previous post)

Launch VLC player with the below command to re-stream RTSP feed as HTTP Live Stream

vlc -I dummy rtsp://ip:port/live--sout '#transcode{vcodec=h264,fps=20,vb=512,scale=1,acodec=none,venc=x264{aud,profile=high,level=60,keyint=15,bframes=0,ref=1,nocabac}}:duplicate{dst=std{access=livehttp{seglen=10,delsegs=true,numsegs=10,index=c:\inetpub\wwwroot\mystream.m3u8,index-url=http://ip/live/mystream-########.ts},mux=ts{use-key-frames},dst=c:\inetpub\wwwroot\mystream-########.ts},dst=std{access=http,mux=ts,dst=:8082/video.mp4}}'

Ok! Now we can start the http part. First define the flowplayer skin & JS

<!-- Flowplayer skin -->
<link rel="stylesheet" href="//releases.flowplayer.org/7.0.4/skin/skin.css"> 
<!-- Flowplayer library -->
<script src="//releases.flowplayer.org/7.0.4/flowplayer.min.js"></script>
<!-- The hlsjs plugin for playback of HLS without Flash in modern browsers -->
<script src="//releases.flowplayer.org/hlsjs/flowplayer.hlsjs.min.js"></script> 

Then define javascript to run the player with specific parameter handling.

window.onload = function () { 
flowplayer("#hlsjslive", { 
splash: true, ratio: 9/16, clip: { live: true, 
sources: [ 
{ type: "application/x-mpegurl", 
src: "http://ip:port/mystream.m3u8" } ] } 

Finally, define the container in the HTML for the player using <div>

<div id="hlsjslive" class="fp-slim"></div>