Reconfiguring Azure AD Sync – rip and replace!

I had an interesting learning experience recently, whilst working with a customer to implement some Microsoft Online services.

They have an existing AAD Sync installation, although from time to time that stops working when Microsoft changes the IP addresses of the servers that are needed for synchronisation. This is not a recommended configuration – but the reasons why are well-described in David Ross’ post on using a proxy with Azure AD Sync Services. To limit the number of IP addresses in their firewall and router configurations, this customer places hosts file entries on the Azure AD Sync server, meaning that Azure AD Sync only uses two IP addresses to find the hosts:

134.170.172.140        adminwebservice.microsoftonline.com
191.235.135.139        login.microsoftonline.com

Microsoft publishes a full list of Office 365 URLs and IP addresses, together with an RSS feed for changes.

Anyway, to cut a long story short, my customer created a test environment by cloning existing servers into Azure IaaS. I ran IdFix against test directory objects, changed the UPN on the user accounts to match the domain we had associated with Office 365 (test.companyname.com) and ran the Microsoft Azure Active Directory Sync Services tool (directorysynctool.exe) to set Azure AD Sync up with the new, test Office 365 tenant. Then I sat back and waited for the changes to sync.

To my horror, I found that the changes didn’t sync to the test Office 365 tenant, but to production! Running miisclient.exe confirmed that the original connectors were in place and had had not been changed by re-running the Directory Sync Services tool.

Unfortunately, because the production AAD Sync server was unable to connect to Azure (due to IP address changes…), we couldn’t force a sync from that server to overwrite the stale directory information, which meant late night working was needed to get emergency changes in place and restore service.

Once the production AAD Sync was up and running again, the live directory data was re-synced to Azure AD and services that relied on this (Intune-managed mobile devices were the obvious ones) started working again.

As expected, the sync with the correct directory over-wrote the changes from the stale directory and the login names for those users that had changed to @tenantname.onmicrosoft.com (because their UPN from the test domain was not valid in the production tenant) reverted to the correct UPNs (which have verified domains in the tenant).

In the cold light of day, I realised that the issue was not caused by me – the only reason synchronisation from the test environment hadn’t over-written the live directory sooner was that the test AAD sync server didn’t have Internet access and then I’d disabled the scheduled task whilst running the Directory Sync Services tool. Once it was enabled it simply did its job – but the key learning point for me is that reconfiguring Azure AD Sync is not as simple as re-running the Directory Sync Services tool and supplying the necessary details – it really needs to be ripped out and run from scratch because directly editing the connectors is unsupported:

Microsoft does not support modification or operation of the Directory Sync tool outside of those actions formally documented.  […]  Unsupported actions include:

  • Opening the underlying FIM Sync Engine to modify Connector configuration
  • Manually controlling the frequency and/or ordering of Synchronization Run Profiles or changing the attributes that are synchronized to the cloud.

Any of these actions may result in an inconsistent or unsupported state of the Directory Sync tool and as a result, Microsoft cannot provide technical support for such deployments / usage of the tool. Filtering configurations applied to your directory synchronization instance aren’t saved when you install or upgrade to a newer version. If you are upgrading to a newer version of directory synchronization, you must re-apply filtering configurations after you upgrade, but before you run the first synchronization cycle.

Bulk changing Active Directory UPNs from PowerShell

As part of my current Office 365 project, I needed to prepare an on-premises Active Directory for synchronisation with Azure AD. This was a test environment that had been created by taking a copy of the production directory, so I had thousands of users – but all with incorrect user principal names (UPNs) that needed to be changed to a new value @test.domainname.tld.

I added the new UPN to the forest in Active Directory Domains and Trusts, then ran the following PowerShell for each OU that contained users I was going to synchronise with Azure AD (discovered via David O’Brien):

Get-ADUser -Filter * -SearchBase 'OU=Employees,OU=Users,OU=CompanyName,DC=DomainName,DC=tld' -Properties userPrincipalName | foreach { Set-ADUser $_ -UserPrincipalName "$($_.samaccountname)@test.domainname.tld"}

