<html> <head> <title>Albert van der Sel : Introduction Powershell</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body bgcolor="#D8D8D8" link="blue" alink="blue" vlink="blue"> <h1>A simple note on "Scripting in Powershell"</h1> <B>Version</B> : 1.1<br> <B>Date</B> : 22/04/2013<br> <B>By</B> : Albert van der Sel<br> <hr/> <br> It's not great, or something.... But, this note might have some value, if your knowledge of Powershell (PS)<br> is pretty low.<br> <br> Everybody can learn it fairly quickly. If <I>even</I> Albert can learn it..., then you can do too... for sure !<br> But, the note is very simple and fairly short. That's on purpose, in order to have a quick startup.<br> <br> <font face="arial" size=2 color="brown"> Addition 22/01/2018: Chapter 5.<br> <br> This note was created when Powershell v1/v2/v3 were current. Chapters 1-4 are still very well usable, since they<br> only deals with the basic-, and scripting actions, and syntax, and CmdLet's.<br> Chapter 5 is current, that is, also Powershell v4/v5 and modules are covered, specifically targeted for<br> interfacing to SQL Server. So, the example shown in 4.7, is a bit outdated.<br> <font face="arial" size=2 color="black"> <br> <br> <font face="arial" size=2 color="blue"> <B>Main Contents:<br> <br> Chapter 1. Introduction: What is it?<br> <br> Chapter 2. Basic scripting (to get into the "mood").<br> <br> Chapter 3. A more structured approach: syntaxes, loops, tests, IO.<br> <br> Chapter 4. Further examples, explained.<br> <br> .............Example 4.1: delete files from a folder (and subfolders) which are older than "X" days.<br> .............Example 4.2: Viewing and managing a "Win2K8 / Win2K12 Failover Cluster".<br> .............Example 4.3: Put quotes around the records of a (flat) .csv file.<br> .............Example 4.4: Some AD examples.<br> .............Example 4.5: Loop through a text file with accounts and assign file system permissions.<br> .............Example 4.6: Compare txt files (.txt, .csv etc...).<br> .............Example 4.7: Calling a stored procedure, or starting a SQL Agent job, from Powershell.<br> .............Example 4.8: Finding the most recent file in some folder.<br> .............Example 4.9: Rename file(s).<br> .............Example 4.10: "Tail" or Show, the end of large txt files (like large logfiles).<br> <br> Chapter 5. Powershell and SQL Server.<br> <br> </B> <font face="arial" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h2>Chapter 1. Introduction: What is it?</h2> <font face="arial" size=2 color="black"> Powershell just looks like a "script interpreter" and commandline interface, like a sort of evolution of the "cmd"<br> commandline interface, which existed (and still does) in Windows for many years. But it's a bit more than that.<br> <br> Microsoft choose Powershell as the platform for scripting tasks in general, but with a strong accent on all sorts of<br> <B>administrative automation</B> like installing applications, or retrieving information of your local or remote Hosts, or interacting<br> with Active Directory (like automated addition/deletion/modification of accounts, or getting listings of all sorts), etc.. etc..<br> <br> Very typical of Powershell, is that you use socalled <B>"CommandLets"</B> (CmdLets), which are very specific small pieces of code, each designed<br> for specific tasks. You probably need to get used to it, but very typical for working with PS is, is that you place the CmdLets in series, whereas<br> the <I>output</I> of a former CmdLet, will be the <I>input</I> for the following CmdLet.<br> <br> Here is a very simple example:<br> <br> From the old fashioned commandprompt (cmd), type "powershell" and press Enter.<br> Note: often it is neccessary to start the prompt with "Administrative privileges" (RunAs), and later more on this.<br> <br> <font face="courier" size=2 color="blue"> C:\> powershell<br> <br> PS C:\><br> <font face="arial" size=2 color="black"> <br> The powershell prompt, denoted by something like "PS C:\>" will come up. Now just type the following code behind the PS prompt:<br> <br> <font face="courier" size=2 color="blue"> PS C:\> Get-Service | Where-Object {$_.status -eq "running"}<br> <font face="arial" size=2 color="black"> <br> It prints a screen of which background processes (services) are running on your machine.<br> <br> Here, you have used two CmdLets: "<B>Get-Service</B>" and "<B>Where-Object</B>". The "Get-Service" CmdLet just retrieves a list of <B>all</B> services<br> which are installed on your system. That's simply its task. Normally, it would just dump that output to your screen.<br> <br> However, by using the <B>"|"</B> (pipeline) symbol, we redirected the output from "Get-Service", to the next CmdLet,<br> namely "Where-Object". So, instead of dumping the records of the output to the screen, the "Where-Object" will receive them<br> as <B>input</B>.<br> <br> As "Where-Object" gets record by record from "Get-Service", you need to tell it what to do with it. That's determined within the <B>"{ }"</B>symbols,<br> because here you see the "condition" that tells "Where-Object" what to do:<br> It must filter the output of "Get-Service" to show only the <I>running</I> services, and not all installed services (of which<br> many maybe not running). Thats actually the condition <B>"status equals running"</B>, or in code: <B>{$_.status -eq "running"}</B>.<br> <br> Now you may still wonder about the "$_" symbol. Indeed, often you do not need need to declare variables beforehand, and some are<br> just already "implicitly there" so to speak. "$_" is a standard variable, acting as a container for records destined<br> to, or from, CmdLet's.<br> <br> It means this:<br> We have setup a "pipeline" from the "Get-Service" CmdLet, to the "Where-Object" CmdLet.<br> So, data "streams" from "Get-Service" to "Where-Object"CmdLet. Every record transferred, is contained in the "$_" variable,<br> which is always how it works in Powershell, when two CmdLets are connected by a pipe.<br> <br> Now, if you would zoom in into one such a record, it usually has multiple properties (values), and it this specific case,<br> we have a "status" property, which may have either of two values: "running" and "not running".<br> Powershell is also "Object Oriented", so an "object" as a record in $_, may have different properties.<br> In the example above, $_.status is such a propery, which may have a certain value (like "running").<br> <br> So, it makes sense to have as a condition for the "Where-Object" CmdLet, the expression {$_.status -eq "running"}.<br> <br> <B>&#8658; How to run code:</B><br> <br> There are two main ways to run code.<br> <ul> <li>Just like above, you simply enter it interactively at the PS prompt. When you are ready typing, you can run it immediately.</li> <li>Or, you place code in a <b>file</b> (scriptfile), and then you have the code stored, and you can run it anytime you want,<br> or you can even schedule it to run for specific date/times.</li> </ul> <br> <B>&#8658; How to create code (in scriptfiles):</B><br> <br> There are at least two ways to create powershell code:<br> <ul> <li>Simply use notepad or any other (flat file) editor, and save the file in a suitable folder.<br> Then, from the PS prompt you can easily call it like for example "PS C:\TESTSCRIPTS> .\myscript.ps1".<br> Here, the file "myscript.ps1" contains the PS code. Dont worry for now how this works exactly. We will see many examples below.</li> <li>Instead of simply using an editor, since Powershell v. 2, we have the "Integrated Scripting Environment" (ISE) available<br> which is a sort of graphical user interface interface, like a "workbench" really, to create scripts.</li> </ul> Actually, it doesn't matter too much what you choose. In this note, we simply use an editor to make scripts.<br> And, whatever you would have choosen, you need to type code anyway. But, ISE has indeed a few smart features.<br> <br> <B>&#8658; A tiny bit more on the architecture:</B><br> <br> If you don't care too much about the architecture, you can skip this section and go to Chapter 2.<br> <br> Figure 1 below, is a simple "sketch" of the general architecture. Powershell CmdLets are actually dot Net code.<br> Dot Net is Microsoft's modern architecture for developing, deploying, and running applications.<br> <br> With modern Windows versions, you get a set of "base" CmdLets. However, whenever you install a business application<br> like SQL Server, Exchange (and many more), you get additional specialized CmdLets that let you interact with those applications.<br> <br> <B>Fig 1. Simplified architecture Powershell.</B><br> <br> <img src="powershell1.jpg" align="centre"/> <br> <br> Your CmdLets, usually uses "providers" and api's to access various parts of the Windows Operating System, like the Registry, AD etc..<br> <br> Traditionally, the inteface to "WMI" (Windows Management Intrumentation) is much used. For example, using the <B>"Get-WMIobject"</B> CmdLet,<br> it's possible to retrieve information from almost everything of your system (like disks, printers, cpu, memory, processes etc..).<br> That's why I printed WMI "very fat" in the figure above, to underline it's importance.<br> <br> However, Powershell is not limited to the Windows Operating system. If the right CmdLets and providers are installed, you can<br> access other platforms too.<br> <br> Alongside the "base set" of CmdLets, delivered with your system, many specialized modules (containing providers and CmdLets)<br> came available the past few years. For example, easy to use CmdLets to access Active Directory, and to access all sorts of applications etc..<br> This "storm" is far from settling down. You will see: CmdLets and providers will come out that will let you access virtually everything.<br> That's why investing time and effort in studying Powershell is one of the best things you can do right now.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h2>Chapter 2. Basic scripting (loops, file modifications etc..).</h2> <font face="arial" size=2 color="black"> <font face="arial" size=2 color="blue"> <h3>2.1. A few preliminaries.</h3> <font face="arial" size=2 color="black"> In this chapter, we will create various scripts. Ofcourse, we will save them as files in some folder.<br> So, I suggest that you create a folder, called something like "C:\TEST" (or another name), and store your files there.<br> <br> But we will also just type in code "interactively", as in example 1 below.<br> <br> In creating scripts, we are going to use an 'flat file' editor, like notepad. I'am perfectly happy using notepad, but I must<br> say that are some free editors around with extended capabilities. Just google on "free Powershell editor". Maybe you like one of them.<br> But, since I only type in some short code pieces, notepad will do fine for me.<br> Before we do anything at all, here are some preliminaries for writing scripts. Just take notice of them.<br> <br> <br> <B>&#8658; Powershell file extension must be ".ps1":</B><br> <br> When you create scripts, be sure to name the file with the <B>".ps1"</B> file extension.<br> So, suppose you create a script that installs a certain printer, you might consider to name it something like "installprinter".<br> But, take notion of the fact that the <I>file extension</I> must be ".ps1", so, in the example above,<br> you should save the file as "installprinter.ps1".<br> <br> <br> <B>&#8658; If your scripts won't run:</B><br> <br> It's quite possible that <B>the execution of saved scripts</B> is disabled on your system as a result of a <B>security policy</B>.<br> Indeed, for Servers in a business environment, one should carefully consider the pro's and contra's of this measure.<br> <br> You can enable the execution of scripts using the "Set-ExecutionPolicy" CmdLet.<br> Then use one of the following options for this CmdLet: Unrestricted, RemoteSigned, AllSigned, Restricted.<br> If you use "Restricted", the execution of scripts is totally "off".<br> So, for systems in a business environment, you should carefully consider what is the best for you.<br> <br> Here, I suppose you use a personal test system, so let's go "unrestricted":<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> Set-ExecutionPolicy -executionPolicy Unrestricted<br> <font face="arial" size=2 color="black"> <br> Also, on Vista, Win2K8 etc.. If you start Powershell from the commandline (cmd), you might consider to start the command prompt<br> first with "elevated privileges" (Run as Administrator) and next start Powershell. Otherwise, you may run into permission issues.<br> In some situations, you do not want the "elevated privileges", but in others (like installing/modifying stuff)<br> you probably do need them.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>2.2. A few examples of using Powershell for file operations.</h3> <font face="arial" size=2 color="black"> With Powershell, you can perform all sorts of file manipulations.<br> Using a few examples, allows me to demonstrate a few important CmdLets, and how code "looks like".<br> <br> In many businesses, people are searching for the best solution for file manipulation. To illustrate this, suppose the organisation<br> uses a database where ascii files must be imported into, at a regular basis. Often, such a file does not "fit" in the database<br> in it's current layout. For example, it has one column too many, or one field must be modified, before it can be imported cleanly.<br> <br> <br> <B>Example 1: cutting one column off the content of a file</B><br> <br> Picture a scenario like this. Your boss runs in your office, waving with a USB stick and yelling: "This ascii file has one column too many".<br> But it's so large, I cannot use Excel or something.... Its too darn large!! I suppose you have some clever script dont you....??"<br> <br> While copying the "customers.txt" file from the stick to the "c:\test" directory on your local harddrive, you start thinking.<br> <br> Lets mimic this situation. Create a flatfile called "customers.txt", with some fields, all seperated by just one "whitespace", like so:<br> <br> <font face="courier" size=2 color="blue"> 1 ABC Amsterdam<br> 2 Goofy Washington<br> 3 Ghostbusters Denver<br> <br> <font face="arial" size=2 color="black"> The "Get-Content" CmdLet could be interresting here. You can use it to spool the content of a flat file to the screen,<br> or even redirect it to another CmdLet.<br> <br> Lets view the content of the file using "Get-Content" first:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> Get-Content c:\test\customers.txt<br> <br> 1 ABC Amsterdam<br> 2 Goofy Washington<br> 3 Ghostbusters Denver<br> <br> <font face="arial" size=2 color="black"> <br> Now, lets try to get the third column off. Here is a piece of code which achieves that:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> Get-Content customers.txt | %{$data=<B>$_</B>.split(" "); Write-Output "$($data[0]) $($data[1])"}<br> <br> 1 ABC<br> 2 Goofy<br> 3 Ghostbusters<br> <br> <font face="arial" size=2 color="black"> Now, that line might look "impressive", bit it's really not difficult, once you are acustomed to Powershell style.<br> <br> I think it's important that you know, that the "exact" details of the syntax are <B>not important</B> right now: I would like you to understand<br> the "general idea" only. Only after chapters 1,2 and 3 are "consumed", you should have an idea about the right syntax.<br> <br> Let's see what the upper command does.<br> First, we have "Get-Content customers.txt | " part, which opens the file, and then it wants to push every record it reads<br> to whatever follows after the "|" pipe symbol.<br> <br> Now actually, the stream of data goes to the CmdLet "Write-Output", which seems to print only two variables, namely<br> $data[0], and $data[1].<br> <br> But before the "Write-Output" CmdLet comes in action, you will notice the variable assignment "$data=<B>$_</B>.split(" ");".<br> As we know from above, every record pushed by "Get-Content", is contained in the "$_" variable.<br> So, as the first line is read by "Get-Content", the "$_" will contain:<br> <br> 1 ABC Amsterdam<br> <br> But $data is defined as "$data=<B>$_</B>.split(" ");", where the split method is called from the object "$_".<br> Now as you may know from modern Object Oriented Languages (OO), an "object" might contain data, as well as call-able<br> functions. Here, "$_" exposes indeed an number of function, among "split()" is one of them. <br> Now, let's quickly see what split() does. Take a look at the following intermezzo:<br> <br> Take a look at this code:<br> <br> First, we declare a variable $string = "fries hamburger milkshake" to be one contiguous string. Then we call the<br> split() method, to split up this string in independent pieces. Note that the argument to the split function is " "<br> which tells split(), to split on the "whitespaces".<br> <br> <font face="courier" size=2 color="brown"> PS C:\TEST> $string = "fries hamburger milkshake"<br> PS C:\TEST> $string.split(" ")<br> fries<br> hamburger<br> milkshake<br> <br> Once a variable is of "string" type, you automatically have the ".split" method (function) available.<br> <br> <font face="arial" size=2 color="black"> So, what "$_" contains, will be split in independent piecies, and placed into the $data variable.<br> <br> Now, $data is automatically an "array of values", because of the intrinsic power of Powershell.<br> Powershell "knows" that if any variable contains independent pieces, it treats it as an array of values.<br> So, you might say that $data can be seen as $data[0], $data[1], and $data[2] (counting from 0).<br> Thus, for the first record read and pushed by "Get-Content", we have:<br> <br> $data[0]=1, $data[1]=ABC, and $data[2]=Amsterdam<br> <br> Lastly, "Write-Output" will only print $data[0] and $data[1].<br> <br> This process is continued for the second record read by "Get-Content", and the third etc.. all the way down to the last record.<br> <br> The result is that we have cut off the third column. However, the result is printed on your screen.<br> <br> Note:<br> <br> Sometimes a command entered at the Powershell prompt, is a bit "long". You can always press enter right after a pipe symbol,<br> since Powershell then knows that you want to enter more text.<br> Powershell then displays the ">>" symbols which denotes that you can enter more text.<br> So, then it looks like this:<br> <font face="courier" size=2 color="blue"> <br> PS C:\test><br> PS C:\test> Get-Content customers.txt |<br> >> %{$data=$_.split(" "); Write-Output "$($data[0]) $($data[1])"}<br> >><br> 1 ABC<br> 2 Goofy<br> 3 Ghostbusters<br> <font face="arial" size=2 color="black"> <br> <br> <B>Example 2: cutting one column off the content of a file, and save it to another file</B><br> <br> Basically, it's the same as example 1 above. So for explanations, see example 1.<br> <br> As you can see in the code below, this time we use a third CmdLet, namely "Out-File", which takes any stream it gets left from the pipe,<br> and writes that to a file of your choice, in this case "customers2.txt".<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> Get-Content customers.txt |<br> >> %{$data=$_.split(" "); Write-Output "$($data[0]) $($data[1])"} | Out-File customers2.txt<br> >><br> PS C:\test><br> <br> <font face="arial" size=2 color="black"> If you open customer2.txt with notepad, you will see that only two columns of data is present.<br> <br> <br> <B>Example 3: cutting one column off the content of a file, using a script, with a parameter</B><br> <br> What we have done above, we are now going to place in a real script. Ofcourse, the script must be setup in such a way so that<br> it can be called with an argument. This argument is then any txt file, from which we only want the first two<br> columns saved in another file.<br> <br> So, in the "C:\TEST" directory, start notepad and create a file called "test1.ps1".<br> Place the following code in "test1.ps1":<br> <br> <font face="courier" size=2 color="blue"> Get-Content $args |<br> %{$data=$_.split(" "); Write-Output "$($data[0]) $($data[1])"} | out-file newfile.txt<br> <br> <font face="arial" size=2 color="black"> Now, let's run it:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> .\test1.ps1 customers.txt<br> <br> <font face="arial" size=2 color="black"> You should notice two things here:<br> <br> (1):<br> Do you see that I called the script, with a parameter (or argument), which is the name of the file from whose content<br> we want only want two columns stored in another file.<br> Do you also see the special variable "$args" directly behind "Get-Content"?<br> <br> Using a script with a simple parameter is really easily done, since the <B>$args</B> automatically takes on any value<br> of the argument passed to the script.<br> <br> (2):<br> Starting a script from the Powershell prompt is really done in this way: ".\scriptname.ps1".<br> You might wonder where this strange looking syntax is coming from.<br> <br> A "." ( a dot) means the "current directory", which in this case is "c:\test". Now, since we need to add a slash "\" to get a full path<br> to the file, we have string like ".\scriptname.ps1" which then resolves to "c:\test\scriptname.ps1".<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>2.3. A few examples of using Loops.</h3> <font face="arial" size=2 color="black"> Let's review a few examples that use a loop. In this section, we will illustrate the "foreach" loop.<br> You can find more loop constructs, like the "while" loop, in Chapter 3.<br> <br> <B>Example 4:</B><br> <br> Just type in the following interactively at the PS prompt:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEMP> $nodelist=("Harry","Nancy") -- press enter<br> PS C:\TEMP> foreach ($file in $nodelist) -- press enter. The prompt changes to ">>"<br> >> {<br> >> write-output "hello $file"<br> >> }<br> >><br> hello Harry<br> hello Nancy<br> <br> <font face="arial" size=2 color="black"> This one should be easy to follow. First, we have a variable "$nodelist" containing the values ("Harry","Nancy").<br> <br> Did you noticed that I did not declare that variable at all? I did an value assignment right away, and Powershell automatically<br> declares the variable for me. Saves time and coding.<br> <br> Then we have the heart of the loop using the "foreach" statement.<br> <br> Foreach uses a "condition" that, if it evaluates to "true" (or if still values present), it goes for another swing. Otherwise, it stops.<br> In this example, it means that <I>"as long as there are values in $nodelist"</I>, perform the statements between the "{" and "}".<br> <br> <br> <B>Example 5:</B><br> <br> In this example, we use a script to start (or stop) a set of related services of some kind.<br> In this example, a take Oracle services on a Windows system, where the display name of such a service starts with "Oracle"<br> like for example the service "OracleServiceTESTDB11g"<br> <br> Now, for your system, if you don't have Oracle installed, maybe you can think of another set of services to use.<br> Or, you can just read this example and not do it "hands-on". The primary object is that you understand the loop that is used here.<br> You don't have to "perse" perform each example on your system.<br> <br> So, lets create a script.<br> <br> Start notepad (or any other editor) and type the following code:<br> <br> <font face="courier" size=2 color="blue"> foreach ($svc in Get-Service){ <br> &nbsp if(($svc.displayname.StartsWith("Oracle")) -AND ($svc.Status -eq "Stopped")) {<br> &nbsp &nbsp echo $svc.DisplayName <br> &nbsp &nbsp Start-Service $svc.name<br> &nbsp } <br> <br> <font face="arial" size=2 color="black"> <br> Save the script with the name "oraclestart.ps1" in the "c:\test" folder.<br> <br> You can run the script, from the PS prompt, like so:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> .\oraclestart.ps1 <br> <br> <font face="arial" size=2 color="black"> It should not be too difficult to follow this code.<br> <br> First, we have a "foreach" loop. The condition of this loop is a list of all "services", that "Get-Service"<br> has produced as a list. So, actually, all code in the <B>"if"</B> statement is repeated for all services.<br> <br> But only where the "service display name" starts with "Oracle" and where this service has the status "stopped", then<br> the code between "{" and "}" is executed. The result is that all stopped Oracle services are started.<br> <br> If you can't actually do this example, don't worry. The main goal is to get a grip of Powershell, and not on a particular script example.<br> <br> <br> <B>Example 6: Rename a set of files: using a "sort of loop" (list), but its not an explicit loop.</B><br> <br> This is a bit of a "weird" example. Here I use Powershell to simply rename a bunch of *.txt files to *.bak files.<br> <br> Now, you might be tempted to use "foreach" again. That would be very reasonable, since "foreach" can be used for setting up loop.<br> <br> Reasoning along these lines, you might first try to simply list the *.txt files, and if that works, you might think<br> that there must surely exist a CmdLet that lets you rename an item (indeed, there is one, namely "Rename-Item").<br> <br> So, suppose you have a few .txt files in "c:\test", then lets try a simple listing first:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> $file=ls *.txt<br> PS C:\test> foreach ($a in $file) {<br> >> write-output $a<br> >> }<br> >><br> <br> <font face="arial" size=2 color="black"> Yes, this produces a listing of all *.txt files. So, if between the "{" and "}" a Write-Output is possible, then some<br> CmdLet that renames stuff must be possible too.<br> <br> This is an example where you might easily going to follow a path that's not wrong, but maybe somewhat slightly cumbersome.<br> What we want can be done in a much more efficient way, if only we knew of a CmdLet that produces a list of the right set of files,<br> which we immediately can pipe through to the "Rename-Item" CmdLet.<br> <br> For this, the <B>"Get-ChildItem"</B> would work perfectly. Just take a look a this working code:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> <B>Get-ChildItem</B> *.txt | Rename-Item -NewName { $_.name -replace 'txt', 'bak'}<br> <br> <font face="arial" size=2 color="black"> The "Get-ChildItem" CmdLet, resembles the old DOS "dir" command. You can get a full listing of all files, or just a "filtered" listing,<br> like a list of *.txt files, or like a list of files that have a name which begins with "book*" etc.. etc..<br> <br> Now, its actually the same story again: Get-ChildItem produces a listing, a recordset as it were, where each record is piped<br> to "Rename-Item". Then "Rename-Item" does the actual renaming of each filename that it receives from Get-ChildItem.<br> Ofcourse, "$_" is used again, where the "$_.name" is actually the property to give another value.<br> <br> It's not an explicit "loop" we see here. But, still, we have a piece of code that just works through a list, namely the list<br> that "Get-ChildItem" produces.<br> <br> This CmdLet is worth while to remember. It comes in handy anytime you want to do something with a list of files.<br> <br> <font face="arial" size=2 color="brown"> Question:<br> <br> Instead of:<br> <br> PS C:\TEST> <B>Get-ChildItem *.txt | Rename-Item -NewName { $_.name -replace 'txt', 'bak'}</B><br> <br> Could we also do simply this?:<br> <br> PS C:\TEST> <B>ls *.txt | Rename-Item -NewName { $_.name -replace 'txt', 'bak'}</B><br> <font face="arial" size=2 color="black"> <br> <B>Example 7: Nesting </B><br> <br> More information on the usage of the curly brackets "{ }" and parenthesis "( )" can be found in the next chapter.<br> However, it's good to show in this section, how you should use "nested" statements. A simple example can illustrate this.<br> <br> Suppose in some directory, you have files with names like *aa*.txt, *bb*.txt, and many other names.<br> Suppose you only want to select the *aa* files, and perform some operation on them.<br> <br> Then, you could<br> <br> - first create a collection (or list) to contain all files.<br> - then, using "foreach", loop through the list.<br> - then, use an "if" test, to test if a member is an "*aa*" file.<br> - then, perform whatever operation.<br> <br> <font face="courier" size=2 color="blue"> $filelist=ls *.txt<br> foreach ($myvar in $filelist) <br> <B>{</B><br> &nbsp if ($myvar.name -like "*aa*")<br> &nbsp <B>{</B><br> &nbsp &nbsp write-output $myvar.name<br> &nbsp <B>}</B><br> <B>}</B><br> <br> <font face="arial" size=2 color="black"> Ofcourse, the above example can be simplyfied by just creating a collection like "$filelist=ls *aa*.txt<br> However, the purpose is to show you the "nesting", and the use of "{ }" enclosing various "levels" of statements.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h2>Chapter 3. A more structured approach.</h2> <font face="arial" size=2 color="black"> Let's take a more structured approach now. Now, its my experience that a very structured doc is.., well.., sometimes a bit dull.<br> But the author here is a certain Albert. So, very structured stuff is not to be expected. ;-)<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.1. Powershell variables.</h3> <font face="arial" size=2 color="black"> As we have seen before, a variable does not need to be declared before you can use it. You can immediately do an assignment<br> of a variablename, to a value.<br> For example (as seen before):<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $string = "fries hamburger milkshake"<br> PS C:\TEST> $string.split(" ")<br> fries<br> hamburger<br> milkshake<br> <font face="arial" size=2 color="black"> <br> Here, "$string" is a variable, which seems to hold a <B>"string-like"</B> value. Well, it's string alright, because the value is enclosed by "".<br> Ofcourse, <B>"numbers"</B> can be placed in a variable too. Try this:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $var1=5<br> PS C:\TEST> $var2=2<br> PS C:\TEST> $var3=$var1 + $var2<br> PS C:\TEST> Write-Output $var3<br> 7<br> <font face="arial" size=2 color="black"> Yes, 5+2=7, so variables holding numbers works too. Also, if such a simple calculation works, then it's immediately clear that also quite complicated <br> arithmetic will work too in a similar way.<br> <br> Note that I used "Write-Output". We have seen "Write-Output" already before in a former example. But with a variable, you don't need to use that CmdLet.<br> Just entering the variable name from the PS prompt will show it's value.<br> <br> I did not use the "$" character in the variable names without a reason. In Powershell it's simply required.<br> So all your variables must start with a "$" in their names.<br> <br> <B><U>Typing:</U></B><br> <br> It's interesting to see what happes in above calculation, if you would try $var1=5 and $var2="2"<br> That is: $var1 is obviously a number, but $var2 seems to be a string because it's enclosed by "".<br> As you will see, the calculation still works like clockwork.<br> <br> So, Powershell does not place "severe restrictions" on types beforehand. No "strong typing" here. I think it's swell for a scripting language.<br> <br> However, if needed, you can try (a bit of) "strong typing", like telling Powershell beforehand what the datatype is. Like so:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> [int] $var1=10<br> PS C:\TEST> [int] $var2=2<br> PS C:\TEST> $var3=$var1 * $var2<br> PS C:\TEST> $var3<br> 20<br> <font face="arial" size=2 color="black"> <br> <B><U>Special Variables:</U></B><br> <br> <B>"$_":</B><br> <br> Remember the "$_" variable? Its always the variable acting as a placeholder for data when CmdLet1 "pipes" the records<br> to CmdLet2. So, CmdLet2 can then immediately work with the "$_" variable.<br> <br> <B>"$args":</B><br> <br> When you create a script that expects one or more arguments (parameters), then $args in the script will be the holder of those argument(s).<br> See example 3 in chapter 2 for an example.<br> <br> There are a few more of those "special" variables. These are not too spectacular.<br> <br> <B><U>Arrays:</U></B><br> <br> An "array" is a sort of "multi-value" variable. So, it stores multiple values (also called "elements"). Now, to address one particular value<br> in the array, you can use an "index". Just like the homes in one street, you can distinguish between those homes,<br> since they all simply have a unique number. In an array, the "index" starts from 0.<br> <br> Remember example 1 in chapter 2? Here we had a variable $data, which contains three elements. In this example, "Write-Output"<br> only printed the values $data[0] and $data[1]. It did not printed $data[2].<br> It might be instructive to take a look at example 1 again.<br> <br> To use an array, you can simply assign a "multi-value" range to a variable like in these examples:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $myArray1 = 1,2,3,4,5<br> PS C:\TEST> $myArray2 = "oracleSRV1", "oracleSRV2", "oracleSRV3"<br> PS C:\TEST> Write-Output $myArray2[1]<br> <br> oracleSRV2<br> <br> <font face="courier" size=2 color="brown"> Task:<br> This note is no more than just a simple intro. Once you have obtained some grip in Powershell,<br> find out what you can do with the "hash table" variable.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.2. More on Loops.</h3> <font face="arial" size=2 color="black"> <br> <B><U>The "foreach" loop:</U></B><br> <br> In chapter 2, we have seen a few examples of the "foreach" statement, like for example:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> $file=ls *.txt<br> PS C:\test> foreach ($myvar in $file)<br> >> &nbsp {<br> >> &nbsp <I>Some code... </I><br> >> &nbsp }<br> >><br> <br> <font face="arial" size=2 color="black"> Actually, the "foreach" statement is great for working through some <B>list</B>, or <B>collection</B>, just like the example above,<br> where "ls *.txt" produces a list of .txt files.<br> <br> <br> <B><U>The "Foreach-Object" loop:</U></B><br> <br> Just as above, "Foreach-Object" CmdLet is great for working through some <B>list</B>, or <B>collection</B>.<br> Now, "foreach" is a command in an expression, while "Foreach-Object" <B>is a CmdLet</B>.<br> <br> So, whenever jou have a CmdLet that "pipes" records to a next CmdLet for processing that record collection, you should use<br> the "Foreach-Object" CmdLet. So, "Foreach-Object" takes its records from the pipeline.<br> <br> The "Foreach" statement, does not need a pipeline. It just takes a list of values (like names of files etc..)<br> <br> As an example of using the "Foreach-Object" CmdLet, take a look at this example:<br> <br> <font face="courier" size=2 color="blue"> PS C:\test> Get-WmiObject Win32_Printer | ForEach-Object {$_.Name}<br> <br> <font face="arial" size=2 color="black"> <br> The "Get-WmiObject" CmdLet pipes records from the "Win32_Printer" class, to the "Foreach-Object" CmdLet.<br> So, its a clear example how the "Foreach-Object" CmdLet gets it's "input".<br> <br> But there are more loop constructs in Powershell. Sometimes, you want to work with a "counter" that increases (or decreases)<br> with each iteration, until this counter reaches a "final value" (like 0, or 1000 etc..).<br> For this type of true loops, the "while" loop can be used.<br> <br> <br> <B><U>The "while" loop, or equivalently, the "do while" loop:</U></B><br> <br> The "while" loop (or "do while" loop) is very usefull when you want some processing done "while a certain condition remains true".<br> It's defined as follows:<br> <br> <font face="courier" size=2 color="blue"> while (condition true)<br> {<br> statement(s)<br> }<br> <br> or stated a bit differently:<br> <br> do {statements}<br> while (condition true) <br> <br> <font face="arial" size=2 color="black"> Both forms are equivalent. To illustrate it, try this example. As the condition I have used <B>"while (1 -le 2)"</B>,<br> meaning: "while (1 is lower than 2) do statements"<br> <br> This condition is always true! So the statements will be repeated forever (until you use Ctrl-C).<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEMP> while (1 -le 2) {write-output "hello"}<br> <br> <font face="arial" size=2 color="black"> You see? It's a silly example. The condition is such, that never an "untrue" value will be reached.<br> <br> A better example would be this:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $i=1<br> PS C:\TEST> while ($i -le 5) {$i=$i+1; write-output "Hi, here is $i"}<br> Hi, here is 2<br> Hi, here is 3<br> Hi, here is 4<br> Hi, here is 5<br> Hi, here is 6<br> <font face="arial" size=2 color="black"> <br> Here is use a counter, $i, which initially is set to "1".<br> Then the condition follows, which in English is: do while $i is lower than or equal to 5, the following statement(s).<br> <br> In the statement block, I first increase the counter by 1. Then follows a print statement.<br> <br> <font face="arial" size=2 color="brown"> Question: Can you explain why the output shows a $i=2 first, and stops at $i=6 ?<br> <font face="arial" size=2 color="black"> <br> Many years ago, like in the old fashioned "C" programming age, a much better way was developed to describe the increase<br> of such a counter, as "$i" in the example above. Once you have seen it, you will <I>never ever</I> use the "boring" $i=$i+1;<br> increment again!<br> Take a look at this compact way to code:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $i=1<br> PS C:\TEST> while ($i -le 5) {write-output "Hi, here is $i";<B>$i++</B>}<br> Hi, here is 1<br> Hi, here is 2<br> Hi, here is 3<br> Hi, here is 4<br> Hi, here is 5<br> <br> <font face="arial" size=2 color="black"> You see that? The "$i++" is the same as the "boring" "$i=$i+1", but it has certainly some more "bling" and style.<br> By the way: professional Powershell scripts will always use that "style", since thats the way to to program increments since ages.<br> <br> <font face="arial" size=2 color="brown"> Question: Can you explain why the output shows a $i=1 first, and now stops at $i=5 ?<br> <font face="arial" size=2 color="black"> <br> Sometimes you do not want an increment of $i by "1", using $i++ but an increment by any integer like "3" or "5" or "100" etc...<br> For this, you can use the "$+=increment" notation. An example might make this clear:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $i=5<br> PS C:\TEST> while ($i -le 26) { $i; $i+=5}<br> <br> 5<br> 10<br> 15<br> 20<br> 25<br> <br> <font face="arial" size=2 color="black"> So keep the usages of "$i++" and "$i+=value" in mind. At many times, they are incredably usefull.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.3. More on CmdLets.</h3> <font face="arial" size=2 color="black"> Up to now, we have only seen the following CmdLets:<br> <font face="courier" size=2 color="blue"> <br> Get-Service<br> Where-Object<br> Set-ExecutionPolicy<br> Get-Content <br> Write-Output <br> Write-Host<br> Get-WmiObject<br> Get-ChildItem <br> Foreach-Object<br> <br> But we are going to meet quite a few more in the text below.<br> <font face="arial" size=2 color="black"> <br> How can we get a list of availble CmdLets? To get a list of CmdLet's which are available in your session,<br> you can use the <B>"Get-Command"</B> CmdLet.<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> Get-Command<br> <br> <font face="arial" size=2 color="black"> This produces a large list of CmdLets, with a possible "alias" (or shortname) to that CmdLet.<br> The listing will rush by on your screen. If you want to see it "one screen at the time", use <B>"Get-Command | more"</B>.<br> <br> Anyway, it's an impressive list.<br> <br> A CmdLet is "dot Net" code. It's not like a traditional ".exe", that you can find in some folder on disk.<br> Instead, dot Net "classes" were created in a hierarchical "namespace", which naming structure "resembles"<br> a sort of folder structure.<br> <br> Objects can be "intstantiated", meaning that they get executed in the supporting dot Net runtime environment. <br> The classes are the blueprints, for the objects that "live" in the dot Net "machine".<br> <br> Often, CmdLets are implemented in "assembly files" which really can be found on disk. The filenames of such files<br> often have a resemblence to the part of the namespace they belong too. For example, you might find files like <br> "Microsoft.PowerShell.Management.dll" or "Microsoft.WSMan.Management.resources.dll" etc.., if you would search for it.<br> <br> It's also possible to "load" additional CmdLets in your session from "some" assembly module.<br> <br> What often happens too, is that when you install additional features or roles on a Windows Server system,<br> that then also additional Powershell modules get installed on your system, often in the form of assemblies.<br> <br> <br> <B>Importing additional functionality: "Add-PsSnapIn" and "Import-Module":</B><br> <br> Before you start to work on some project, are you sure you have the right CmdLets for the job?<br> <br> You know, with the base set of CmdLets, you can create perfect scripts for a almost unlimited variety of tasks.<br> <br> However, sets of CmdLets can be <B>"imported"</B> to your session, and it could well be that those CmdLets are a great help<br> for certain tasks, like managing Internet Information Server, managing SQL Server, Sharepoint, Clusters, VMWare etc.. etc..<br> <br> In Powershell version 1, you could load a socalled "Snapin" which was the source for additional CmdLets.<br> In version 2 and higher, you can now load a socalled "Module", which does not need registering, and is easier to use.<br> But, also in v.2 you can load a Snapin, if required.<br> <br> There are two CmdLets that let you import a Snapin or a Module (see below): Add-PsSnapIn and Import-Module.<br> For example, PowerShell snapins are binaries (.dll) that implement cmdlets and providers. You must install the snapin<br> and then add the cmdlets in the snapin to your session using the Add-PSSnapin cmdlet.<br> <br> Here are two examples:<br> <br> <font face="courier" size=2 color="blue"> Powershell v1 style (after installing the snapin). You might have to use Set-Location "path" first.<br> <br> PS C:\TEST> Add-PSSnapin Microsoft.SharePoint.PowerShell<br> <br> Powershell v2 style:<br> <br> PS C:\TEST> Get-Module -ListAvailable<br> <br> <I>produces a list of available modules...</I><br> <br> PS C:\TEST> Import-Module -Name ModuleName<br> <br> <font face="arial" size=2 color="black"> Since CmdLets are perfectly designed for "passing objects through a pipe", the following statement would not be very surprising:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> Get-Module -ListAvailable | Import-Module<br> <br> <font face="arial" size=2 color="black"> As an example, if you would load the "Active Directory" module, many new CmdLets are available which are specialized for<br> AD operations. This means that with simple short statements or scripts, you can add or list accounts, computers etc.. etc..<br> Truly amazing stuff, really.<br> <br> You would load the AD module, using:<br> <br> <font face="courier" size=2 color="blue"> PS C:\> Import-Module ActiveDirectory <br> <font face="arial" size=2 color="black"> <br> On a Win2K8 DC, the module is available right away. From a Win7 workstation, you can load the module,<br> but additional steps need to be taken. This is not the subject of this introduction.<br> <br> If you would want a list of AD useraccounts, using VB, it would be a relatively lengthy story (well, not really that much).<br> Not with PowerShell. On a DC, try this:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> Get-Aduser  filter * | export-csv "listing.csv"<br> <font face="arial" size=2 color="black"> <br> The "Active Directory Module for Windows PowerShell", offers at least 90 (or so) new CmdLets for full<br> AD management, like for example "New-ADUser", "New-ADComputer".<br> <br> The upper information is truly "minimal". The only effect can be, that at least you are aware that there exists options<br> to load additional functionality.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.4. Functions.</h3> <font face="arial" size=2 color="black"> A "function" is nothing else than a block of statements, identified under one "name".<br> Usually, a function takes one or more arguments, but that's not a requirement.<br> It's really that simple. So, the "simplest of simplest" of all functions, could be this one:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> function myfunction<br> >> {<br> >> write-host "Hi there!"<br> >> }<br> >><br> PS C:\TEST> myfunction #call the function <br> Hi there!<br> <br> <font face="arial" size=2 color="black"> Obviously, this one is not so usefull, but it's still a fuction allright.<br> <ul> <li>One obvious advantage of a function is, is that you can call it more than once in a script.</li> <li>Another advantage is, is that you can create a function to do specific things, and once it works,<br> you can just almost blindly re-use it again in other code.</li> <li>If you have a large script, the use of a function makes it easier to read, instead of repeating the code over and over again.</li> </ul> Using parameters (or arguments) can be done in several ways.<br> One way is to define the function with parameters <I>directly</I>. Here is a simple example:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> function findfile ($p, $f)<br> >> {<br> >> Get-Childitem $p -Recurse | Where {$_.Name -eq "$f"}<br> >> }<br> >><br> PS C:\TEST> findfile "c:\" "oops.txt"<br> <br> Mode..........LastWriteTime......Length...Name<br> -a--..........06.01.2013 19:26...5........oops.txt<br> <br> <font face="arial" size=2 color="black"> Ok, maybe not the greatest example of all, but it should be easy to follow. Here, I created a function <I>that finds a file.</I><br> It uses as arguments the "starting path" (like "C:\") and the "file name" (like "oops.txt").<br> <br> This example relies heavily on the <B>"Get-Childitem"</B> CmdLet. This is an important CmdLet indeed.<br> We have seen it before in example 6. In short, a folder can be the "parent" of subfolders and files.<br> So, if we use a CmdLet which essentially says "get all childs within $p" with the condition "where the childname must be $f",<br> then it indeed returns the name of such a file, if it exists.<br> <br> The above really is just minimal information. There is much more to learn on functions.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.5. A few notes on when to use curly brackets "{ }" and parenthesis "( )".</h3> <font face="arial" size=2 color="black"> In the scripts sofar, we have seen the use of curly brackets "{ }" and parenthesis "( )" in various "places".<br> But there has to exist certain rules for this. That's indeed true.<br> <br> Sometimes it "feels" quite intuitive, the more so if you already have use other scripting/programming languages.<br> <br> For example a <B>"statement block"</B> always goes between curly brackets "{ }". For example:<br> <br> <font face="courier" size=2 color="blue"> $filelist=ls *.txt<br> foreach ($a in $filelist) <br> <B>{<br> write-output $a<br> }</B><br> <br> <font face="arial" size=2 color="black"> Although the "statement block" here, is just one statement, the <B>"write-output $a"</B> sits between the "{ }"<br> <br> The simple example above, also shows a clear example when using parenthesis "( )".<br> The "foreach" loop must process a list, and this list is a sort of "control structure" for the loop on where to start,<br> and when to end the loop. In this case, the controlstructure is just a list of all .txt files.<br> <br> But this is typical for loop structures. Anytime a list or expression is used to control a loop structure, it'sits between "( )".<br> Here, take a look at this while loop:<br> <br> <font face="courier" size=2 color="blue"> $i=1<br> while <B>($i -le 5)</B> {write-output "Hi, here is $i";<B>$i++</B>}<br> <br> <font face="arial" size=2 color="black"> But there are occasions where the use of curly brackets "{ }" and parenthesis "( )" might not be so obvious.<br> <br> For example, when you have a CmdLet that pipes to another CmdLet, where the second CmdLet <B>must evaluate</B> a condition or expression,<br> then curly brackets "{ }" are used.<br> <br> Here are a few examples:<br> <br> <font face="courier" size=2 color="blue"> => For each device in the printer class, only show it's name:<br> <br> <B>Get-WmiObject Win32_Printer | ForEach-Object {$_.Name}</B><br> <br> => Only print the properties for this one printer, called "Fax":<br> <br> <B>Get-Wmiobject Win32_printer | where {$_.name -eq "Fax"}</B><br> <br> => Again an example where a CmdLet (Get-Childitem) pushes records to "Where" (an alias to Where-Object), which must evaluate something.<br> <br> <B>function findfile ($p, $f)<br> {<br> Get-Childitem $p -Recurse | Where {$_.Name -eq "$f"}<br> }</B><br> <br> <font face="arial" size=2 color="black"> Whe have seen the use of square brackets "[ ]" too, in a few occasions. They are often used to denote array members.<br> <br> <br> <font face="arial" size=2 color="blue"> <h3>3.6. WMI.</h3> <font face="arial" size=2 color="black"> WMI or "Windows Management Instrumentation", abstracts computer resources (like disks, memory, netcards etc..), and<br> <I>anything else</I> for which a socalled "WMI providers" exists.<br> For example, if there should exists a "provider" to see "scheduled tasks", then this resource is viewable from the WMI api too.<br> So, it's not only hardware resources that are covered by WMI, but applications and Operating System resources as well.<br> <br> WMI is Microsoft's implementation of the general "CIM" standard ("Common Information Model").<br> <br> In WMI language, the stuff that is managed and viewable, are the "Objects", which (as usual) are organized "in" "Classes".<br> The "script" or "programming language" that wants to access the managed objects, are often called "consumers".<br> These can access managed objects through the use of the right "providers".<br> <br> The WMI infrastructure is fairly complex, and is partly also "COM" based. For this note, we do not need to know all of the details.<br> Important is, that Powershell CmdLets are available to query the WMI repository, which can obtain lots of systemdata.<br> <br> The WMI repository uses hierarchical namespaces. Every set of objects which are related, gets their own "namespace".<br> Each namespace has a sort of "root". The default "root", if Powershell accesses WMI, is "root\cimv2", which is a collection<br> of classes which represents your computer system as a whole. However, usually other namespaces exists on your system as well,<br> like for example related to "applications", or a Directory Service etc..<br> <br> The most important CmdLet is "Get-WmiObject", which can query namespaces, and all classes within a namespace (which is a class itself as well).<br> <br> Let's try this:<br> <font face="courier" size=2 color="blue"> <br> PS C:\TEST> Get-WmiObject -namespace "root\cimv2" -List<br> <br> <font face="arial" size=2 color="black"> This should return an impressive list of classes which you can query further.<br> <br> Since the default namespace is "root\cimv2", you do not need to put that as a parameter if you want to get information<br> of the classes shown in the above listing. You can just query the classes directly. Here are a few examples:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> Get-WmiObject -Class win32_LogicalDisk<br> PS C:\TEST> Get-Wmiobject -Class win32_OperatingSystem<br> PS C:\TEST> Get-WmiObject -Class Win32_NetworkAdapter<br> PS C:\TEST> Get-WmiObject -Class Win32_Printer<br> <br> <font face="arial" size=2 color="black"> Since an Object (like a printer) might have many "properies" (each with a value), you might be interrested in one property only.<br> In such a case, for printer objects, you can pass the records through to (for example) the "ForEach-Object" CmdLet,<br> and let it only print the "Name" (of each printer).<br> <br> Remember: Every record transferred by "Get-WmiObject", is contained in the "$_" variable, which is always how it works<br> when two CmdLets are connected by a pipe. So, if you only want the printername, then get the "$_.name" property for each printer in the list.<br> <font face="courier" size=2 color="blue"> <br> PS C:\test> Get-WmiObject Win32_Printer | ForEach-Object {$_.Name}<br> <br> <font face="arial" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h2>Chapter 4. Some Further examples.</h2> <font face="arial" size=2 color="black"> In time, for further "inspiration", I will add a couple of more scripts here, with appropriate comments.<br> For now, I think that already a few nice examples are listed. You can try them, or just simply browse along the code.<br> If you would agree (understand) the logic and syntax..., <I>that only</I> would already be fantastic !<br> <br> <br> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.1: delete files from a folder (and subfolders) which are older than "X" days.</B><br> <font face="arial" size=2 color="black"> <hr/> <br> The problem can be defined as follows:<br> <br> Many applications create logfiles. But after weeks, or months, those logfiles piles up, and take too much diskspace.<br> Maybe you only want to have the last 7 days, or 30 days (or so), online on disk.<br> So, older logfiles can be deleted. The following PS script does that.<br> You can schedule such a script, for example, to run once a day, once a week etc..<br> <br> <font face="courier" size=2 color="blue"> $Today = Get-Date <br> $NumberOfDays = "7" <br> $StartingFolder = "C:\temp" <br> $Extension = "*.log" <br> $LastWrite = $Today.AddDays(-$NumberOfDays) <br> <br> # Body of code:<br> <br> $FilesCollection = Get-Childitem $StartingFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"} <br> <br> foreach ($File in $FilesCollection) <br> { <br> Remove-Item $File.FullName | out-null <br> }<br> <br> <font face="arial" size=2 color="black"> Now, let's walk through this script.<br> <br> You see that first a few variables are set. You know that any variable starts with a "$" symbol.<br> The variable $Today equals the CmdLet "Get-Date", which, when called, retrieves the current date/time.<br> Then a few fixed variables are set, like "$StartingFolder", which I placed here at "C:\TEMP".<br> <br> The determination of "$LastWrite" deserves some explanation. You know that the plan is to remove all files<br> which are older that 7 days. To get at that point, we need a calculation later on, where the "date of 7 days ago"<br> must be known. Then we can investigate the "last write time" of any file, and compare it to the "date of 7 days ago".<br> <br> You can try the following statements (or just read them...)<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEST> $Today=Get-Date<br> PS C:\TEST> write-host $Today # lets see what we have<br> <br> 05.01.2013 13:25:02<br> <br> PS C:\TEST> $NumberOfDays="7"<br> PS C:\TEST> $LastWrite = $Today.AddDays(-$NumberOfDays)<br> PS C:\TEST> write-host $LastWrite<br> <br> 29.12.2012 13:25:02<br> <br> <font face="arial" size=2 color="black"> Yes, "$LastWrite" contains the "date", from 7 days ago.<br> <br> Next, with "$FilesCollection", we create a collection (or list) of files, in all subfolders in C:\TEMP,<br> which are older than 7 days. Notice the use of the "Get-Childitem" CmdLet.<br> We have seen "Get-Childitem" before in example 6. Here too, we wanted a list of files. The name "Get-Childitem"<br> already says it all. If "C:\TEMP" is the "parent", then every object in it, are it's "children".<br> <br> You should be able to have "an idea" how "$FilesCollection", after "Get-Childitem" did it's work, is a list of files<br> which must be processed. Next, a foreach loop will work through the list, and deletes the files.<br> <br> <font face="arial" size=2 color="brown"> Note:<br> <br> A similar cmd shell command is:<br> <br> <font face="courier" size=2 color="blue"> C:\> forfiles /P C:\webserver\logs /S /M *.log /D -30 /C "cmd /c del @FILE"<br> <br> <font face="arial" size=2 color="brown"> This example will delete all *.log files in the "c:\webserver\logs" directory, which are older than 30 days.<br> It seems almost easier than using Powershell ! Well, No. You do not want to know what effort MS used to build the "forfiles" command.<br> With Powershell, your CmdLets and everything else that make up the environment (e.g. loop constructs, functions etc..)<br> all form endlessly more powerfull building blocks, to build anything you need.<br> <font face="arial" size=2 color="black"> <br> <br> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.2: Viewing and managing a "Win2K8 / Win2K12 Failover Cluster".</B><br> <font face="arial" size=2 color="black"> <hr/> <br> Once you have installed a "Failover Cluster" on 2 or more Nodes, on each node, new Failover cluster CmdLets<br> are available on those systems.<br> <br> However, it's possible you still need to load the failoverclusters powershell module before you can use those new CmdLets.<br> <br> <font face="courier" size=2 color="blue"> PS C:\> import-module failoverclusters<br> <br> <font face="arial" size=2 color="black"> Let's take a look at a few of those new CmdLets. Here are some examples.<br> The commands shown, are only for illustrational purposes. In your particular situation, you often need to modify<br> such an example, before it really works (e.g. clustername, resource group name etc...), unless it's a generic command<br> which does not use a particular cluster object name (for example, create a Cluster log file).<br> <br> <font face="courier" size=2 color="blue"> <B>=> 1. Create the Cluster log file:</B><br> <br> Examples:<br> <br> - Create the log into the current directory (.), over the last 400 minutes (that could be a large ascii file).<br> <br> PS C:\> Get-ClusterLog -Destination . -TimeSpan 400<br> <br> - Create the log into the current directory, over the last 10 minutes.<br> <br> PS C:\> Get-ClusterLog -Destination . -TimeSpan 10<br> <br> <B>=> 2. Show cluster nodes:</B><br> <br> PS C:\> Get-ClusterNode <br> <br> Name..........State <br> ----..........----- <br> node1.........Up <br> node2.........Up <br> <br> If you are not connected to a certain Node of a certain cluster, or you want to see the state of the nodes<br> of another cluster, then supply the "clustername" to the CmdLet.<br> <br> PS C:\> Get-ClusterNode -Cluster ClusterSales<br> <br> <B>=> 3. Show info of a Cluster (or names of all Clusters)</B><br> <br> PS C:\> Get-Cluster<br> PS C:\> Get-Cluster cluster1 <br> PS C:\> Get-Cluster | fl * (shows lots of info)<br> PS C:\> Get-Cluster -domain antapex.nl <br> <br> <B>=> 4. Show cluster quorum info:</B><br> <br> PS C:\> Get-ClusterQuorum -Cluster SQLCluster1 <br> <br> <B>=> 5. Move (failover) a Group to another node:</B><br> <br> PS C:\> Move-ClusterGroup  Name SQLCLUS1 -Node node2<br> PS C:\> Get-ClusterNode node2 | Get-ClusterGroup | Move-ClusterGroup<br> <br> <B>=> 6. view resources for a certain Group:</B><br> <br> PS C:\> Get-ClusterGroup "GROUPNAME" | Get-ClusterResource<br> <br> <B>=> 7. Start/Stop a certain Group:</B><br> <br> PS C:\> Start-ClusterGroup SQLCLUS1<br> PS C:\> Stop-ClusterGroup SQLCLUS1<br> <font face="arial" size=2 color="black"> <br> <br> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.3: Put quotes around the records of a (flat) .csv file.</B><br> <font face="arial" size=2 color="black"> <hr/> <br> Suppose that from a database, a report is extracted using some export utility. This utility produces a ".csv" file,<br> which is a "comma seperated" ascii file. Suppose that this export file is called "pre_report.csv" and it is created by<br> the export utility in the folder "F:\Uploads\".<br> <br> Now, suppose that the records in "pre_report.csv" have a layout as in the following example:<br> <br> <font face="courier" size=2 color="blue"> Business Unit,Revenue,TotalCosts<br> NL,1000400,200562<br> US,200726,19925<br> BE,5999000,2001788<br> etc..<br> <br> <font face="arial" size=2 color="black"> Generally, there could be hundreds, or thousends (or even millions) of rows in such a file.<br> Anyway, suppose further that the file is not complete for further processing.<br> What the requirement is, is that the records shown above, needs to be enclosed (surrounded) by quotes (").<br> (For the sake of argument, let's suppose that the database extracting utility is not able to do this).<br> <br> So, we need this:<br> <br> <font face="courier" size=2 color="blue"> "Business Unit,Revenue,TotalCosts"<br> "NL,1000400,200562"<br> "US,200726,19925"<br> "BE,5999000,2001788"<br> etc..<br> <br> <font face="arial" size=2 color="black"> One solution is, that we create a PowerShell script that operates on every record in "pre_report.csv",<br> put double quotes around every record, and places them in the new "report.csv" .csv file.<br> Take a look at the following script, which will do exactly that.<br> <br> <font face="courier" size=2 color="blue"> Remove-Item F:\Uploads\report.csv<br> <br> $sym="`""<br> $list = Get-Content F:\Uploads\pre_report.csv<br> <br> foreach ($v in $list) <br> {<br> Write-Output "$sym$v$sym" >> F:\Uploads\report.csv<br> }<br> <br> <font face="arial" size=2 color="black"> As you can see, using the "Remove-Item" CmdLet, we start with removing the former "report.csv" file.<br> <br> Next, we use a variable "$sym" which contains the " symbol. Such a character is a string really, so you would<br> normally enclose it using double quotes. But just using three double quotes (""") does not work.<br> Using it this way "`"", the special single quote will sort of "hide", or "escape", the second double quote, so it ends up<br> as if the second quote is just a normal string (enclosed by double quotes).<br> <br> Next, we use the "Get-Content" CmdLet, to read in the "pre_report.csv", and put it in the $list variable.<br> Next, using the "foreach" loop, we process every record from "$list", meaning that we place double quotes<br> around every record, and store this new collection in the file "report.csv".<br> <br> <br> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.4: Some AD examples.</B><br> <font face="arial" size=2 color="black"> <hr/> <br> When working on a Win2K8 DC, you likely have the "Active Directory" module available, which means that new specialized CmdLets are<br> at your disposal, from which you can easily add, delete, list accounts, groups, members in groups, computers, list OU's, etc.. etc..<br> <br> With some preparations done, you can also use it from a Win7 workstation, or some management Server.<br> <br> Here are a few simple examples.<br> <br> <B>List all user accounts in AD with their most interesting properties (like CN, enabled/disabled etc..):</B><br> <br> <font face="courier" size=2 color="blue"> get-aduser  filter * | export-csv "listing.csv"<br> <br> <font face="arial" size=2 color="black"> <br> <B>List all user accounts in groups, with group names like FIN* (like Finance or any group else starting with "FIN":</B><br> <br> <font face="courier" size=2 color="blue"> # Users in All FIN* groups in user "Long" names (Distinguished Names)<br> # First get all groups with names like FIN*<br> $groups=get-ADGroup -Filter { name -like 'FIN*' } -Searchbase "DC=myorg,DC=org" | ForEach-Object {$_.SamAccountName}<br> <br> # Find all Members in those FIN* groups:<br> foreach ($group in $groups){<br> get-adgroupmember $group | foreach-object { $_.Name} >> FINmembersFullNames.txt<br> }<br> <br> <br> # Same script, but now Members of All FIN* groups in in Short names (SAM Names)<br> # First get all groups with names like FIN*<br> $groups=get-ADGroup -Filter { name -like 'FIN*' } -Searchbase "DC=myorg,DC=org" | ForEach-Object {$_.SamAccountName}<br> <br> # Find all Members in those FIN* groups:<br> foreach ($group in $groups){<br> get-adgroupmember $group | foreach-object { $_.SamAccountName} >> FINmembersSAMNames.txt<br> }<br> <br> <font face="arial" size=2 color="black"> <br> <br> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.5: Loop through a text file with accounts and assign file system permissions.</B><br> <font face="arial" size=2 color="black"> <hr/> <br> Suppose you have a text file with user accounts and other data. Now, using that file, you need to grant<br> permissions to some filesystem directory, using "icacls", for the user accounts that are listed in that file.<br> <br> Just suppose we need to create user home directories, and give every user full control on it's private directory,<br> as well as making the user the "owner" of it's private directory.<br> <br> <br> Suppose we have the following file, called "newaccounts.txt":<br> <br> 12335 user1 Amsterdam<br> 23769 user2 New York<br> 67244 user3 Berlin<br> <br> Then, using<br> <br> <font face="courier" size=2 color="blue"> Get-Content newaccounts.txt | %{$data=$_.split(" "); Write-Output "$($data[1])"} > acctmp.txt<br> <font face="arial" size=2 color="black"> <br> produces the file "acctmp.txt", with content like this:<br> <br> user1<br> user2<br> user3<br> <br> Using "Write-Output "$($data[1])"}", only the second column (column #1 since the array starts with "0") will be printed.<br> <br> We can make the code better, if we first test if a former "acctmp.txt" already exists, and if so, delete it first.<br> So, we could start with this block:<br> <br> <font face="courier" size=2 color="blue"> if (Test-Path c:\test\acctmp.txt)<br> {<br> Remove-Item c:\test\acctmp.txt<br> }<br> <br> <font face="arial" size=2 color="black"> So, the CmdLet "Test-Path" can be used to test if a directory or file exists. If "true" the code between { } will be executed.<br> <br> Note that the "test" is actually useless in our case, since later on we create "acctmp.txt" using single redirection ">",<br> which will always overwrite a former file (if it existed already).<br> However, in many other scripts the CmdLet "Test-Path" can be very usefull.<br> <br> Now, our full script could look like this:<br> <br> <font face="courier" size=2 color="blue"> if (Test-Path c:\test\acctmp.txt)<br> {<br> Remove-Item c:\test\acctmp.txt<br> }<br> <br> Get-Content newaccounts.txt | %{$data=$_.split(" "); Write-Output "$($data[1])"} > acctmp.txt<br> <br> $list = Get-Content c:\test\acctmp.txt<br> <br> foreach ($login in $list) <br> {<br> New-Item \\SERVER\users\$login -itemtype directory<br> <br> $acl = Get-Acl \\SERVER\users\$login<br> $permission = "YOURDOMAIN\$login","FullControl","Allow"<br> $accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule $permission<br> $acl.SetAccessRule($accessRule)<br> <br> $acl | Set-Acl \\SERVER\users\$login<br> icacls \\SERVER\users\$login /setowner "YOURDOMAIN\$login" /T<br> }<br> <br> <font face="arial" size=2 color="black"> The first few lines we have already discussed.<br> <br> Next, we start a loop using the records in "$list" which contains all accounts from "acctmp.txt".<br> <br> Using the CmdLet "New-Item" we create the users private Home directory.<br> <br> Then, we define a series of variables which essentialy are use to transfer the value "FullControl"<br> to the second last statement, where "Set-Acl" is "feeded" with "FullControl" which gets applied to <br> the user's Home dir "\\SERVER\users\$login".<br> <br> Finally, the last statement uses "icacls" to make the user the owner of it's private Home dir.<br> <br> <br> <font face="arial" size=2 color="black"> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.6: Compare txt files (.txt, .csv etc...)</B><br> <font face="arial" size=2 color="black"> <hr/> <br> There are ofcourse many ways to do that. In one approach, we might use the <B>Compare-Object</B> CmdLet.<br> Here are just a few examples (or no more than a few statements, actually):<br> <br> <font face="courier" size=2 color="blue"> PS C:\temp> Compare-Object -ReferenceObject (Get-Content .\ReportJohn.csv) -DifferenceObject (Get-Content .\ReportMary.csv)<br> <br> other similar example (could be in a script):<br> <br> $RefFile = Get-Content "C:\Temp\ReportJohn.txt"<br> $DiffFile = Get-Content "C:\Temp\ReportMary.txt"<br> Compare-Object $RefFile $DiffFile<br> <br> <font face="arial" size=2 color="black"> So, if you would have "C:\Temp\ReportJohn.txt" as having the content:<br> <br> a<br> b<br> c<br> d<br> e<br> <br> And if you would have "C:\Temp\ReportMary.txt" as having the content:<br> <br> a<br> b<br> c<br> <br> Then running the command (or script) would show you "d" and "e" as the difference.<br> <br> Note: the DOS "fc" command in order to compare (ascii or binary) files, is really very easy to use:<br> <br> <font face="courier" size=2 color="blue"> C:\TEMP> fc /L ReportJohn.txt ReportMary.txt<br> <font face="arial" size=2 color="black"> <br> Note: Use "fc /?" to get more info on the command switches.<br> <br> Indeed, in just a few cases DOS commands work even better than the equivalent Powershell.<br> But I haste me to say that PowerShell is much more "powerful" and has a much wider scope, compared to DOS.<br> <br> If you look at the last powershell example, and put those 3 lines in a ".ps1" scriptfile, then it works for the<br> ReportJohn.txt and ReportMary.txt files, But that's quite ugly. It's much better to have a script<br> that uses parameters to be supplied, so that you can re-use it for other (similar) files as well.<br> Do you know how to do that? Yes..., if you indeed have read this note, you do.<br> <br> <br> <font face="arial" size=2 color="black"> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.7: Calling a stored procedure, or starting a SQL Agent job, from Powershell. (Works, but a bit outdated).</B><br> <font face="arial" size=2 color="black"> <hr/> <br> The following shows a very simple example. You need to know which stored procedure you want to call,<br> or which SQL Server Agent job you want to start, and ofcourse which database and Instance are involved.<br> <br> Example:<br> <br> <font face="courier" size=2 color="blue"> $instancename="MySQLServer"<br> $db="MyDataBse"<br> <br> $sqlConnection = new-object System.Data.SqlClient.SqlConnection <br> $sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db <br> $sqlConnection.Open() <br> $sqlCommand = new-object System.Data.SqlClient.SqlCommand <br> $sqlCommand.CommandTimeout = 120 <br> $sqlCommand.Connection = $sqlConnection <br> $sqlCommand.CommandText= "<B>exec msdb.dbo.sp_start_job 'My SQL Agent job'</B>" <br> Write-Host "Executing the job..." <br> $result = $sqlCommand.ExecuteNonQuery() <br> $sqlConnection.Close() <br> <br> <font face="arial" size=2 color="black"> In the example below we use a call to a stored procedure from Powershell.<br> Here we call the stored procedure "<B>msdb.dbo.performancevlpi </B>, which performs some stuff,<br> but also returning a variable which contains the end status of the performed work.<br> <br> <font face="courier" size=2 color="blue"> $Logfile = "\\fileshare\dfs\jobs\albert.log"<br> $instancename="SVRSQL05"<br> $db="DataMart"<br> $myoutput=""<br> <br> Write-Host "Job is running. Please do not close this window"<br> $sqlConnection = new-object System.Data.SqlClient.SqlConnection <br> $sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db <br> $sqlConnection.Open() <br> $sqlCommand = new-object System.Data.SqlClient.SqlCommand <br> $sqlCommand.CommandTimeout = 3600 <br> $SqlCommand.CommandText = "<B>msdb.dbo.performancevlpi</B>"<br> $SqlCommand.Connection = $SqlConnection<br> $SqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure'; <br> $outParameter = new-object System.Data.SqlClient.SqlParameter;<br> $outParameter.ParameterName = "@answer";<br> $outParameter.Direction = [System.Data.ParameterDirection]'Output';<br> $outParameter.DbType = [System.Data.DbType]'String';<br> $outParameter.Size = 2500;<br> $SqlCommand.Parameters.Add($outParameter) >> $null;<br> <br> $result = $SqlCommand.ExecuteNonQuery();<br> $myoutput = $SqlCommand.Parameters["@answer"].Value;<br> $myoutput;<br> $SqlConnection.Close();<br> <br> If ($myoutput -eq 1) <br> { <br> Write-Output "The job completed succesfully" | Out-File $logfile <br> } <br> else <br> { <br> Write-Output "The job failed" | Out-File $logfile <br> } <br> <font face="arial" size=2 color="black"> <br> <br> <font face="arial" size=2 color="black"> <hr/> <font face="arial" size=2 color="brown"> <B>Example 4.8: Finding the most recent file in some folder.</B><br> <font face="arial" size=2 color="black"> <hr/> <br> Example 1:<br> <br> $dir = "c:\test"<br> $latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1<br> $latest.name<br> <br> Example 2:<br> <br> $f = Dir | Sort CreationTime -Descending | Select Name -First 1 <br> $f.name<br> <br> Example In DOS (cmd):<br> <br> Suppose we are using d:\test:<br> <br> FOR /F "delims=|" %%I IN ('DIR "d:\test\*.*" /B /O:D') DO SET NewestFile=%%I<br> echo %NewestFile%<br> <br> <br> <font face="arial" size=2 color="black"> <hr/> <font face="arial" size=2 color="brown"> <B>4.9: Rename file(s).</B><br> <font face="arial" size=2 color="black"> <hr/> <br> Example 1:<br> <br> $datetime = get-date -f MMddyy-hhmmtt<br> rename-item d:\test\daily.bak -newname ($datetime + ".bak")<br> <br> Example 2:<br> <br> Get-ChildItem -Filter  *.bak -Recurse | Rename-Item -NewName {$_.name -replace '.bak','.archive' }<br> <br> Get-ChildItem *.dmp | Rename-Item -NewName { $_.Name -replace '.dmp','.bak' }<br> <br> Example In DOS (cmd):<br> <br> ren *.doc *.archive<br> ren a.txt b.txt<br> ren f:\data\sales.xls sales_2015.xls (or better: copy f:\data\sales.xls g:\backups\sales_2015.xls) <br> <br> <br> <font face="arial" size=2 color="black"> <hr/> <font face="arial" size=2 color="brown"> <B>4.10: "Tail" or Show, the end of large txt files (like large logfiles)..</B><br> <font face="arial" size=2 color="black"> <hr/> <br> In unix/linux, it's easy to see the last lines of some txt file (like a logfile). Simply use the tail command, like:<br> <br> $ tail -50 alert.log<br> <br> which shows you the last 50 lines of that logfile. You can use any number, like 50, 120, 200 etc..<br> <br> In Powershell we have a similar commandlet. Use "Get-Content" with the "-tail" option.<br> <br> Example:<br> <br> PS C:\LOGS> get-content -tail 100 '.\web.log'<br> <br> Here, the ".\" means that the CommandLet must use the current directory.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h2>Chapter 5. Powershell and SQL Server.</h2> <font face="arial" size=2 color="black"> <font face="arial" size=2 color="blue"> <h3>5.1 Powershell versions</h3> <font face="arial" size=2 color="black"> It seems that every new Windows version, comes with a new Powershell version. Also, in between, sometimes "interfaces"<br> to applications, and the OS, may be "upgraded" too. Backward compatibility of new versions to older scripts is very high, <br> although in some cases you must indeed choose to use a newer interface.<br> <br> <br> For about Windows versions, the following crude overview holds:<br> <br> <font face="courier" size=2 color="blue"> Version............Most prominent OS, in which it was first implemented<br> PowerShell 1.0.... Windows Server 2008, and Later XP versions (XP2)<br> PowerShell 2.0.... Windows Server 2008 and R2, Win7<br> PowerShell 3.0.... Windows Server 2012, Win8, Win7 SP1<br> PowerShell 4.0.... Windows Server 2012 R2, Win7, Win8.1<br> PowerShell 5.0.... Windows Server 2016, Win10<br> PowerShell 5.1.... Windows Server 2016, Win10 latest build<br> PowerShell 6.0.... comes available Win/Linux<br> <font face="arial" size=2 color="black"> <br> If you look at figure 1 above, you see that Powershell itself runs on .Net, and has<br> a strong dependency on "WMI", that is Windows Management Instrumentation.<br> Through WMI, Powershell accesses objects of the OS and registered devices.<br> <br> For example, although PowerShell 5 and WMI 5 are not equivalent, it is true that a more modern<br> Powershell, like Powershell v5, needs a certain revision of WMI too. It means also that you can use for example, <br> Powershell v5 on Win7, but then you need updates on WMI and .Net too.<br> <br> You can check your version using:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEMP> $PSVersionTable.PSVersion<br> <br> <I>returns compact information on builds</I><br> <br> PS C:\TEMP> get-host<br> <br> <I>returns information general version and language (culture).</I><br> <br> <font face="arial" size=2 color="black"> If the variable $PSVersionTable.PSVersion is not available, you have v1/v2, and you may check that with "get-version".<br> <br> As of SQL Server 2008, "direct" interfacing from Powershell to SQL Server, was possible, using<br> the sqlps.exe "wrapper", which starts Powershell, <I>with the PowerShell snap-ins to get loaded and registered.</I><br> From that point, you could for example use the "Invoke-Sqlcmd" CmdLet.<br> <br> At later SQL Server versions, as of 2012, the "sqlps" module could be loaded, enabling a larger set<br> of CmdLet's. As of SQL 2016, this module was enhanced and renamed to "SqlServer".<br> <br> <font face="arial" size=2 color="blue"> <h3>5.2 Powershell interfaces to SQL Server.</h3> <font face="arial" size=2 color="black"> <h3>ADO.NET:</h3> Since .Net became popular, ADO.NET is a "basic database API" to access a variety of RDBM'ses, like Oracle and SQL.<br> The namespace (provider) "System.Data.SqlClient", is well known by .NET developers.<br> So, for the older SQL Server 2008 version, for example, it was quite common to develop web apps, or C#, or VB .NET apps,<br> using ADO.NET to access databases.<br> <br> ADO.NET can be used by various development platforms, and by Powershell too, like for example in the basic code below:<br> <br> <font face="courier" size=2 color="blue"> $scon = New-Object System.Data.SqlClient.SqlConnection<br> $scon.ConnectionString = "Data Source=SQL01\SQLINST1;Initial Catalog=sales;Connection Timeout=0;Integrated Security=true"<br> $cmd = New-Object System.Data.SqlClient.SqlCommand<br> $cmd.Connection = $scon<br> $cmd.CommandText = "SELECT * FROM customers"<br> $cmd.CommandTimeout = 0<br> <br> <font face="arial" size=2 color="black"> Basically, example 4.7 above, uses ADO.NET too.<br> The method can be used by modern apps and modern SQL versions too. But Powershell has evolved a bit,<br> so other methods for access to databases, using rather simple CmdLet's, is possible too.<br> <h3>The older SQLPS and newer SqlServer modules:</h3> With later SQL Server versions, using the graphical Management Studio, you can activate Powershell,<br> or actually "sqlps". <br> This module makes it possible to manage SQL Server objects from Powershell, and "traverse" the SQL Server hierarchy<br> of objects, just like a "filesystem" (using cd, ls etc..).<br> <br> From the powershell "shell" itself, you can load the module too. Then use:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEMP> Import-Module -Name sqlps -DisableNameChecking<br> <font face="arial" size=2 color="black"> <br> Apart from the feature that you can "walk" or "traverse" the SQL Server hierarchy, it also<br> makes a range of SQLserver CmdLet's available, like "Backup-Sql Database", "Restore-Sql Database" and many more.<br> <br> SQLPS uses the "SQL Server Management Object" interface, or "SMO".<br> <br> There are actually two SQL Server PowerShell modules, namely "SqlServer" (newer) and "SQLPS" (older).<br> The SQLPS module is/was included with the SQL Server installation, but is nowadays regarded<br> as "the former module". The most up-to-date PowerShell module today is the "SqlServer" module.<br> However, for the "SqlServer" installable module, Powershell v5 seems to be a requirement.<br> <br> With SQL 2016, the "SqlServer" module seems to be automatically installed with the seperate SSMS (Management Studio) installation.<br> In most cases, it seems to reside in "%Program Files\WindowsPowerShell\Modules\SqlServer".<br> <br> In that case simply give the command:<br> <br> <font face="courier" size=2 color="blue"> PS C:\TEMP> Import-Module -Name SqlServer<br> <font face="arial" size=2 color="black"> <br> <font face="arial" size=2 color="blue"> <h3>5.3 Powershell Architecture with API's .</h3> <font face="arial" size=2 color="black"> Below you see a Jip-Janneke figure, trying to illustrate how powershell might access<br> other parts of the system (like the OS, Applications, SQL Server etc..).<br> The first sub-figure, is the same as figure 1, above. The second sub-figure, tries to show<br> the different API's to connect to SQL Server.<br> <br> <B>Fig 2. Simplified architecture Powershell.</B><br> <br> <img src="powershell2.jpg" align="centre"/> <br> <br> Both figures are simply a very high-level overview. For example, the details of "WMI" are not shown.<br> <br> <font face="arial" size=2 color="blue"> <h3>5.4 SQL CmdLet's .</h3> <font face="arial" size=2 color="black"> If you have imported "sqlps" or "SqlServer" modules, as shown above, a range of SQL Server specific<br> CmdLet's are available.<br> <br> Example:<br> <br> <font face="courier" size=2 color="blue"> PS SQLSERVER:\SQL\Computer\Instance> Backup-SqlDatabase -Database "SALES"<br> <font face="arial" size=2 color="black"> <br> To see all installed CmdLet's, try:<br> <br> <font face="courier" size=2 color="blue"> PS SQLSERVER:\SQL\Computer\Instance> get-command -module SQLSERVER<br> <font face="arial" size=2 color="black"> <br> An overview of all CmdLet's from the imported "SqlServer" module, can be found in this Microsoft page:<br> <br> <a href="https://docs.microsoft.com/en-us/powershell/module/sqlserver/?view=sqlserver-ps">SQL Server PowerShell cmdlets</a><br> <br> <br> <br> <h3>That's it. Hope you liked it !</h3> <br> <br> <br> <br> <br> <br> <br> <br> <br> <br> </body> </html>