System Admin Tips

Wednesday, June 15, 2016

Excel 2016 has a built in function IsFormula to return if the cell contains formula reference or values. As this function is not available in Excel 2010 or earlier editions, one workaround is to create a custom or user defined function in VBA.

In case you do not want to enable Macro or want to avoid VBA, the alternative is to use XLM (Excel 4 Macro) which was the original Excel Macro language before VBA was introduced in Excel 5.0.

In the Name Manager under Formulas ribbon, create a new Name - let's call it IsFormula which should refer to =GET.CELL(48,INDIRECT("rc",FALSE))

Create a new conditional formatting rule with the formula "=IsFormula" with a custom format and apply that on the range where you want to check for cells containing formula.

Thursday, November 05, 2015

When you create a custom Pivot Table Style, it remains available only for the file it was created in. If you try to apply the same style to a different file you'll realize that the custom style doesn't appear in the list of available styles for new file.

In order to transfer the custom style or to save it for other Excel files, follow these steps:

Monday, October 26, 2015

Recently, one of the Excel 2010 files which I have been using for the past few months to make regular updates in, suddenly ran into issue and started to show error - Records removed from /xl/calcChain.xml.

To resolve this issue, follow these instructions:

1. Change the Excel file's extension from .xlsx to .zip

2. Unzip the contents and go to /xl/ folder.

3. Delete caclChain.xml

4. Re-zip the contents of the folder

5. Change the extension from .zip back to .xlsx.

Upon opening the file, if Excel will not find calcChain.xml, it will recalculate the formulas and will re-create the calcChain.xml file.

Friday, December 26, 2014

We know that the Excel function EOMONTH(start_date, months) can be used for determining the last date of the month that is the number of months before or after the start_date.But what if we want to find the last date of the current/given month. In this case, the months parameter should be 0. This will return (serial number) the last date of the same month as given in start_date.

When working on an Excel sheet which has Conditional Formatting rules, creating additional rows/columns/cells automatically create duplicates entries of Conditional Formatting. Below is the screenshot of first instance of Conditional Formatting entries

In order to avoid getting duplicate rules created, the workaround is to Insert a new row/column/cell and then copy over only the formulas to the new row/column/cell - this will retain originally created Conditional Formatting rules and will not unecessarity duplicate those rules.

Friday, January 15, 2010

Ever wondered how some applications, for example Winword, Excel, Outlook, start automatically when called from 'Run' window without providing their complete path even though the system PATH variable doesn't have these applications' locations?
The answer is in this registry key:
HKLM\Software\Microsoft\Windows\CurrentVersion\App Paths\

So the next time any of the applications do not start without the complete path but you are sure that it exists on the system, check that the registry should have the right information.

Thursday, September 04, 2008

I Came across a very strange error message in Excel 2003 - "Your entry cannot
be used. An integer or decimal number may be required" This message appeared
whenever I tried changing font size in a cell/sheet or when tried moving from one tab
to any other tab in Options window.

Eventually, it turned out that this issue was the result of missing Decimal
symbol in Regional Settings. As soon the decimal symbol was restored, this
issue was resolved.

Thursday, July 12, 2007

Feedburner now offers feeds integration for Blogger.com blogs. Gone are the days when one had to fiddle with the blogger template to replace blogger's default feed source with feedburner's feed link! Just go to the template page now and enter you feedburner feed source. Mind you, this configuration will not replace your default blooger feed source; it'll only redirect that link to feedburner's feed link. Also note that, apparently this configuration redirects only Atom feeds, and not RSS feeds.

This is just the begining of the services users will start to get with feedburner's acquisition by Google. Like, few days ago, feedburner announced two of its previously PRO (paid) services as freely available now - FeedBurner Stats PRO & MyBrand.

Sysinternals released a new tool for Active Diretory administrators called Active Directory Explorer v1.0