The command failed when I ran it on the domain controller (as did the script I originally tried) but when I used PowerShell on another server that was a member of the domain (my Azure AD sync server), it worked. This forum post suggests that it can run locally if you use the -server parameter but I haven’t tried that.  Just be sure to run Import-Module ActiveDirectory first, or else the *-ADUser commands won’t be available.

Export transport rules from Exchange or Exchange Online

After all my work last week creating Exchange transport rules for profanity, audio/video attachments, message encryption and more, I wanted to export the rules just in case they needed to be re-established.  Thanks to TechNet, I found the required PowerShell to export transport rules from Exchange or Exchange Online, which is:

$file = Export-TransportRuleCollection
Set-Content -Path "ExchangeOnlineRules.xml" -Value $file.FileData -Encoding Byte

The resulting XML includes the New-TransportRule commands to re-create the rules if required (or the Import-TransportRuleCollection cmdlet can be used instead).

Getting back to BASIC (Microsoft Small Basic)

I’ve written before about my son’s interest in computer programming (well – computers in general, as it seems he provides IT support for his class!). It started with Scratch (at school), we went to an Apple Store for an Hour of Code last year and now, inspired by watching episodes of the BBC’s Making the Most of the Micro series from 1983, he’s moving up to BASIC.

After a short hunt on the ‘net I found a Windows port of BBC BASIC but I wondered if he might be better using something that prepared him for other, more modern, languages (my own path went something like RM BASIC, Turbo Pascal, Modula-2, 68000 assembly, COBOL, SQL, C, C++, VisualBasic, HTML/CSS, C# – although in truth the closest I get to writing code these days is a little bit of PowerShell every now and again). Then I found Microsoft Small Basic. After a late-night on Friday getting back to BASIC myself (with a multiplication tables programme which I’m sure professional coders will baulk at, inspired by Ian McNaught-Davis in episode 2 of Making the Most of the Micro), I felt I’d re-familiarised myself enough with BASIC to get my son started – and he really took to it, moving on to graphical windows on Saturday afternoon.

I started out looking at Beginning Small Basic (there are other Small Basic programming books available online too) but the Small Basic reference documentation in the TechNet Wiki came in useful too (like when looking up the available colours).

If I have one gripe with Small Basic, it’s that it doesn’t seem to understand multi-user Windows computers: I installed it using my account, but it wasn’t visible when my son logged in; I reinstalled and now it’s not there for me. Nevertheless, it’s a great way to get stuck in to programming, before “graduating to Visual Basic” or hopefully he’ll be learning something else, like Python, at school soon.

It’s interesting to see how today’s nearly-11-year-olds view the computers of 1983 (by co-incidence, 1983 was the year when I turned 11 too…). Green screens, cassette tape input, floppy disks (none of those new-fangled 3.5″ disks either), dot matrix printers, character-based interfaces (only a few days previously he had asked me what I was doing in cmd.exe) – will my grandchildren view touch screens and patchy mobile phone networks in a similarly quaint manner in 2047?

Just for reference

My first Small Basic programme is below (although WordPress has stripped out the indentation). I’m hoping my son can do much better!

Start:
TextWindow.Title = "Multiplication Tables"
TextWindow.Write("How many tests would you like? ")
Tests = TextWindow.Read()
Loop = 0
Right = 0
Wrong = 0
For Loop = 1 To Tests
TextWindow.BackgroundColor = "Blue"
TextWindow.ForegroundColor = "White"
FirstNumber = Math.GetRandomNumber(12)
SecondNumber = Math.GetRandomNumber(12)
Result = FirstNumber * SecondNumber
Output = "What is " + FirstNumber + " multiplied by " + SecondNumber + "? "
TextWindow.Write(Output)
Answer = TextWindow.Read()
If Answer = Result Then
TextWindow.BackgroundColor = "Green"
TextWindow.WriteLine("Yay")
Right = Right + 1
Else
TextWindow.BackgroundColor = "Red"
TextWindow.WriteLine("Uh, Oh!")
Wrong = Wrong + 1
EndIf
EndFor
TextWindow.BackgroundColor = "Purple"
Output = "You got " + Right + " correct answers and " + Wrong + " incorrect answers"
TextWindow.WriteLine(Output)
TextWindow.WriteLine("Would you like to try again? ")
Answer = TextWindow.Read()
If Answer = "Yes" Or Answer = "yes" Then
Goto Start
Else
Goto End
EndIf
End:
TextWindow.BackgroundColor = "Black"
TextWindow.WriteLine("Goodbye")

Cutting laminate worktops – a fine blade makes a huge difference

Mrs W has promised our eldest son a larger bedroom. That means a smaller office for us… and lots of clearing out plus a week’s DIY planned for October…

Last weekend’s trip to IKEA in preparation also taught me that a) they don’t make the Pragel counters I currently use in the office any more; and b) the Linnmon table tops I’m using in the new office need to be cut down to fit (because they are 2000x600mm and the room is 2590mm wide – so the tops will be 1cm too long to fit in an L-shape!).

