Computer Stuff

Replace NULL Excel cell contents only up to the last row in a dataset

by on Jun.02, 2021, under Computer Stuff, Windows Info

Recently, I needed to write a macro to replace all NULL values in a spreadsheet with a “0.00”. This seems easy, but if you write a default search and replace in your macro, Excel will dutifully replace *every* *single* NULL in your columns, even where there is no adjacent data. This means that your spreadsheet will grow to the maximum number of rows, because you added zeros to ALL NULL cells in the column. To only replace NULL values where there is adjacent data, you need to find the “LastRow” in your spreadsheet.

If you have to replace values in more than one column, you’ll need to rename the variable declarations as below in this example: (Note the Area, Area1, Area2, LastRow, LastRow1, LastRow2, etc. below)

## MACRO SNIP ##

Columns("D:D").Select
  Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

Columns("E:E").Select
  Dim Area1 As Range, LastRow1 As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

Columns("F:F").Select
  Dim Area2 As Range, LastRow2 As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, _
  LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
  SpecialCells(xlCellTypeBlanks).Areas
  Area.Value = "0.00"
  Next

This way, you get zeros just until the last row, and no more! Replace the zeros in the value of Area.Value = “0.00” with whatever you want put into the NULL cells!

Leave a Comment :, more...

Upgrade from LSI Logic SAS adapter to VMWare Paravirtual on existing VM

by on May.25, 2021, under Computer Stuff, General Info, Linux, Windows Info

By default, when you add a hard drive to a VMWare virtual machine, the SCSI adapter default choice is LSI Logic SAS. For speed and optimization, the VMWare Paravirtual driver is the better choice. However, you need to have the driver installed in the guest OS before just flipping your controller to a new type. If you just change the controller type to VMWare Paravirtual, and the driver is not installed in the guest OS, your VM may not boot.

  1. Edit settings on the VM, and add a new SCSI controller to slot 1, selecting “VMWare Paravirtual” as the type.
  2. Add a 1gb disk to the VM, adding it to the new slot 1 controller you created in step 1.
  3. Check compmgmt.msc on the VM to ensure that you see the new SCSI controller driver under “Storage Controllers”
  4. Optionally, update the driver on the guest OS by searching online for an updated version. I found one from 2019.
  5. Shut the machine down, and edit settings again. Remove the 1gb disk you created earlier.
  6. Click on the old SCSI controller in slot 0 and expand it. Change the type to “VMware Paravirtual”
  7. Remove the additional SCSI controller added in step 1.
  8. Click OK, and boot the server again.
  9. Once the server is booted, it should report that it found and installed a new driver, (again) and will request a reboot.
  10. Reboot the server.

Leave a Comment :, , more...

Office365 Content Search for External Recipients

by on May.17, 2021, under Computer Stuff, General Info, Windows Info

Recently, I was asked to find all emails sent to and from a particular user, from an external email address. The mailbox exists in Office365, so I hopped into Compliance Center, and started a new Content Search. In trying to fill out the form for the recipient, or participant, I realized that I could not choose or enter an external email address. When I try to enter an external SMTP address, it says that there is “No User Chosen”, and you cannot continue with creating the search.

To get around this, you can enter your search parameters in the “Keywords” box, and ignore the other fields, like so:

KEYWORDS:

(date=2019-07-30..2020-09-17)(senderauthor=localuser@localdomain.com)(senderauthor=remoteuser@externaldomain.com)(participants=localuser@localdomain.com)(participants=remoteuser@externaldomain.com)(participants=remoteuser2@otherexternaldomain.com)

Leave a Comment more...

Find and delete a file on ALL drives using powershell

by on May.05, 2021, under Computer Stuff, Windows Info

In the example below, replace filename.txt with the file you’d like deleted. It will be removed from all drives.

get-psdrive -PSProvider filesystem | ForEach-Object { Get-Childitem -Path $_.Root -Filter filename.txt -recurse | Remove-Item -force}

Leave a Comment :, more...

I was today years old when I learned…

by on Oct.15, 2020, under Computer Stuff, Networking, Windows Info

That you can do this:

dnscmd /createbuiltindirectorypartitions

Creates a DNS application directory partition. When DNS is installed, an application directory partition for the service is created at the forest and domain levels. Use this command to create DNS application directory partitions that were deleted or never created. With no parameter, this command creates a built-in DNS directory partition for the domain.

Leave a Comment :, more...

Let’s Encrypt certificates with WordPress MultiSite

by on Jan.27, 2020, under Computer Stuff, Linux

Lately, everyone seems to be moving to WordPress. It’s a rock-solid platform for publishing, with a low cost of administration. To securely allow a remote login to your WordPress site, you MUST use an SSL certificate to encrypt the login communications.
I’ve found that LetsEncrypt.org really solves that problem, and with minimal setup required. The biggest catch that I found to using LetsEncrypt.org certificates with my domains was this:
You need a DNS host that supports CAA records.  SSLMate publishes a list of those hosts here.


So first, you MAY need to move your authoritative DNS zone to a host that supports CAA records. I’ll wait.

After that, you need a CAA record for the root of your domain name.
It should look something like this:
@ CAA 6h 0 issue “letsencrypt.org”

Once your record is in place, you can validate it using DNSSpy here

Now that your DNS record is answering correctly, it’s time to address the WordPress installation. WordPress can be installed in any number of ways. I usually use Apache2, MySQL, PHP, etc.. A common Linux LAMP stack.

Traditionally, when installing multiple WordPress sites on the same web server, I’d create a separate virtual host and directory for each site. Things have changed, though, and now, I can use WordPress Multisite. This allows(requires that) all of your sites use the same virtual directory. (In most cases, that’s the main web root of your server. On most Linux OSs, that’s /var/www/html )

For a basic WordPress installation, see this link.
For a deep-dive into the WordPress Network creation and enabling Multisite, see this link.

Since Apache2 comes with a default virtual directory and a default virtual host definition that covers the root of the web server, you don’t *need* to add a virtual host directive for each site you add using WordPress Multisite, because WordPress Multisite handles requests for each site. However, if you want to take advantage of a free, auto-renewing 3-month SSL certificate from Let’s Encrypt, you need an Apache virtualhost entry for each site in WordPress Multisite.
So, we’ve just installed WordPress Multisite, and we’ve created our first site of testdomain.com.
In Apache2 on Ubuntu 18, you need to edit 2 files. They are:

/etc/apache2/sites-available/000-default.conf
/etc/apache2/sites-available/000-default-le-ssl.conf

##/etc/apache2/sites-available/000-default.conf##
(this is the default, leave it alone)
<VirtualHost *:80>
ServerAdmin webmaster@localhost
DocumentRoot /var/www/html <- same root for all sites
ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

(you’re going to add this for each new site, changing the site name)
<VirtualHost *:80>
ServerAdmin webmaster@localhost
ServerName testdomain.com
ServerAlias www.testdomain.com
DocumentRoot /var/www/html <- same root for all sites
<Directory />
Options FollowSymLinks
AllowOverride All
</Directory>
<Directory /var/www/html>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>
RewriteEngine on
RewriteCond %{SERVER_NAME} =testdomain.com [OR]
RewriteCond %{SERVER_NAME} =www.testdomain.com
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]
</VirtualHost>

##/etc/apache2/sites-available/000-default-le-ssl.conf##
<VirtualHost *:443>
ServerAdmin webmaster@localhost
ServerName testdomain.com
DocumentRoot /var/www/html
<Directory /var/www/html>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>
</VirtualHost>

After you install and run certbot, you’ll see these lines added to the end of each virtual host in the SSL config file above:
Include /etc/letsencrypt/options-ssl-apache.conf
SSLCertificateFile /etc/letsencrypt/live/testdomain.com/fullchain.pem
SSLCertificateKeyFile /etc/letsencrypt/live/testdomain.com/privkey.pem

1 Comment more...

Building a linux terminal server with vncserver

by on Jun.09, 2017, under Computer Stuff, Linux

Sometimes you need to allow multiple people to login to a linux server, and run something that requires Xorg/X11/Xwhatever they’re calling it these days. (xenocara if you’re cool ;)
So, from the start. Install your OS, (I’m using Ubuntu) install gnome, xfce4, openssh-server and vncserver, and add your users.

  • Setting up your user accounts for VNCserver

Log in as your first user, and run the command: vncserver in a terminal. This will start an instance of the vncserver, and allocate an available port to you. It should look something like this:

username@laptop:/home/username$ vncserver
You will require a password to access your desktops.

Password: (enter password)

Verify: (verify password)

xauth: file /home/username/.Xauthority does not exist

New 'laptop:1 (username)' desktop is laptop:1

Creating default startup script /home/username/.vnc/xstartup

Starting applications specified in /home/username/.vnc/xstartup

Log file is /home/username/.vnc/laptop:1.log

Immediately after this, you’ll want to kill the server with:

username@laptop:/home/username$ vncserver -kill :1

You’ll want to do that for each user account, so later, we can use their passwd file to start the vncserver as a system service, so it will run the vncserver every time the server boots up. The next user you add and run vncserver for should get port :2 (or, 5902)

We can see that this was the first instance of vncserver to run on this server, because we were assigned port :1 (technically, port 5901)
This will also create the .vnc folder in your home dir. In my case, this is ‘/home/username/.vnc’.  Inside this hidden folder, you’ll find the passwd file you created when setting your password for vncserver, and the xstartup file that is created by default.

It doesn’t tend to be extremely useful out of the box, so we’ll replace the contents of it with this for a Gnome desktop in your vnc session:

#!/bin/sh
[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
x-window-manager &
gnome-session &
gnome-panel &
gnome-settings-daemon &
metacity &
nautilus &

Replace it with this for an xfce4 desktop in your vnc session:

#!/bin/sh
unset SESSION_MANAGER

unset DBUS_SESSION_BUS_ADDRESS

startxfce4 &

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup

[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources

xsetroot -solid grey &

One you’ve got your xstartup configured the way you want it, run vncserver once more, and make sure that you get the desktop you expect when connecting.

Immediately after this, you’ll want to kill the server with:

username@laptop:/home/username$ vncserver -kill :1

  • Configure vncserver as a system service for each user

As of right now, each user would need to first ssh to the server, and start their instance of vncserver manually to take over an X desktop. This is not what we want. We want their individual vncserver instances to run every time the server boots, and this way, we can restart the vncserver instance for each individual user, rather than having to kill the entire server to reset one connection.

For that, we need to install some init scripts in /etc/init.d/ You need to be root to do this, or able to use sudo.

(at this point I wonder if I should outline how to edit files.. I feel like if you’ve read this far, you already know, or are googling it as I speak.. )

username@laptop:/home/username# vi /etc/init.d/vncserver-username

Insert the following into the newly created file:

#!/bin/sh -e
### BEGIN INIT INFO
# Provides: vncserver:1
# Required-Start: networking
# Required-Stop:
# Default-Start: S
# Default-Stop: 0 6
### END INIT INFO

# The Username:Group that will run VNCserver
export USER=”username”
#${RUNAS}

# The display that VNC will use
DISPLAY=”1″

# Color depth (between 8 and 32)
DEPTH=”16″

# The name that the VNC Desktop will have.
NAME=”username on Laptop”

. /lib/lsb/init-functions

case “$1” in
start)
log_action_begin_msg “Starting vncserver for user ‘$USER’ on localhost:$DISPLAY”
su username -c “/usr/bin/vncserver :1 -geometry 1920×1080 -geometry 1280×1024 -geometry 1024×768 -f ~/.vnc/passwd”
;;

stop)
log_action_begin_msg “Stoping vncserver for user ‘$USER’ on localhost:$DISPLAY”
su username -c “/usr/bin/vncserver -kill :1”
;;

restart)
$0 stop
$0 start
;;
esac


exit 0

IMPORTANT: To add another service for another user, you’ll need to copy /etc/init.d/vncserver-username to /etc/init.d/vncserver-newuser, and then edit the file for the new username and port info. Especially important is the “Provides: vncserver:1” line, as this is the name that the system uses to identify the service, and it must be unique for each instance of the service that you want to run. Name each user’s service for the port that they were assigned when they first ran vncserver. So, assuming that newuser got port :2, you’d replace ALL of the “username” with “newuser” and all of the “:1” with “:2”. Read carefully. ;)

Save and exit this file, and then make it executable:
chmod +x /etc/init.d/vncserver-username

Anytime you make a change to the init scripts, you have to tell the system:
systemctl daemon-reload

Now, update the runlevels:
update-rc.d vncserver-username defaults 99

Last, start your system service:
/etc/init.d/vncserver-username start

Comments Off on Building a linux terminal server with vncserver more...

Recreating the Exchange 2013 Receive connectors

by on Feb.10, 2016, under Computer Stuff, Windows Info

Just in case you ever have to recreate the default receive connectors in Exchange 2013, here you go:

Default Client Front End Transport (FrontEnd Transport)
TLS, Basic, Integrated, Exchange users, port 587
Default Client Proxy (Hub Transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange Server Auth, Exchange Servers, Exchange users, port 465
Default Front End Transport (FrontEnd Transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange server auth, Exchange Servers, Legacy Exchange Servers, Anonymous Users, port 25
Default Hub Transport (hub transport)
TLS, Basic, Offer Basic after TLS, Integrated, Exchange Server Auth, Exchange Servers, Legacy, Exchange Users, port 2525
Default Outbound Proxy Frontend Transport (Frontend transport)
TLS, Enable domain security, Basic, Offer basic after TLS, integrated, Exchange server auth, exchange servers, anonymous, port 717

Comments Off on Recreating the Exchange 2013 Receive connectors more...

It’s finally here!! Defer Windows Updates using Group Policy!!

by on Nov.16, 2015, under Computer Stuff, Windows Info

Comments Off on It’s finally here!! Defer Windows Updates using Group Policy!! more...

Great post on User Certificate Autoenrollment

by on Oct.26, 2015, under Computer Stuff, Windows Info

If you’re setting up PKI, or 802.1x, go read this-

http://www.vkernel.ro/blog/set-up-automatic-certificate-enrollment-autoenroll

 

Comments Off on Great post on User Certificate Autoenrollment more...


Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

CryptedNets is proudly powered by

Entries (RSS) and Comments (RSS)
Register - Login

Visit our friends!

A few highly recommended friends...