Active Directory Explorer (AD Explorer) is an advanced Active Directory (AD) viewer and editor. You can use AD Explorer to easily navigate an AD database, define favorite locations, view object properties and attributes without having to open dialog boxes, edit permissions, view an object's schema, and execute sophisticated searches that you can save and re-execute.

AD Explorer also includes the ability to save snapshots of an AD database for off-line viewing and comparisons. When you load a saved snapshot, you can navigate and explorer it as you would a live database. If you have two snapshots of an AD database you can use AD Explorer's comparison functionality to see what objects, attributes and security permissions changed between them.

Wednesday, April 25, 2007

When querying for disk space sizes using WMI, it
returns the numbers in bytes. These large numbers in bytes do not make much
sense until they are converted into Kilobytes (KB), Megabyte (MB), Gigabyte
(GB), or Terabyte (TB) and so on.Quite
frequently I need to convert these sizes in bytes to KB/MB/GB/TB for better
interpretation. I therefore created a quick VBScript function which I call
inside VBScript code whenever I need to convert numbers in bytes to
KB/MB/GB/TB.

Saturday, April 14, 2007

I have been seeing couple of queries in few tech
forums for uninstalling Remote Desktop Connection (RDP) 6.0, or how to revert to
original version of XP SP2 RDP client (mstsc.exe version 5.1.2600.2180).

RDP 6.0 was released as a patch (KB925876),
therefore, by default it doesn't appears as a seperate entry in Add/Remove list.
To uninstall it, you'll have to click on the check box of "Show updates" in
Add/Remove window, select "Update for Windows XP (KB925876)", and remove
it.

Alternatively, you can also browse to the folder
C:\WINDOWS\$NtUninstallKB925876$\spuninst\ and run spuninst.exe
from there, which will uninstall RDP 6.0 client and revert to the older
version.

Friday, April 13, 2007