My last attempt at cutting laminate tops was pretty awful. Thank goodness the cut edge sits in the corner, against the wall, hidden by the computer monitor on top of it because the chipping is so bad. And I used masking tape along the cut on the finished – as well as cutting from the underside.

Mrs W did say I could buy new tools… and the guy in IKEA was clearly trying to convince her I needed do do some Makita shopping but, to be perfectly honest, my JCB-branded-Chinese-import-bought-from-a-DIY-shed-circa-2002 only gets used once a year and the biggest job was our decking… circa 2002…

20 teeth circular saw blade is no good for cutting laminate worktops
This is not a fine blade!

It turns out the problem was the blade I used. Lots of reading about cutting laminate worktops told me to get a fine blade… but how do you define a “fine” blade circular saw? One post I read said 24-30 teeth. So I bought one on Amazon that had 100 teeth – that should do the job, right?

Well, a test cut today suggests so – look at the picture here and you can see my old ragged (20 teeth) cut on the left, and the new one on the right (100 teeth). Still not perfect but passable…

On that basis, I’ll risk it. And if it all goes horribly wrong, its only £25 for another counter top… or I could always chip a centimetre out of the plasterboard wall instead!

Further reading

Cutting Pragel countertops (via The Wayback Machine, as IKEA’s lawyers appear to have forced closure of the original Ikeafans website).

Skype account unlocked with the help of the registration timestamp in the client database

A couple of weeks ago, I wrote a post about trying to get my Skype account unlocked and the arcane questions I was being asked by Skype Customer Support. I’d been meaning to write that post for a while… and I’m glad I finally got around to it because John Buston (a former colleague from my days at Fujitsu) pointed me at some advice in the Skype Community that proved to be very useful indeed

The Skype client (which I still had access to on one of my PCs) uses an SQL database called main.db, found in %appdata%\Skype.  The SQLite browser can be used to view the contents of this database and locate the registration_timestamp in the Accounts table.

Registration timestamp inside the Skype client database (main.db)

Take the number from the registration timestamp, multiply it by 60 and you’ll have the Unix time (the number of seconds since 1 Jan 1970, UTC) from when you registered for Skype. Put that number into a converter (like this one) and you’ll have the exact time when you registered for your Skype account.

Armed with that information (and some more that I could guess, like the email address I used to register for Skype; plus some more that I knew, like my personal details and some names from my Skype contact list) and I was finally able to get my account unlocked.

Thanks again John!

Getting to grips with Office 365 Message Encryption

As part of my work this week with Exchange transport rules, I needed to recreate another facility that my customer has grown used to in Office 365 – the ability to selectively encrypt emails using keywords.

This one turned out to be relatively straightforward – Office 365 Message Encryption has been around for a while now (it replaced Exchange Hosted Encryption) and I was able to use a transport rule to detect a phrase in the subject or body (“encrypt me please”) and apply Office 365 Message Encryption accordingly. I could equally have done this based on other criteria (for example, I suggest that any message marked as confidential and sent externally would be a good candidate).

So, the rule is fairly simple:

New-TransportRule -Name 'Encrypt email on request' -Comments ' ' -Mode Enforce -SubjectOrBodyContainsWords 'encrypt me please' -ApplyOME $true

