About Matthew.Sharland

This author has not yet filled in any details.
So far Matthew.Sharland has created 6 blog entries.

How to simplify Excel formulas with “Cell Naming”…

2017-07-27T00:01:03+00:00October 31st, 2013|Uncategorized|

This week’s post is about a little-known-feature in Microsoft Excel called “Cell Naming.”  If you ever use Excel, like I do, then this tip is for you!  This is one of those features that I always knew existed but I never actually took the time to try, until now.

Simply put, “Cell Naming” allows you to assign a name to a cell, or a range of cells, for easy reference.  

You can get to the ” Name Manager” from the ‘Formulas’ tab then by clicking on the “Name Manager” button. 

For example:  

Click on the image to get a clear view…

 

In the above example, you see an Excel worksheet which is 3 columns wide x 14 rows tall that lists sale prices of 10 items at 3 different stores (“Store1″,”Store2″,”Store3″).  Each column has a “SubTotal” at the bottom that adds up the prices of that store’s 10 items sale prices, respectively.  Finally, there is a cell that adds up the 3 different stores “SubTotals” to equal the “GrandTotal.”

Next to the spreadsheet is the “Defined Names” which show you all of your cells/ranges with their associated names.

The four “Defined Names” that I created are:

Store1ItemTotals = $E$4:$E$13
Store2ItemTotals = $F$4:$F$13
Store3ItemTotals = $G$4:$G$13
GrandTotalSales = Store1ItemTotals+Store2ItemTotals+Store3ItemTotals

This was just a simple example, but imagine if you have 1000′s of cells with complex formulas that you need to manage!  And instead of having to manually cross-reference which cells that each formula is calling (i.e “E14″), you can know exactly which is being called by the descriptive “Cell Names” that you’ve pre-applied (i.e. “Store1ItemTotals”).  

I hope this tip will help you save a great amount of time and energy when working with Excel in the future!

Read More

NTFS Permissions – Copies, and Moves…

2013-07-24T22:37:13+00:00July 24th, 2013|Uncategorized|

Did you know …

Per Microsoft:  “By default, an object inherits permissions from its parent object, either at the time of creation or when it is copied or moved to its parent folder. The only exception to this rule occurs when you move an object to a different folder on the same volume. In this case, the original permissions are retained.“

Okay, so you knew that… but did you know this …?

Per Microsoft: “You can modify how Windows Explorer handles permissions when objects are moved in the same NTFS volume. However, if you want to modify this behavior so that the object inherits the permissions from the parent folder, modify the registry as follows: “

HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionExplorer

Value name: MoveSecurityAttributes
Data type: DWORD
Value data: 0

And maybe even you knew that….  but did you know it doesn’t always work …?