TweakUI
has a nice handy feature of creating Internet Explorer search shortcuts. So,
instead of opening www.google.com first and then typing the search string,
TweakUI lets you assign a search prefix (e.g. “g”) to Google’s search URL. You
can then type “g searchstring” directly in IE’s address bar to search for
“searchstring” using Google search. For example, Google’s search URL for the
search string “ipod” is http://www.google.com/search?hl=en&q=ipod. To create
a shortcut for this search URL, copy-paste it in TweakUI window and replace the
search string “ipod” with “%s” (http://www.google.com/search?hl=en&q=%s).

You can create search shortcuts not only for Search
engines, but for any site which offers search functionality by observing their
search URLs in the address bar. Below are some of the search shortcuts that I
use for some common sites.

Shortcut

Search URL

What for?

a

http://www.answers.com/topic/%s

Answers.com

c

http://support.citrix.com/article/%s

Citrix Support Articles

d

http://www.dnsstuff.com/tools/whois.ch?ip=%s

WHOIS lookup on
dnsstuff.com

e

http://eventid.net/display.asp?eventid=%s

Lookup info on Windows Event
IDs

f

http://filext.com/detaillist.php?extdetail=%s

Lookup details of any file
extension

g

http://www.google.com/search?hl=en&q=%s

Google search

kb

http://support.microsoft.com/default.aspx?scid=kb;en-us;%s

Microsoft KB support
articles

p

http://www.processlibrary.com/directory?files=%s

Lookup executable/DLLs
details

r

http://dictionary.reference.com/search?q=%s

Word meaning references

t

http://www.rottentomatoes.com/search/full_search.php?search=%s

Rottentomatoes.com

w

http://en.wikipedia.org/wiki/Special:Search?search=%s

Wikipedia
search

After you
have created these shortcuts, you can export the relevant registry key as a
backup or to import that to any other computer. These shortcuts are located at
HKEY_CURRENT_USER\Software\Microsoft\Internet
Explorer\SearchURL. Export this key into a .REG file and import it on any
other computer. These shortcuts work with IE7 as well.

Here is the .REG file with above shortcuts. If you
are geeky enough, you can add/modify entries in your registry even without
TweakUI!

Thursday, April 12, 2007

Sometimes Citrix application when launched via Program Neighborhood Agaent (PNAgent) shows the error message - Failed to Launch. The Server returned CharlotteAppHostUnreachable. The issue apparently is related to Zone Data Collector (ZDC), though I haven’t been able to find the reason accurately.

Usually server restart resolves the issue. If not, then to avoid downtime for the application, a quick workaround is to create an .ICA file for the application and provide that to users. Directly accessing the remote application via .ICA file would work even if the application shows that error message when launched via PNAgent. This way you can continue to troubleshoot the issue without impacting application users!

Monday, April 09, 2007

Remote Desktop Connection client
6.0 introduces new authentication features to improve security for
Windows Vista and Windows Longhorn Server, which makes it mandatory for the user
to enter logon credentials before RDP client can establish connection to the
remote server (" Enter your credentials for <server>. These credentials
will be used when you connect to the remote computer" ). But if the remote
machine is configured to show logon warning message or if the remote system
happens to be Windows 2000 or XP, you’ll need to enter the credentials again at
remote machine’s logon screen.

There is however a workaround to skip the
credentials screen that RDP 6.0 client shows by choosing “Do not attempt
authentication” under Authentication options on the Advanced tab, but this
option is not set permanently. To permanently skip the additional credential
screen of RDP 6.0 client, edit the Default.RDP file in notepad to include
enablecredsspsupport:i:0 . The Default.RDP is located in each user’s My
Documents folder. Including enablecredsspsupport:i:0 disables the
Credentials Security Service Provider for the connection. If you use separate
.RDP files for different server, modify each of those .RDP files. Below is the content section of the default.rdp file with enablecredsspsupport:i:0 option included.

Note that this workaround is suggested only if you
connect Windows 2000/2003/XP systems because according to Terminal Services Team blog
post – “This option does disable the new
credential prompting behavior, but it also disables support for Network Level
Authentication for Vista (and Longhorn Server) RDP connections; Network Level
Authentication requires credentials to be provided by the client before a
session is created on the server side.” So if you do connect to
Vista/Longhorn over RDP, you’ll not be able to use this option.

Friday, April 06, 2007

When any software tries to access Outlook Address Book programmatically by
using Outlook libraries, the system shows the security warning message –

A program is trying to access e-mail addresses you have
stored in Outlook. Do you want to allow this?
If this is unexpected, it may
be virus and you should choose “No”.

According to KB329765 – “This behavior occurs because there is no
running session of Outlook to determine the correct security profile to load.
Therefore, the default security profile is used, causing the security prompt.
When you programmatically access an item in the Address Book, a session must be
running to determine the correct security profile to load. When Microsoft
Outlook is not running, the security dialog prompts the user because the default
security profile is used.”

Although offered as a security feature, this prompt can be very annoying if
the application frequently needs to access the address book or to send mails.

The workaround is to disable this security prompt by setting/creating a
REG_DWORD registry entry CheckAdminSettings = 1 located at
HKEY_CURRENT_USER\Software\Policies\Microsoft\Security

Looks for settings in the Outlook Security Settings folder,
applying them according to the defaults and specific users you've
specified.

2

For Outlook 2002 and Outlook 2003 only: Looks for settings in
the Oulook 10 Security Settings folder, ignoring any settings in the Outlook
Security Settings folder. Use this value when you want Outlook 2002/2003 to use
different settings

Anything else

Uses its default settings

This setting applies in current user hive only. Therefore, to apply it by
default to all users, make the same entry in Default User hive
(HKEY_USERS\.DEFAULT). This NTUSER.DAT file can then be copied to other systems
as well where the Outlook security warning needs to be disabled. Of course, it
goes without saying that this setting can also be exploited by viruses. Also
note that CheckAdminSettings registry change works only with Exchange Server.

Friday, March 30, 2007

For our terminal/citrix servers we have to regularly run delprof command for deleting inactive user profiles to minimize disk space consumption. The annoying part is to login into each of the servers just to run this command. Even though delprof supports deleting profiles on remote servers with the switch /C:\\<computername>,
but that runs terribly slow over WAN links.

A workaround is to remotely execute delprof (or for that matter any
other command) on the remote server by using tools like PsExec which installs a temporary service on the remote
machine to be able to execute process remotely, and unintalls the same service
after the process finishes. Because of its dependency on installing a service,
PsExec might not always be a viable option in production environment. So, what's
an alternative now?

Enter WMI.

It offers the capability to execute process remotely with the
limitation of not allowing any user interaction - the process will run in the
background without showing any interface on user's session, which is a perfect
feature for running silent installs or non-interactive processes, for example,
delprof /q/i

This script also uses Popup function of WScript.Shell instance to display message
box which disappears after specified seconds - great for showing quick status
messages without waiting for any user interaction.

Here is the complete script which executes delprof command on
the target server and shows before and after free space information at the end,
on the client side.

Assumptions:

Delprof executable should already be installed on the target server.

Because the script also checks before and after free space on C: drive, it
assumes that \Documents and Settings folder is located on server's C:
drive.

NOTE: For some weird reason, delprof command is case
sensitive. Therefore, DELPROF
/q/i does not recognize the "quiet" and "ignore" switches and still
prompts: Delete inactive profiles on
\\SERVERNAME? (Yes/No)

Wednesday, March 21, 2007

In Windows 2000 Terminal Server/Citrix environments, it often happens that after users have logged off, their user registry hive doesn't unload automatically from HKEY_USERS\, which in turn keeps consuming registry space and causes it to go out of sufficient free space. The workaround is to launch REGEDT32.EXE (on W2K) or REGEDIT (on W2K3) and manually unload user hives from HKU, but again you'll need to manually figure out who all are active users and exclude those from unloading. This manual process requires converting each of the logged-in user IDs into their respective SIDs and searching them in HKU to exclude.

This script addresses these issues and automates the process of unloading only non-active user hives from HKEY_USERS. This script first converts each of the loaded user SIDs into user names, then attempts to unload all but the active ones by matching those user names with the output from 'query user' command. Below are more details:

Expected Input:
Target computer name. By default it’ll show local computer name where it is executed from, which can be changed to any target system. This script can be run from the local desktop session, no need to login into remote system.

Definite Output:
The output file will open automatically in notepad after script execution, and will be stored in C:\TEMP\UnloadHive-SERVERNAME.log on the system where it is run. The output file shows a quick summary of how many hives unloaded successfully and registry space gained. This script also keeps updating a single log file (CSV) with summary result of each execution - in case any data analysis or trend analysis is required to do in future.

Dependency:
Requires psgetsid.exe to be present in the same directory where script is. It is recommended to copy psgetsid.exe in the executable path on the local machine (e.g., C:\Windows\).

Wednesday, August 30, 2006

This is definitely not good. During last one week two of my friends lost their
yahoo accounts, possibly got hacked. Amit was on SANS security training in
Singapore, with 65 other potential hackers security
professionals. After the last day in his training when he came back home he
realized that his Yahoo! Account password wasn’t working anymore. My other
colleague, Sudhakar, accessed his yahoo mail on his roommate’s laptop and next
thing he knew the following day was, his yahoo password wasn’t working
anymore.

I can’t stress enough how risky it is to access your email/e-banks/e-commerce
transactions over public networks or on some others’ machine. It is not a
matter of having complete trust on someone you know well enough, even if that
person happens to be your family member/close friend/colleague, because you
can never be sure if his own machine is secure enough. You cannot rule out the
possibility that his machine might already be hacked, and all it takes is just
one attempt for you to enter your secret credentials on that hacked machine
(or if he hasn’t intentionally been running malicious programs).

An end-user never bothers to secure his own machine apart from following
regular recommendations – things like, keep the anti-virus definitions
updated, install anti-spywares like Spybot & Windows Defender and keep the
definitions updated, regularly scan your system with anti-virus/anti-spywares,
blah-blah. But the important aspect we don’t usually realize is that there are
other channels to hack into system and keep it infected in a manner that
regular anti-virus/anti-spywares cannot detect. The biggest limitation with
these scanning tools is that these are all definition based and not
behavioral/pattern based. What this means is that unless the
loophole/vulnerability/threat becomes visible in public domain and a
patch/definition is released, these scanning tools will not be able to detect
them. For example, anyone with a decent programming knowledge can develop a
quick key-logger/virus/Trojan and release it within limited scope, may be
within among his contact circle. Behavioral based scanning tool, on the other
hand, keep monitoring the system at lower layers (of OS architecture) and are
better able to detect system modifications that key-logger tries to make to
activate itself.

The other aspect most people ignore is that they do not change the default
system configuration. For example, after a typical Windows installation, quite
a few system services get active which might not really be required for user,
but which can act as potential security holes. Network services like ‘client
for Microsoft networks’ and ‘file and print sharing’ are always active on all
the network interfaces – physical network interface as well as wireless
interface. Unless a good firewall is installed on the system, it is not very
difficult to hack into the system using just these two services and activate
some Trojan/key logger on that system. Rootkit is another new category of
tools which are even harder to detect with traditional scanning tools.

Here are some quick recommendations. This is not an exhaustive and polished
list, but just few quick ones on the top of my head. Of course, it goes
without saying that if you use same laptop at both office and home, you should
check with your system administrator before making these modifications.

Always ensure that your system is completely patched with up to date
hot-fixes. You can use
Microsoft’s
Baseline Security Analyzer to do the gap analysis and
install all the required patches.

Never trust any system other that your own (secure) system for entering your
credentials (email/banking/credit card/etc). Remember, all it takes is just
one attempt, even if that system belongs to your closest friend/family
member/colleague. I personally confess of having captured password details
of my friends, though I have never (mis)used those details!

Disable Remote Registry service. An example where this can be exploited is,
lot of instant messengers store user passwords in encrypted form inside
registry. All it takes is extracting the relevant registry keys remotely and
attacking it offline. Again, anyone who knows me, when I say to them that
they haven’t changed their passwords for a long time, I really mean it
(sometimes)! J

Even when using your own system over public wireless network, do not enter
confidential details. The risk with these public hotspots is that you can
never know that the person sitting next to you can possibly be running some
network capturing tool to sniff your data packets to crack it offline later.
There are tools available which can capture your network interface’s MAC
address and inject those same MAC address in their own machine’s network
packets to trick the wireless switch to send the returning packets to their
system. If you really have to use public wireless hotspots for entering
confidential details, do that only over VPN connection.

Disable ‘Client for Microsoft Networks’ & ‘File and Print Sharing’ on
wireless network interface unless you use open wireless access in your
office/home for logon authentication and/or sharing files/print attached to
your own system. At home if you connect your DSL directly to your system,
you should either install a good firewall or disable these two services on
the interface where DSL connects (physical LAN port or wireless), because
when DSL is directly connected to your system, it is your system which gets
the public IP address and gets exposed to Internet. Do yourself a big favor
and get a
switched-router instead
of connecting your system directly to public interface.

Avoid installing any third-party softwares without first testing it on some
dummy machine. Use
VMWare
Workstation (paid) or
Virtual
PC (free) for testing softwares in isolated environments.

Regularly run Autoruns and Process
Explorer on your system to monitor what all processes are
configured to autostart and currently running. If all the entires in these
tools scare you first, start getting yourself familiar with it.

Here are some quick directions on what you should have on your system: