Update to Batch Converting Visio Files

After some trail and error from my previous post we went through batch converting 300+ Visio vsd files over to vsdx. Overall the files size was reduced by 70%, dropping from over 6 Gb to around 2Gb, and allowing the files to open/save to the network a lot quicker. The only caveat I found was Visio 64-bit is the best way to do this and is most stable, especially with files over 25Mb. Above about 33 – 35Mb and the 32-bit version would randomly crash. With that said I added in some basic error detection to skip corrupt Visio files, some user variables to keep personal info or remove along with deleting the original file or not, and the ability to do sub directories. Here is the modified code:

Public FilesAttempted As Integer
Public FilesConverted As Integer
Public FilesDeleted As Integer
Public FilesSkipped As String

Sub ConvertToVsdx()
FilesAttempted = 0
FilesConverted = 0
FilesDeleted = 0
FilesSkipped = ""
Dim FileSystem As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Dim HostFolder As String
Dim DeleteOriginal As Boolean
Dim RemovePersonal As Boolean

''' HostFolder is directory to start it. Change to your base directory.
HostFolder = "T:\"
''' DeleteOriginal will delete the original file as long as the VSDX was created. Either True or False
DeleteOriginal = True
''' RemovePersonal will remove personal information from the file. Reduces the size a little but you might want to keep the original info
RemovePersonal = False

DoFolder FileSystem.GetFolder(HostFolder), DeleteOriginal
MsgBox "Conversion complete! " & vbCrLf & vbCrLf & "Files attempted: " & FilesAttempted & vbCrLf & "Files converted: " & FilesConverted & vbCrLf & "Files deleted: " & _
FilesDeleted & vbCrLf & "Files with issues: " & vbCrLf & FilesSkipped, vbOKOnly + vbInformation, "Conversion Complete"
End Sub

Sub DoFolder(Folder, DeleteOriginal)
On Error GoTo ErrHandler:
Dim SubFolder
For Each SubFolder In Folder.SubFolders
DoFolder SubFolder, DeleteOriginal
Next
Dim File
For Each File In Folder.Files
' For each file name sure its a vsd and not a temp file
If ((Right(File, 3) = "vsd") And (Right(File, 4) <> "~vsd")) Then
FilesAttempted = FilesAttempted + 1
' Open the file
Application.Documents.Open File
' Remote personal info if set
If RemovePersonal = True Then
Application.ActiveDocument.RemovePersonalInformation = True
End If
  ' Loop through each master then check across pages to see if it is used
  Index = Application.ActiveDocument.Masters.Count
  While Index > 0
  bMasterUsed = False
  Set oMaster = Application.ActiveDocument.Masters.Item(Index)
  For Each oPage In Application.ActiveDocument.Pages
  For Each oShape In oPage.Shapes
  If oMaster.Name = oShape.Name Then
  bMasterUsed = True
  End If
  Next
  Next
  ' if Not used delete it from the document stencil
  If bMasterUsed = False Then
  oMaster.Delete
  End If
  Index = Index - 1
  Wend

  ' Save as a vsdx and increase our counter
  Application.ActiveDocument.SaveAs File & "x"
  Application.ActiveDocument.Close
  FilesConverted = FilesConverted + 1

  ' Delete the original if set and the new vsdx exists
  If ((DeleteOriginal = "True") And (FileExists(File & "x"))) Then
  SetAttr File, vbNormal
  Kill File
  FilesDeleted = FilesDeleted + 1
  End If
NextFile:
End If
Next
Done:
Exit Sub

ErrHandler:
Debug.Print "Error encountered. Error number: " & Err.Number & " - Error description: " & Err.Description
If File <> "" Then
FilesSkipped = FilesSkipped & File & vbCrLf
GoTo NextFile:
End If
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
FileExists = (Dir(FileToTest) <> "")
End Function

If you use this please let me know how it goes or any tweaks that need to be made.

Check Uptime of Domain Computers

Recently we pushed some updates through GPO which ran at a users login to the domain. Weeks went by and I kept getting calls about people with old software that didn’t update. After some quick investigating these users were simply not rebooting or shutting down their computers and some were going on two months. On one hand that’s pretty good for Windows 10 machines but on the other they were missing important updates. After looking around I found that PsInfo.exe, part of the PSTools suite, would let me poll a computer for uptime but I wanted to poll all the computers and see how widespread this problem was.

First I started with a list of all computers taken from Active Directory using this PowerShell command to export them to a text file. Technically this command exports to a csv but I’m only taking one column so I skipped a step:

Get-ADComputer -Filter * -Properties Name | Select-Object Name | Export-CSV "C:\temp\ComputerNames.txt" -NoTypeInformation

Opening the file you should have a header of Name with all your workstations. I deleted the header and did a global find and replace to remove the quotes so I had a file with just the workstation names. Next I made a batch file with this single line:

For /f "tokens=*" %%i in (ComputerNames.txt) do psinfo uptime -nobanner \\%%i >> uptime.txt

I placed the batch file (CheckUptime.bat for me) in the same directory as PsInfo.exe and my ComputerNames.txt file. Run the batch file and it will step through each computer name in the file and check the uptime giving you something like this:

System information for \WSComputer2:
Uptime: 0 days 5 hours 24 minutes 57 seconds
System information for \WSComputer6:
Uptime: 2 days 17 hours 45 minutes 18 seconds
System information for \WSComputer23:
Uptime: 0 days 0 hours 42 minutes 41 seconds

I’m sure there is also a way to scrap the file and clean this up but it works for my needs.

Adding a SSL Certificate to a WorkPlace N4 Web Supervisor

So as a follow up to my post Adding a SSL Certificate to a Vykon AX Web Supervisor the way WorkPlace N4 web supervisors imports a certificate is slightly different. Mainly the key needs to be in a unencrypted RSA format which was tripping me up. So if you need to do this first read that prior post and then the only real change is for exporting out your key use this command:

openssl pkcs12 -in CertExport.pfx -nocerts -nodes -passin pass:YourPassword | openssl rsa -out key.pem

You will find the key will be exported and have a “RSA” tag in the headers now. Follow the same instructions from the other post to make your pem file (key, cert, intermediate, root) and it should import into a N4 supervisor just fine.

Batch Converting Visio VSD files to VSDX

I use Visio for my network and building layout drawings for simplicity. We have recently upgraded to Visio 2016 from 2007 and along with that there is a new file format, a .vsdx instead of a .vsd. Now I’m not sure of all the behind the scene changes but I do know that when you check a files properties you can reduce the file size by removing unused shapes, remove personal info, and save your old files as the new format. Between these three options the file size will drop 40 – 70% per file. Now when you have hundreds or even thousands of these you can gain a significant amount of file space back along with quicker overall loading and saving times. The problem is this looked to be a manual process.

So I did some searching and of course others have already tried to script this. I found two main articles, one on batch converting the vsd to vsdx and a second on removing the unused objects. I combined the two into a single script and added in the remove personal information option. So now you can do all three with a single script.

This is in VBA so to use this start out by opening a new blank Visio drawing. Hit ALT+F11 to get into Microsoft Visual Basic for Applications. Right click your drawing on the top left (by default Drawing1) and insert a module. Then copy and paste this in:

Sub ConvertToVsdx()
Dim strPath As String
Dim strFile As String
strPath = "C:\Temp2\"
strFile = Dir(strPath & "*.vsd")
Do While strFile <> ""
If Right(strFile, 3) = "vsd" Then
Application.Documents.Open strPath & strFile
Application.ActiveDocument.RemovePersonalInformation = True
' Loop through each master then check across pages to see if it is used
Index = Application.ActiveDocument.Masters.Count
While Index > 0
bMasterUsed = False
Set oMaster = Application.ActiveDocument.Masters.Item(Index)
For Each oPage In Application.ActiveDocument.Pages
For Each oShape In oPage.Shapes
If oMaster.Name = oShape.Name Then
bMasterUsed = True
End If
Next
Next
' if Not used delete it from the document stencil
If bMasterUsed = False Then
oMaster.Delete
End If
Index = Index - 1
Wend
Application.ActiveDocument.SaveAs strPath & strFile & "x"
Application.ActiveDocument.Close
End If
strFile = Dir
Loop
End Sub

Change the strPath to the directory you want to run this on and hit F5 to run. It will open each drawing with a vsd extension in that directory, set the remove personal information tag, remove the unused objects/stencils, then save it as a vsxd.

I haven’t adapted it to do subfolders yet as this is all I really needed but I’m sure there is a way to iterate through directories.

Compiling NodeMCU ESP32 Firmware

Recently I’ve been doing some experimenting with the NodeMCU ESP8266 and ESP32-S chips to use in home automation, sending data back to my Samsung SmartThings system. The ESP32 is considered a development board and as such not everything works as expected. One of the issues I was having was the ADC isn’t very linear resulting in some of my analog inputs being pretty far off (over 4%). With that said the firmware is being updated pretty regularly so I wanted to compile a new version. I attempted to follow the instructions available (https://nodemcu.readthedocs.io/en/dev-esp32/en/build/) but was having trouble. Finally I created a Ubuntu 16.04.3 LTS virtual machine using VirtualBox but then ran into a bunch of other issues since it didn’t have any of the dependencies installed. Well after some experimenting I got it working and here are the steps:

  1. Install Ubuntu 16 (should work with 17 also)
  2. Update it (search for update and launch the software updater)
  3. Drop to a terminal (CTRL+ALT+T), typing each item, and press enter:
    • sudo apt-get update
    • sudo apt-get upgrade
    • sudo apt-get install libncurses5-dev libncursesw5-dev flex bison gperf python-serial
    • sudo apt-get install git
  4. In the same terminal window grab the firmware code: “git clone --branch dev-esp32 --recurse-submodules https://github.com/nodemcu/nodemcu-firmware.git nodemcu-firmware-esp32
  5. Move to the source directory with “cd nodemcu-firmware-esp32”
  6. Start up the menu using “make menuconfig”

You should now be able to select your config options and save a sdkconfig file that you can use to make the firmware. Now I couldn’t save the sdkconfig in the same directory and ended up saving it to my home directory then making a copy of the modemcu-firmware-esp32 directory, pasting the config in there, and running make from there to compile. I’m assuming this is because I used git to pull down the repo and its read only (compiling from the copy with my sdkconfig worked so I didn’t try to figure it out).

If you want to get fancy you can also share out your USB through VirtualBox and flash the chip from the virtual machine using make flash but I didn’t like that idea so I transferred out the NodeMCU.bin from the build directory to my host machine (Windows 10) and used NodeMCU-PyFlasher-2.0 to flash to firmware. Afterward I found that my ADC’s, while not 100% accurate, were a lot closer. Hopefully they keep making progress on this chip as it seems like a very capable replacement to the ESP8266.

Installing Pydio 8 on IIS 8.5 and IIS 10

Pydio 8.0.0 was released earlier today and has a major UI change along with some minor back-end changes. I’ve gone through and did another re-write of the instructions for this new version. Among other things it now supports PHP 7.1.*. I’ve also streamlined the instructions slightly, upgrading all other pre-reqs to the latest versions, and use the modified version of PHP Manager that Ronald Carter has upgraded to no longer require Dot Net 2.0 so less stuff to install on the server. So it should now work without issues on both IIS 8.5 and IIS 10. Here are the new instructions:

Installing Pydio 8 on Windows Server 2012 R2

Installing Pydio 7 on IIS 8.5 – Pure 64 bit

With the recent release of Pydio 7.0 my old instructions are now outdated, especially when it comes to public file shares. There were a lot of changes in the new Pydio so I have re-written my instructions for it. The biggest change is there is no longer any reason to create your own web.config files, the stock one now works correctly on IIS. I’ve also added instructions on scripting things form the command line like indexing workspaces on a schedule which has helped me immensely since I keep some workspaces updated with DFS and have gotten away from using Samba. Here are the new instructions, let me know if you have trouble with them:

Installing Pydio 7 on Windows Server 2012 R2 – Pure 64 bit

Sophos XG Firewall PCI Compliance Woes

Recently I deployed a Sophos XG firewall to replace my very much aging Microsoft Forefront TMG 2010 firewalls. For the most part, after lots of back and forth with Sophos tech support, I got it working correctly for things like Outlook Anywhere, Web and Mobile Access, protecting internal websites, and general web filtering. Everything seemed fine until our monthly PCI compliance scans came along and we failed miserably. TLS 1.0 was enable, HTTP Track/Trace was enabled, and 64 bit cyphers were enabled for each external IP that we were hosting a site on. I contacted Sophos and long story short there is currently no way to fix these through the UI (v16)….all required manually editing the appache httpd file on the box. So here is how to do it.

First telnet into your XG, log in, then go to 5 Device Management then 3 Advance Shell. Type in the following:

# mount -no remount,rw /
# vi /usr/apache/conf/httpd.conf

You should now be able to edit the file. Press “I” once to enter insert mode. Find the SSLCipherSuite line and remove any sections (between the colons) that have 3DES in them. For example the original cypher line:

ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:RSA+AESGCM:RSA+AES:ECDH+3DES:DH+3DES:RSA+3DES:!aNULL:!MD5:!DSS

Modified cypher line:

ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:RSA+AESGCM:RSA+AES:!aNULL:!MD5:!DSS

Now find the SSLProtocol list, usually right below the cyphers and remove support for whichever protocol by adding it with a minus. Here is the original line:

SSLProtocol all -SSLv2 -SSLv3

Modified protocols line:

SSLProtocol all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1

Lastly for the tracking add a new line under these:

TraceEnable off

Hit ESC then :w and enter to write the file then :q and enter to quit. Then restart the services and mark things what I’m assuming is read only:

# service apache:restart -ds nosync
# service WAF:restart -ds nosync
# mount -no remount,ro /

Keep in mind any Outlook 2010 clients you have in the field might try to connect using TLS 1.0 and will fail with a cryptic proxy server error. You can follow this site to hopefully fix that: https://blogs.technet.microsoft.com/schrimsher/2016/07/08/enabling-tls-1-1-and-1-2-in-outlook-on-windows-7/

Getting Broadcom Wireless Working on Ubuntu 14 (Dell Inspiron 1501)

Recently we changed out a bunch of rarely used training PC’s, Dell Inspiron 1501’s, for newer models. The Dells were definitely past their prime but I’d hate to throw them out since they all looked good and had almost no wear. From previous memory ad hard drive upgrades I had a stack of DDR2 memory and some decent mechanical drives so I decided to upgrade all of them as much as possible and find a OS that would run on them.

The best I could muster was 1.5Gb of RAM each and some 160Gb 7200 RPM drives. After a quick search it looked like Ubuntu 14 was my best bet (16 has a 2Gb memory minimum). I created a Ubuntu 14 USB drive using Universal USB Installer (http://www.pendrivelinux.com/universal-usb-installer-easy-as-1-2-3/), hooked up the ethernet cord, and booted it. At the install screen I selected the option to download updates and also to isntall third party software. Little did I know that third party software checkbox was going to cause me lost of headaches.

Turns out some firmware for the Broadcom cards is installed by that option which renders the wired and wireless connection in these laptops dead. After lots and lots of searching I found someone that posted how to remove it. Unfortunately there steps didn’t work for me but eventually I found ones that did.

Before I fixed the wireless/wired issue I found that these machines freeze during a shutdown or reboot. Some searching for that issue led me to here http://askubuntu.com/questions/523638/why-does-ubuntu-freeze-during-reboot-14-04-lts and editing the grub config file (CTRL+ALT+T for terminal) with:

sudo gedit /etc/default/grub

And changing these two lines as listed:

GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash reboot=acpi”
GRUB_CMDLINE_LINUX=”acpi”

Once thats fixed run the following to remove the current Broadcom stuff which doesn’t seem to work on this model (or others around the same vintage):

sudo apt-get remove –purge bcmwl-kernel-source

Reboot and the wired connect should be working again. It might not reboot because the above grub changes aren’t yet in effect so let it sit for a minute on the shutdown screen then hard power it off and back on. Now to get the firmware for the wireless card and then reboot again:

sudo apt-get install firmware-b43-installer
sudo apt-get install linux-firmware-nonfree

Now both the wired and wireless should work. And I have decent working Ubuntu machines that we can give to someones kinds to mash on instead of throwing them in the garbage.

Disabling Dropbox from Installing or Running if Installed

Recently I was on a quest to disable the Dropbox program from running on company owned (domain joined) machines. There were lots of hacks to make it work but finally I found a solution, although it was worded relatively cryptically, on Experts Exchange by a McKnife (http://tinyurl.com/gr3f9ar). Long story short you can use Software Restriction Policies (https://technet.microsoft.com/en-us/library/bb457006.aspx) to do this but his solution was more elegant as it blocked Dropbox programs based on the certificate used to sign them as opposed to the file path or things that might change often. This not only blocks the Dropbox program if it’s already installed but also prevents a user from installing it in the first place. Here is my expanded version of his instructions.

First download the Dropbox installer. Right click it and select Properties then go to Digital Signatures. Select the first one (SHA1) and click “Details”. Click “View Certificate” then the Details tab then “Copy to File…”. This lets you export out the certificate. Click Next then “Base-64 encoded X.509 (.CER)” and next again. Save the certificate as something like “Dropbox SHA1 Cert.CER”. Once that one is exported repeat the procedure for the SHA256 certificate.

Once you have both certificates open up Group Policy Management and if you already have a software restrictions policy edit it. If not I suggest you create a new one. Navigate to Computer Configuration -> Policies -> Windows Settings -> Security Settings -> Software Restriction Policies -> Additional Rules. Right click and create a “New Certificate Rule”. Browse for the SHA cert and make sure the Security Level is set to Disallow. Give it a description such as “Dropbox SHA Certificate”. When you click OK, if you didn’t have any certificate rules before, it will prompt you to turn them on and display the “Enforcement Properties” page. At the bottom “Enforce certificate rules” then “OK”. Repeat for the SHA256 certificate.

Once GPO updates Dropbox will no longer start and executing the exe or installer directly will give you a nice error message:

dropboxerror

Side note: Once this policy is in place you will also not be able to uninstall Dropbox since the same certificate is being used on the uninstall. Keep that in mind…you would have to disable enforcing certificates temporarily to get it uninstalled.