Office 365 Message Encryption needs Azure RMS

The challenge for me was that I wasn’t creating it in PowerShell – I was using the Exchange Admin Center and the appropriate options weren’t visible. That’s because Office 365 Message Encryption needs Azure Rights Management Services (RMS) to be enabled, and it’s necessary to use the More Options link to expose the option to Modify the Message Security… from which it’s possible to Apply Office 365 Message Encryption.

Unfortunately that still didn’t work and the resulting error message was:

You can’t create a rule containing the ApplyOME or RemoveOME action because IRM licensing is disabled.

It seems it’s not just a case of enabling RMS in the service settings. I also needed to run the following commands in PowerShell:

Set-IRMConfiguration –RMSOnlineKeySharingLocation “https://sp-rms.eu.aadrm.com/TenantManagement/ServicePartner.svc”

(that’s the European command – there are alternative locations for other regions listed in the post I used to help me)

Import-RMSTrustedPublishingDomain -RMSOnline -name "RMS Online"
Test-IRMConfiguration -RMSOnline

(check everything passes)

Set-IRMConfiguration -InternalLicensingEnabled $true

With RMS/Information Rights Management (IRM) properly enabled I could create the rule as intended.

Customising the experience

Testing my rule was easy enough, but it’s also possible to customise the portal that recipients go to in order to read the encrypted message.

This is all done in PowerShell, with some simple commands:

Get-OMEConfiguration provides the current Office 365 Message Encryption configuration and to set the configuration to meet my requirements, I used:

Set-OMEConfiguration -Identity "OME Configuration" -Image (Get-Content "markwilsonitlogo.png" -Encoding byte) -PortalText "markwilson.it Secure Email Portal" -EmailText "Encrypted message from markwilson.it"

The tricky bit was working out how to provide the logo file as just the filename creates a PowerShell error and the Get-Content cmdlet has to be used to encode the file.

Further reading

Office 365 Message Encryption (and decryption) – steps – understanding, purchase options, configuration, branding and use.

Exchange transport rules to detect audio/video attachments

After my fun creating a profanity filter for Exchange Online earlier this week, my attention turned to some of the other rules that my customer needed re-creating in preparation for the move to Office 365. Most were fairly straightforward blocks on certain domains/addresses or using the normal templates to prevent financial data from being leaked, etc. but then I found another one that I’d expect to be in included in Exchange Online Protection, but isn’t: copying any audio/video files emailed from within the organisation to a defined mailbox.

The rule itself is quite simple, but the number of file extensions involved meant I actually needed 4 rules to avoid this error message:

The rule can’t be created because it is too large. It has 9028 characters, and the maximum number of characters is 8192.

Reduce the size, either by removing content, such as words or regular expressions, from the rule; or by removing conditions, exceptions, or actions from the rule.

After chunking the attachment extensions, the final Exchange transport rules used to to detect audio/video attachments were:

New-TransportRule "Notify Security if outbound email contains audio (1)" -AttachmentExtensionMatchesWords 'afc','vag','copy','vdj','sng','aob','act','ang','nra','hsb','rfl','sma','smp','syh','vyf','acm','at3','vmd','aimppl','nvf','saf','xfs','ins','alac','mod','omf','sfk','als','caf','gp5','wav','mp3','pla','abm','aup','wma','acd-zip','amxd','dmsa','dmse','emp','logicx','m4r','midi','ptx','rns','rx2','slp','trak','5xb','a2b','a2i','agr','akp','asd','bnk','bun','bww','csh','dfc','dsm','dtm','fev','flp','frg','g726','gsm','h5b','h5s','isma','krz','ksf','mbr','mmlp','mpga','mtp','musx','nkc','nkm','omg','pkf','r1m','rex','rip','rol','sbi','sfpack','smf','sseq','svd','syw','tg','u','uax','vpl','zvd','0.669','eop','mus','sf2','mid','ksd','aif','flp','oga','pcg','sty','dig','mscz','ogg','m3u','flac','sib','aiff','syx','zab','dss','gpk','xspf','mui','vlc','nbs','5xe','logic','minigsf','sd','sdat','wve','ins','cda','ram','aac','iff','nki','wave','wpk','dff','amr','3ga','dcf','aud','cwt','dls','ds2','flm','nsa','it','pcm','pho','q1','sns','sph','xwb','dsp','sam','u8','wand','ym','ac3','oma','sds','stm','acd','dsf','cpr','xa','m3u8','ftm','4mp','apl','cwp','cws','gpbank','gsflib','med','mo3','mx5','ply','qcp','rmj','w64','ahx','au','b4s','h0','h3e','hbb','hbs','ins','kit','kmp','ksc','mdl','mu3','phy','q2','sbg','sfap0','smp','toc','vgz','vmf','zpa','2sf','m4a','ds','nsf','sesx','ape','fls','mus','emx','pcast','dtshd','mmm','peak','vox','bmml','mscx','xmf','rtm','pls','sfl','xm','avastsounds','snd','voc','wax','wpp','ra','cdr','seq','gpx','au','aa','m4b','odm','mpa','amz','5xs','a2m','abc','acd-bak','adts','agm','aifc','alc','amf','band','bap','bdd','bidule','bwf','caff','cdda','cdlx','cdo','cel','cgrp','cidb','ckb','conform','cpt','cwb','dct','dewf','df2','dig','dm','dmf','dra','drg','dwd','efk','efq','efs','efv','emd','esps','f2r','f32','f3r','f4a','f64','fdp','fsb','fsc','fsm','ftm','ftmx','fzf','fzv','g721','gig','groove','gsf','h4b','hbe','igp','iti','koz','koz','kt3','la','lso','lwv','m4p','ma1','mdc','mgv','miniusf','mka','mmp','mmpz','mpc','mte','mti','mtm','mus','mux','narrative','nkb','nks','nkx','nml','note','nrt','nst','ntn','nwc','obw','okt','omx','ovw','pandora','pca','pek','pna','psm','ptm','pts','rax','rgrp','rmi','rmx','rng','rso','rti','s3i','sc2','scs11','sd2','sfz','sgp','smpx','sou','sppack','sprg','stap','sty','sxt','syn','td0','tta','txw','ult','uni','usf','usflib','ust','uw','uwf','vap','vc3','vmo','voxal','vpm','vpw','vrf','vsq','wfb','wfm','wfp','wow','wproj','wrk','wus','wut','wv','wvc','wwu','xmu','xrns','yookoo','adv','cmf','dmc','gmc','mp_','ppcx','sbk','sid','sng','vgm','6cm','8med','a52','al','d01','evr','fda' -GenerateIncidentReport security
New-TransportRule "Notify Security if outbound email contains audio (2)" -AttachmentExtensionMatchesWords 'gsm','kin','mini2sf','pd','prg','record','rmf','tmc','tun','wyz','xp','xt','kar','vb','wem','adg','dts','kfn','pk','mxl','mtf','ncw','dw','igr','vce','ddt','k25','sf','dvf','aa3','adt','fpa','h5e''mpdp','ove','rbs','sd','slx','stx','swa','vsqx','w01','zpl','mmp','opus','ppc','rsf','sdt','wav','xa','xpf','xsb','brstm','tak','ptf','efa','g723','mmf','s3m','sap','vqf','2sflib','avr','ear','mp1','dcm','ay','zvr','pat','ams','cts','gbs','ics','k26','mp2','mts','myr','ots','psf','rsn','ses','shn','snd','a2p','a2t','a2w','ab','acp','ais','alaw','all','apf','aria','ariax','axa','bwg','c01','ckf','djr','efe','emy','erb','far','fti','gbproj','gym','h3b','h4e','hdp','iaa','imp','itls','its','jam','jam','kpl','kt2','l','lof','lqt','m','m1a','m2','minipsf','minipsf2','mogg','mpu','mt2','mux','mx3','mx4','mx5template','npl','ofr','ovw','pbf','pjunoxl','plst','pno','prg','psf1','psf2','psy','ptcop','pvc','rad','raw','rbs','rcy','rmm','rta','rts','rvx','s3z','sd2f','spx','sseq','ssnd','svq','svx','thx','tsp','ub','ulaw','v2m','vmf','vtx','wtpl','wtpt','xbmml','xmi','xmz','xsp','zgr','atrac','box','fzb','hmi','imf','sdx','aax','sb','cfa','mxmf','pac','d00','8svx','ams','wfd','msv','xi','nmsv','ase','awb','expressionmap','hma','hps','mlp','mzp','sfs','snd','tak','8cm','gm','lvp','bcs','bonk','cfxr','dwa','fff','gio','gio','gro','jo','jo-7z','ksm','ktp','minincsf','mt9','musa','muz','mwand','mws','nap','orc','pmpl','r','sdii','seg','snsf','sth','sti','stw','sw','swav','syn','tfmx','tm2','tm8','ulw','val','voi' -GenerateIncidentReport security
New-TransportRule "Notify Security if outbound email contains video (1)" -AttachmentExtensionMatchesWords 'aep','dzp','viv','vro','mp4.infovid','scm','dir','rms','wlmp','dzm','mswmm','amc','psh','3gp','veg','sfd','trp','wpl','m2p','ntp','aaf','bdmv','d3v','dck','gcs','ivr','m21','mk3d','mproj','msdvd','rdb','rmp','rv','screenflow','sec','swt','trec','usm','vcpf','viewlet','xej','dnc','ivf','playlist','spl','wm','bik','swf','webm','dcr','mani','prproj','wp3','mkv','avi','fbr','gfp','srt','piv','3gp2','bu','mpeg','wmv','scc','meta','gvi','vob','m4v','aepx','dzt','ts','ism','swi','amx','m2ts','rec','rmd','vpj','g64','mmv','ifo','wve','cpi','vp6','mov','vsp','mp4','mpg','hdmov','fcp','ogm','sbk','vc1','vgz','wmx','xesc','zm3','bnp','k3g','lvix','vp3','bin','mob','dmx','kmv','flv','par','vid','rmvb','dcr','tp','xvid','mnv','str','asf','bdm','camproj','mxf','yuv','0.89','avchd','dat','m1pg','mvd','roq','tsp','wmmp','ddat','f4f','imovielibrary','lsx','proqc','qt','sbt','video','yog','f4v','mts','3gpp','3mm','r3d','dav','smv','ogv','nvc','h264','3g2','dvdmedia','fcproject','ismv','sqz','tix','clpi','f4p','fli','hdv','m2t','mvp','nsv','rsx','smk','thp','ttxt','inp','mvc','m15','0.264','lrv','mvp','wmd','camrec','dxr','divx','stx','aetx','vep','dv4','db2','mpeg4','pds','mod','aec','ajp','dv','sfera','dvr','pmf','ced','dash','rm','ale','avp','bsf','dmsm','dream','imovieproj','otrkey','3p2','arcut','avb','avv','bdt3','bmc','cine','cip','cmmtpl','cmrec','cst','d2v','dce','dmsd','dmss','dpa','evo','eyetv','fbz','flc','flh','fpdx','ftc','gts','hkm','imoviemobile','imovieproject','ircp','ismc','izz','izzy','jss','jts','jtv','kdenlive','m21','m2v','mj2','mp21','mpgindex','mpls','mpv','mse','mtv','mve','mxv','ncor','nuv','ogx','pac','photoshow','plproj','ppj','prel','prtl','pxv','qtl','qtz','rcd','rum','rvid','rvl','sdv','sedprj','seq','sfvidcap','siv','smi','svi','tda3mt','tivo','tp0','tpd','tpr','tvlayer','tvs','tvshow','usf','vbc','vcv','vdo','vdr','vfz','vlab','vtt','wcp','wvx','wxp','xfl','xlmv','y4m','zm1','zm2','exo','lrec','mp4v','mys','vcr','w32','am','aqt','cvc','gom','mpeg1','mpv2','orv','rmv','ssm','zeg','arf','moi','zmv','wtv','mjp','gifv','mpe','dpg','mpl','rcproject','amv','tod','60d','moff','mp2v','tdt','dvr-ms','bmk','asx','edl','smil','snagproj','cmmp','dv-avi','eye','mgv','mp21','pgi','pro','stl','xml','avs','box','int','irf','scn','sml','ismclip','avs','evo','smi','awlive','m4e','mpg2','tdx','vivo','movie','vf','3gpp2','psb','axm','cmproj','dmsd3d','dvx','ezt','ffm','mqv','mvy','vp7','xel','aet','anx','avc','avd','axv','bdt2','bs4','bvr','byu','camv','cmv','cx3','dlx','dmb','dmsm3d','fbr','fcarch','ffd','flx','gvp','iva','jmv','ktn','m1v','m2a','m4u','mjpg','mpsub','mvex','osp','pns','pro4dvd','pro5dvd','pssd','pva','qtch' -GenerateIncidentReport security
New-TransportRule "Notify Security if outbound email contains video (2)" -AttachmentExtensionMatchesWords 'qtindex','qtm','rp','rts','theater','tid','tvrecording','vem','vfw','vix','vs4','vse','wot','xmv','mvb','nut','pjs','sec','0.787','ssf','mpl','clk','dif','vft','vmlt','anim','grasp','moov','pvr','vmlf','modd','bix','cel','dsy','gl','ivs','lsf','m75','mpf','msh','pmv','rmd','rts','scm','vdx' -GenerateIncidentReport security

The file extension lists are taken from fileinfo.com (audio and video).

It should also be noted that these rules are fairly simple – they are only looking at the file extension name and not the actual contents of the message.

Creating an Office 365 profanity filter (works for Exchange too)

As part of recreating the rules that my customer currently has set up with a popular cloud-based message hygiene platform, I needed to create an Office 365 profanity filter for Exchange Online. Believe it or not, there isn’t one built into the product (it disappeared with BPOS) but you can do some interesting things with DLP classification rules and policies.

I’d like to publish the exact steps here but I can’t, for commercial reasons. What I can do though is signpost some useful resources:

Once you’ve created a policy you can apply it in PowerShell with:

New-ClassificationRuleCollection –FileData ([Byte[]]$(Get-Content -path ProfanityPolicy.xml -Encoding byte -ReadCount 0))

If you need to update it then the cmdlet is Set-ClassificationRuleCollection and if you want to take it out again, Remove-ClassificationRuleCollection will do the trick.

With the classification in place, you can create rules that use the policy. In my case, one to block emails containing sensitive content (i.e. a list of pre-defined words) and send an incident report to a defined mailbox.

Even though I was working with Exchange Online (v15), the same process will work for Exchange Server 2013 and, presumably 2016 when it comes…

Finally, one gotcha I found (well, it was a user error really):

  • I thought my rule wasn’t working. When I later logged into the shared mailbox that blocked messages were copied to, I found copies of the messages I’d been sending for quite a while. My confusion was because I’d been testing with Policy Tips (which seemed a bit hit and miss in OWA) and that doesn’t actually block the message (doh!). As soon as I enforced the rule, my rude messages started bouncing back as expected…

NDR from message blocked by Office 365 profanity filter

Short takes: Excel tips to display the worksheet name and validate data; editing Microsoft Project files stored on SharePoint; and an XPS to PDF conversion service

Another collection of mini-posts based on recent IT trials and tribulations…

Excel tips to display the worksheet name in a cell and to validate data

Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet.  I’m quite pleased with the result but I did need to work a little on some of the tricks.

Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.

The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

Incidentally, I also needed to look something up that I’ve blogged about previously: if a cell shows the formula rather than the result, check the formatting is General and not Text.

Editing Microsoft Project files stored on SharePoint

Much as I try, it seems I can’t avoid working with Microsoft Project. Unfortunately, when working directly from SharePoint the files are opened read-only. The answer, it seems, is to work on a synchronised local copy – as described by Victor Butuza on his Microsoft Office SharePoint blog.

XPS to PDF conversion web service

Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files.  There’s an API for those who want to make the conversion programmatically too.