Actually, this registry value used to work natively with Windows XP (after a reboot).  But for Windows Vista, Windows 7, Windows Server 2008, and Windows Server 2008 R2, you must install a Microsoft-supplied HOTFIX to make it work, found here: (http://support.microsoft.com/kb/2617058).

Install the hotfix and reboot.

Also, make sure that the user account that is used to move the object has the Change Permissions permission set.

For the entire knowledge base article, please see: http://support.microsoft.com/kb/310316

 

 

Read More

A cool ‘Remote Desktop Connection Manager’ tip!

2017-07-27T00:01:04+00:00April 3rd, 2013|Uncategorized|

I am constantly monitoring multiple Microsoft Windows Servers and XP workstations via Remote Desktop Connection Manager and having to switch between each console one at a time is a very user-intensive and time-consuming process.  Though, I have recently discovered a very useful and time-saving trick that I will share with you below…

First, by default, the Remote Desktop Connection Manager gives you a thumbnail view of all of your workstations when you click on any “group” of servers from the left server pane list.  I always thought this was a “gimmick” and never thought twice about using it for anything because the dimensions of the thumbnail views of the remote desktops were just way too small to be useful.

But after reviewing the program’s options I found that you can modify the size of the thumbnails (Tools > Options > Client Area > Thumbnail Size)!

But that isn’t all!

The thumbnails are actual “live” clickable Remote Desktop sessions; so if you set the pixel size of the thumbnails to 25%, 30% or 40% of your screen size, you can fit 4-12 active server connections into one very productive window (obviously depending on your monitor size and screen resolution!).  This screen cap should give you a good idea of what I mean:

screenshot

 

I hope you find this tip as useful as I did!  Enjoy!

 

 

Read More

AD Attributes – LastLogon vs. LastLogonTimeStamp…

2017-07-27T00:01:04+00:00February 27th, 2013|Uncategorized|

A little while back I was working at an enterprise that has many locations across the United States.  I had a list of 30 usernames (from one specific out-of-state location) and a couple brand-new test accounts that I wanted to report on their “last logon times” from the Active Directory domain.  I put together a quick PowerShell script to loop through each user and report on the “lastLogon” time and I had (what I thought were) my results in no-time.  Here is a snippet of the code:

Get-ADUser $UserNameToSearch | Get-ADObject -Properties lastLogon

First, I opened up an RDP session to a domain workstation that I had access to at the out-of-state location that I was working with at the time and I went ahead and logged in with my test user.  I waited a few minutes for the replication to occur back to my location’s domain controller and then I ran the script from my local workstation.  Surprisingly, there were no results reported for that test account, but there were current up-to-date results for 95% of the users who were on my list that are said to work at that same location.

So I logged out, logged back in, waited, and ran the script again.  Still, no “logon time” results for my test user account.

Very interesting….

I did a few minutes of research on the “lastLogon” attribute and then I discovered I was searching the wrong attribute, per Microsoft’s MSDN Attribute Library which states: “This attribute is not replicated and is maintained separately on each domain controller in the domain. To get an accurate value for the user’s last logon in the domain, the Last-Logon attribute for the user must be retrieved from every domain controller in the domain. The largest value that is retrieved is the true last logon time for that user.“  But since the enterprise I was working with had more domain controllers than I could count on one hand so I chose to find a simple alternative.

After a few more minutes of research I then found the attribute that is replicated across all domain controllers, the “lastLogonTimeStamp” attribute.  I updated my script to:

Get-ADUser $UserNameToSearch | Get-ADObject -Properties lastLogonTimeStamp

I then had the results that I expected and I carried on with my day.

Hopefully this experience will save you time and effort!

 

 

 

Read More

PowerShell Basic Commands 101

2017-07-27T00:01:04+00:00January 16th, 2013|Uncategorized|

It looks like January has turned into “Powershell Month” here at the TekTopics.com! 

And while typically, we write these Powershell-related posts with the assumption that you’re already at least reasonably versed in the language, I thought it might be good to give a few introductory tips to those of you who need the first few basics of Powershell to get you up and running.  Then, you can take these tips and start getting creative with some of the great scripts, loops and commands we’ve talked about here in this blog.

First off, when in the PowerShell command prompt type in “Get-Command” and hit enter:

Get-Command

This will list every PowerShell command available. It is quite lengthy, but can be very helpful if you have the time to scroll through the entire list to discover new and exciting scripting tools.

If you wanted to save this list, you could by outputting it to a file by using the “|” (“pipe”) combined with the “Out-File” command, for example:

Get-Command | Out-File C:Commands.txt

If you are like me and require the list to be sorted by “practical application” then you can sort it by `Module Name’ by simply running:

Get-Command | Sort Module

You can then take it one step further to narrow your search results by filtering by Module name and a “*” (wildcard) as seen here an example of searching for BitLocker:

Get-Command -Module BitLock*

Or if you are looking for a specific command to run to `Enable’ BitLocker:

Get-Command –Module BitLock* -Name Enable*

From there, if you need usage commands or examples of any newly found Powershell command you can use the “Get-Help” command. The following example would give you helpful information for the “Get-Command” cmdlet as previously mentioned:

Get-Help Get-Command

Or for “Select-String”, the PowerShell equivalent to “FindStr” or the ever-so-popular string search tool called “Grep”:

Get-Help Select-String

One final note to tie this all together: Instead of memorizing the entire cmdlet, module or function names you can use the built-in aliases, which are shorter commands that execute the same command as the long name function, for example: gcm is Get-Command and help is Get-Help

So you could run the following command, which is the same as the above “Get-Help Get-Command”:

help gcm

And you can view the entire list of “Aliases” by running:

gcm -Type Alias

That is all for now! Stay tuned for PowerShell Basic Scripting 101!

 

 

Read More

How to script “Ownership” of NTFS File Systems…

2017-07-27T00:01:07+00:00July 25th, 2012|Uncategorized|

There’s a time in every IT professional’s life where he or she will need to “Take Ownership” of files and folders that reside on an NTFS File Server (or in larger cases with hundreds or thousands of servers) in a Windows Server 2008 R2 or Windows 7 environment.  I’m sure most IT professionals already know how to do this in the Windows Explorer GUI … but what if your task at hand required that you script this process to run during a limited window of time given during a server migration, and you had to minimize the amount of “clicks” as well as the amount of time spent on multiple servers?  

I was recently assigned to a project very similar to the scenario described above; and after a little research, I stumbled upon a little-known Microsoft tool called ‘takeown.exe’ that has been shipping with Microsoft Server products since Windows Server 2003.  Within minutes of discovering ‘takeown.exe’ I had a script written and I was running it in my test environment with positive results.  This shows how simple the tool really is!

Below is the usage example as seen from the command line ‘takeown.exe /?’:

TAKEOWN 
[/S system [/U username [/P 

					

						[password]]]] /F filename [/A] [/R [/D prompt]]

Below is my personally recommended example:  

TAKEOWN.exe /F C:MyFolder /R /A

 

As expressed above, my suggestions are to use the /F (to specify the folder to apply ownership on), the /R switch (as in “recursive” which mean to apply to all child objects, sub-folders and files) and the /A switch (which gives ownership to the “Administrators” group instead of the currently logged in user).  And while I didn’t use the /D switch in the above example, it may be necessary to use the “/D Y” to avoid being prompted in cases where the user ID running the command does not have rights to list the folders.   

You can also reference additional parameters by typing in ‘takeown.exe /?’ from the command prompt on any Windows Server 2008 R2 server or Windows 7 machine.

 

Read More