Powershell Megathread: Verb-Noun Scripting is the future

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future

«‹›94 »

Dr. Arbitrary: Mar 15, 2006; Bleak Gremlin

I'm incredibly new at IT but I want to be a badass one day and in all the IT threads people say Powershell is the way to go. I've got a VMware and a powershell book on order but until then I'd love a little feedback.

I wrote my first thingy that actually might do something useful and I have a few questions:

code:

$TIME = get-date; $TIME2 = (get-date).addhours(-24);
get-eventlog -computername 127.0.0.1 -log security -after $TIME2 -Before $TIME -message "*JohnDoe80*"

Basically it's supposed to check the specified server, in this case it's doing a loopback but I'd like to aim it at the active directory server.
It goes through the security logs and returns every message containing a specific username and gives me a big list. If I wanted to get details I could stick on a "| format-list"

My concern is that from the way I'm reading the help files, what it's really doing is grabbing the entire security log and then applying some filters. That's great on a normal computer but on a big server there could be a zillion entries and I'm really worried about crashing something important while running some idiot script. I don't want to get fired in my first month.

# ? May 1, 2013 07:19

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 06:53

Vulture Culture: Jul 14, 2003; I was never enjoying it. I only eat it for the nutrients.

Dr. Arbitrary posted:

I'm incredibly new at IT but I want to be a badass one day and in all the IT threads people say Powershell is the way to go. I've got a VMware and a powershell book on order but until then I'd love a little feedback.

I wrote my first thingy that actually might do something useful and I have a few questions:
code:
$TIME = get-date; $TIME2 = (get-date).addhours(-24);
get-eventlog -computername 127.0.0.1 -log security -after $TIME2 -Before $TIME -message "*JohnDoe80*"
Basically it's supposed to check the specified server, in this case it's doing a loopback but I'd like to aim it at the active directory server.
It goes through the security logs and returns every message containing a specific username and gives me a big list. If I wanted to get details I could stick on a "| format-list"

My concern is that from the way I'm reading the help files, what it's really doing is grabbing the entire security log and then applying some filters. That's great on a normal computer but on a big server there could be a zillion entries and I'm really worried about crashing something important while running some idiot script. I don't want to get fired in my first month.

It's always a good idea to run things past your superiors when you're the new guy. That said, performance is typically more of a concern than availability with these things - services were designed to actually be used - and what you're doing is great.

# ? May 1, 2013 12:51

Dr. Arbitrary: Mar 15, 2006; Bleak Gremlin

Jethro posted:

Technically mkdir is not an alias, but a function that calls New-Item with -Type Directory

Powershell seriously has some awesome documentation built in. I just typed
get-command mkdir | format-list
and got the source code for mkdir. Neato.

# ? May 1, 2013 16:09

chizad: Jul 9, 2001; 'Cus we find ourselves in the same old mess
Singin' drunken lullabies

MJP posted:

I'm trying to identify all AD accounts that are expired and have also never logged on, while excluding the Out of Band group (it's got all the $ime computer accounts).

Here's what I've got:
code:
get-aduser -filter {not ( lastlogontimestamp -like "*" -and (enabled -eq $false) -and (distinguishedname -not like "*out of*") }
This returns no results. If I use enabled -eq $true that also returns nothing. However, if I remove the -and (distinguishedname) part, I get a long list of users.

The only thing is that this lists disabled users, not users who have account expiration dates which have already passed. Let's say Jane Doe had an expiration date of last week - I want to find Jane Doe, not John Doe, a user who had no expiration date in AD but was disabled.

I tried looking around for an AD attribute that specifies an expired user with no luck - anyone know how I can go about that, and how I should execute the filter to exclude Out of Band?

Edit: Okay, found my answer in the Powershell.org forums:
code:
get-aduser -filter {not ( lastlogontimestamp -like "*" -and (enabled -eq $false) | where {$_.DistinguishedName -notmatch 'Out of'
I added a few of these to the cmdlet for other OUs to filter out and they're pretty effective.

Still looking for how I can only search on Expired users - I'm still getting Disabled users.

Warning: my answer is a bit of a wall-o-text, so anyone that's following along can see my logic and understand how I came up with my answer. :science:

In my (limited) experience with Get-ADUser, piping to format-list * or select-object -Property * or even Get-Member doesn't show you all the available properties like it does with the built-in cmdlets. For anything outside the default set it retrieves, you have to tack on the -Properties parameter. You can either specify certain ones or just do -Properties * to get them all. (And yet, it looks like Microsoft is ignoring their own guidelines and using a plural parameter name.

)

At least what I've done so far, the attribute names from the attribute editor tab in ADUC are the same names you use with the -Properties parameter of Get-ADUser. When you're viewing a user object, there's an accountExpires attribute. Unfortunately, it's stored in AD in the same weird format the pwdLastSet attribute is, so running the following doesn't get you much.

code:

Get-ADUser -Properties accountExpires

But, if you run Get-ADUser -Properties * | Get-Member, you'll see that PowerShell is nice enough to give us an AccountExpirationDate property that converts that ugly mess into a proper DateTime object. So something like the following should do what you want:

code:

get-aduser -Properties AccountExpirationDate -filter {not ( lastlogontimestamp -like "*" -and (enabled -eq $false) | where {($_.DistinguishedName -notmatch 'Out of' ) -and ($_.AccountExpirationDate -lt (Get-Date))

Dr. Arbitrary posted:

I'm incredibly new at IT but I want to be a badass one day and in all the IT threads people say Powershell is the way to go. I've got a VMware and a powershell book on order but until then I'd love a little feedback.

I wrote my first thingy that actually might do something useful and I have a few questions:
code:
$TIME = get-date; $TIME2 = (get-date).addhours(-24);
get-eventlog -computername 127.0.0.1 -log security -after $TIME2 -Before $TIME -message "*JohnDoe80*"
Basically it's supposed to check the specified server, in this case it's doing a loopback but I'd like to aim it at the active directory server.
It goes through the security logs and returns every message containing a specific username and gives me a big list. If I wanted to get details I could stick on a "| format-list"

My concern is that from the way I'm reading the help files, what it's really doing is grabbing the entire security log and then applying some filters. That's great on a normal computer but on a big server there could be a zillion entries and I'm really worried about crashing something important while running some idiot script. I don't want to get fired in my first month.

If running that specific command crashes anything, then they've got bigger problems.

But like Misogynist said, yeah, always a good idea to run things up the flagpole first, especially if you're just starting off. If you're really interested in learning, see if you can get a lab environment set up somewhere, even if it's just in VMWare Workstation (or Client Hyper-V if you're running Windows 8) on your machine. That way you can have a few domain controllers or whatever else servers to play around with and if you do happen to make a mistake, it's only your test environment that gets screwed up.

What PowerShell book did your order? If you've skimmed through the thread at all you'll see that Learn Windows PowerShell 3 in a Month of Lunches is the gold standard. After finally sitting down and reading it (instead of just cobbling stuff together from samples I found online), I totally get why; it does a great job of explaining things and stresses using Get-Help and Get-Command to figure out how to accomplish whatever the task at hand is. I also like the fact that the first 17 chapters focus strictly on doing things with the pipeline, so you can get a feel for that before getting into more traditional scripting/programming constructs like variables and if/then blocks and all that.

Now, a couple tips for your specific example:

- It certainly doesn't hurt anything, but there's no real reason to declare variables. You could just run the following and get the same results:

code:

get-eventlog -computername 127.0.0.1 -log security -after ((Get-Date).AddHours(-24)) -Before (Get-Date) -message "*JohnDoe80*"

- The way you're doing things is going to look at every security log entry (in the date range you specified, but it's only going to retrieve the ones that match the criteria you specified. If you had written it as something like get-eventlog -computername 127.0.0.1 -log security -after $TIME2 -Before $TIME | Where-Object {$_.Message -like "*Johndoe80*"} instead, then it would've retrieved all the security log entries in the date range and then filtered out anything that didn't contain "JohnDoe80" in the message. Running this locally, there probably isn't any (noticeable) performance difference between the two. But if you pointed it at a remote server, the way you originally wrote it would be preferred since only the records that matched would be sent back across the network to your machine.

(And in an ideal world, especially if you're going across slow WAN links or wanting results from multiple servers, you'd most likely want to use remoting and Invoke-Command. That way everything would execute on the server and only the serialized results would be sent across the network, but that's a bit advanced for someone that's just starting out.)

# ? May 1, 2013 17:32

Dr. Arbitrary: Mar 15, 2006; Bleak Gremlin

Can I get an idiots guide to installing the active directory module into Powershell?

Is it something that only works on a computer that's running as a server or can I use the commands on my desktop and target them at the AD server?

# ? May 8, 2013 07:49

Red Robin Hood: Jun 24, 2008; Buglord

I'm a Junior Systems Engineer so I tend to get stuck doing a lot of the grunt work. My superior has been telling me I really should be learning PowerShell because it will be very useful to know. I'm not really that knowledgeable writing batch files but I feel like I could figure out on my own. Writing this in PowerShell is another story, though.

This is what I do by hand:
Create a folder in a static directory: \\EXAMPLESERVER\f$\DIRECTORY\*Create folder here*
Create that folder with a name based on my input
Set permissions on that folder with modify permissions for AllUsers.Company Name

Is that difficult? I felt like I was doing well with this:

code:

$name = read-host "Enter Company Name"
md c:\Users\test\Desktop\$name

And then I looked up setting permissions on a folder and hit a brick wall.

Any help would be greatly appreciated!

Additionally, are there any books that I should buy that will get me rolling with this with very little scripting experience?

# ? May 10, 2013 17:34

FISHMANPET: Mar 3, 2007; Sweet 'N Sour
Can't
Melt
Steel Beams

From what I can tell, permissions in PowerShell are pretty awful, I had a script that modified permissions, and I just ended up calling xcacls in my script.

# ? May 10, 2013 17:38

stubblyhead: Sep 13, 2007; That is treason, Johnny!; Fun Shoe

What you want to do use are Get-ACL and Set-ACL. Take some directory that has permissions the way you want them, and do something like

code:

$myACL = Get-ACL -path <reference path>
Set-ACL -path <new directory> -AclObject $myACL

I would imagine it's possible to be more granular with this (i.e. add permission x for AD object y), but it's not something I've messed around with myself. Also, instead of mixing DOS commands with PS (though that is totally allowed) you can do this to create your new directory:

code:

$name = read-host "Enter Company Name"
New-Item  -path c:\Users\test\Desktop\ -name $name -itemtype directory

# ? May 10, 2013 19:14

Scikar: Nov 20, 2005; 5? Seriously?

Dr. Arbitrary posted:

Can I get an idiots guide to installing the active directory module into Powershell?

Is it something that only works on a computer that's running as a server or can I use the commands on my desktop and target them at the AD server?

On a server running 2008 or later just add the feature. On a desktop OS it comes with the RSAT tools. You can also install the feature on the server and then start a remote session on your desktop, so that you don't need the overhead of RDP but your commands still run locally on the server for better performance. Not that RDP overhead is particularly huge but a remote shell is more convenient if you're just running commands.

# ? May 10, 2013 20:13

turn it up TURN ME ON: Mar 19, 2012; In the Grim Darkness of the Future, there is only war.

...and delicious ice cream.

Oh gently caress you, Powershell. I spent a little while working on a script that is going to grab a set of performance counters nad shove them into a rolling continuous log file in a specified directory (specified by a variable). I thought that forcing a remote-signed execution policy would allow it to run on any given machine (it needs to be totally portable and as self-contained as possible) but NOPE. Looks like it really wants a certificate to be created.

Any ideas aside from just forcing unrestricted (which I really don't want to do, as we'll probably want to run these things on client servers eventually)? Is there any possible way around it? Could I maybe force the script to somehow re-lable itself as created by the computer it is running from?

# ? May 11, 2013 00:04

chizad: Jul 9, 2001; 'Cus we find ourselves in the same old mess
Singin' drunken lullabies

Anyone else doing the Scripting Games this time around? Between comments people are leaving on my entries, rating/commenting on other people's entries, and reading the judges' blog posts I've already picked up a ton of tips/tricks, and the third event just started a couple days ago.

# ? May 11, 2013 17:55

Mully Clown: Aug 1, 2004; I handle my piss like the great big frilly girls blouse that I am

Red Robin Hood posted:

And then I looked up setting permissions on a folder and hit a brick wall.

Any help would be greatly appreciated!

Additionally, are there any books that I should buy that will get me rolling with this with very little scripting experience?

The permissions handling leaves a lot be desired. This is where I'd leave it up to someone else to do the hard work, use the NTFSSecurity module instead. Though it is still good to get basic idea of the standard functionality.

# ? May 12, 2013 10:56

turn it up TURN ME ON: Mar 19, 2012; In the Grim Darkness of the Future, there is only war.

...and delicious ice cream.

For anyone who is interested, I neatly sidestepped my problem with a more careful reading of the help file (and some more thorough Google searching). Instead of trying to set the execution policy within the script (which was changing it for the entire system), I instead just used a switch on the powershell.exe command prompt line that forced it to use a session-based unrestricted policy. That worked! Now, I'm moving on to creating a GUI for my little tool.

# ? May 13, 2013 19:48

AreWeDrunkYet: Jul 8, 2006

I'm using the send-mailmessage cmdlet, and I'm running into a problem with multiple recipients (or CCs)

currently, the code is along the lines of

[string[]] $cc = $person1,$person2,$person3
send-mailmessage -to $recipient -from $from -cc $cc

This works if all the $person variables are populated, but errors out if any are empty. The data structure I am working with will often leave that blank (with no specific pattern, $person1 and $person3 being populated is as likely as all of them or the first two).

Is there a workaround, or some null value I could use that wouldn't throw off the send-mailmessage cmdlet?

e: Found a workaround, just dimmed a new cc array and went through each of the items in the existing cc array, adding the non-blank ones to the new array. Then I used the new array with send-mailmessage. Not the most elegant solution, but works.

AreWeDrunkYet fucked around with this message at 22:44 on May 22, 2013

# ? May 22, 2013 20:41

CLAM DOWN: Feb 13, 2007

Is there a standard/best way of throwing up dialog boxes in Powershell? One thing requested in a script I'm working is the ability to give dialog boxes and perform an action depending on which one they click, and I haven't done this before.

This is what I have so far, gleaned from a PS book and this:

code:

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$dialog = [System.Windows.Forms.MessageBox]::Show("Do you like dialogs?","Test dialog",4)

Is using the output from this as simple as going:

code:

if ($dialog -eq "Yes") { do yes stuff }
else { do no stuff }

Is this even the best way to go about utilizing dialog boxes in PS? If there is a better/more efficient way I'd definitely like to hear it.

# ? May 22, 2013 22:46

Mario: Oct 29, 2006; It's-a-me!

AreWeDrunkYet posted:

Is there a workaround, or some null value I could use that wouldn't throw off the send-mailmessage cmdlet?

e: Found a workaround, just dimmed a new cc array and went through each of the items in the existing cc array, adding the non-blank ones to the new array. Then I used the new array with send-mailmessage. Not the most elegant solution, but works.

I would just use Where-Object to filter out the blanks:

code:

send-mailmessage -to $recipient -from $from -cc ($cc | Where-Object { $_ -ne [string]::Empty })

# ? May 23, 2013 04:26

turn it up TURN ME ON: Mar 19, 2012; In the Grim Darkness of the Future, there is only war.

...and delicious ice cream.

Alright, so I've got a nice .ps1 script that gets a set of counters and writes it to a circular log file with a maximum size of 1GB. It works well, and is making everyone happy.

The way it's fired off, currently, is by a .bat file that runs through the script and then keeps an instance of Powershell.exe open in a hidden window. This allows it to run unobtrusively.

However, I'm really not liking the idea of the "Circular" log file. I'm grabbing a lot of information, and on some of these servers that means that even the 1GB limit will be getting hit fairly often. I'm running a test to see how long that actually takes, but it's foreseeable that a rolling log file might work better.

I have a function I've found to have a particular file checked for size and create a new file if necessary, but I'm not quite sure how to have that function called periodically (like, once a day or hour or something) from a Powershell script that basically is fire and forget.

To further complicate things, my experiments with start-job and stop-job in this context didn't seem to work that well. It wouldn't create the log files at all.

Code:

code:

# This script tracks performance counters useful for tracking performance on a SQL server in a rolling .csv file 
located at a directory of your choosing. It is written for Powershell v.2

$Folder="C:\Perflogs\BBCRMLogs" # Change the bit in the quotation marks to whatever directory you want the log file 
# stored in

$Computer = $env:COMPUTERNAME
$1GBInBytes = 1GB
$p = LOTS OF COUNTERS GO HERE;
     
# If you want to change the performance counters, change the above list. However, these are the recommended counters for 
a client machine. 

$dir = test-path $Folder 

IF($dir -eq $False) 
{
New-Item $Folder -type directory
get-counter -counter $p -SampleInterval 60 -Continuous | Export-Counter  $Folder\SQL_log.csv -Force -FileFormat CSV 
-Circular -MaxSize $1GBInBytes
}
Else
{
get-counter -counter $p -SampleInterval 60 -Continuous | Export-Counter  $Folder\SQL_log.csv -Force -FileFormat CSV 
-Circular -MaxSize $1GBInBytes
}

Keen eyes will notice the function for rolling is missing. This is because the above script is my stable version, the function I'm using is here:

http://sysbrief.blogspot.com/2011/05/powershell-log-rotation-function.html

Any ideas?

EDIT: Line breaks added in the code to keep the tables from breaking.

turn it up TURN ME ON fucked around with this message at 14:17 on May 28, 2013

# ? May 28, 2013 14:14

Red Robin Hood: Jun 24, 2008; Buglord

Has anyone gone through the CBT Nuggest Powershell videos? I just finished the first video and it seems like it is going to be a good learning experience but I wanted to get some more input.

# ? Jun 3, 2013 16:48

Alleric: Dec 10, 2002; Rambly Bastard...

My apologies if I flat-out missed information earlier in the thread that can speak to this. I searched, but did not find.

This question is SQL-related, but is different from the conversations I've seen in here accessing SQL from powershell.

Here's the deal.

1. We have two known-good power-shell scripts. One is a simple file copy. One is a simple file extraction from a zip file.
2. The execution policy on all scopes on the host in question is Unrestricted.
3. The SQL service account and SQL Agent service accounts are administrators on the host.
4. All NTFS perms on all associated drives have been explicitly defined to mod rights. Rights assignment has been tracked down to the individual files in question.
5. Scripts have been run from interactive desktop using the service account SQL is running under and the SQL agent is running under.

So we have known good scripts that do what they do. I can run them all day from a powershell, the service account can run them all day from a powershell. All good there.

Throw them into a SQL Agent job step and run them as a powershell execution? Job step runs successfully, no errors, nothing in the output file we've configured other than debug echos and... it does nothing. All variable generation works, values of the variables can be echo'd, but the one line of the script where the move is executed from the zip or directory... and nothing happens. I can set up a separate credential, configure it as a proxy for powershell execution, the step executes as that other, also-known-good-no-rights-issues-account and it does the exact same thing.

Google has been painfully unfruitful on this. The signal to noise ratio of "SET YOUR GODDAMN EXECUTION PLAN" is... bad.

This is freshy fresh install of Server 2012 Standard with SQL 2012 Enterprise. On top of that, it's a brand new vanilla domain with one GPO applying to the host (to set all the execution policy scopes to Unrestricted).

The only thing that makes me raise a brow a bit is that I know SQL Agent powershell executions runs through a separate dll file that SQL uses as a powershell subsystem. Might it be doing something bizzare?

Any help is greatly appreciated.

# ? Jun 13, 2013 02:22

turn it up TURN ME ON: Mar 19, 2012; In the Grim Darkness of the Future, there is only war.

...and delicious ice cream.

Can you see any access errors in the event viewer logs? Sometimes SQL will put stuff there and it may help.

# ? Jun 13, 2013 02:50

Alleric: Dec 10, 2002; Rambly Bastard...

SquadronROE posted:

Can you see any access errors in the event viewer logs? Sometimes SQL will put stuff there and it may help.

No errors were logged into the system log, app log, and no audit failures logged to the security log. Nothing was logged to the internal sql logs other than job execution trappings. Nothing out of the ordinary in msdb records concerning job history or anything else. It seriously just runs, succeeds, and does nothing. It's one of the damnedest things I've seen.

I know I read some time ago on RTM of SQL 2012 that people were having some oddities with powershell execution on the new platform, but that mostly was surrounding having to hack the registry to get the process scope execution policy set correctly for the SQL service and Agent service (otherwise job steps that called powershell would insert a "set-policy -scope process -executionpolicy AllSigned -Force" on the front of all of your powershell job steps, which meant any un-keyed, un-signed, arbitrary scripts you were using for utility work would immediately fail). I did all that and verified it under SQL's powershell scope (right click the instance in SSMS and open powershell, do the routine "get-executionpolicy -list"). So it's not tripping up on the process scope. That's all fixed. Nothing's erroring. The job step, and the job, all succeed.

# ? Jun 13, 2013 03:03

Alleric: Dec 10, 2002; Rambly Bastard...

So this was still bugging me and I took some time to build a test process to do this same thing on one of our Server 2008 R2 with SQL 2008 R2 servers in our quality control environment. Same issue. Exactly the same. No errors, no nothing. Known good scripts, known good accounts, known good rights assignments.

Here's the script. It's very simple:

$filelocation = dir N:\path\directories\*.zip
foreach ($file in $filelocation{
$filename = $file.name.Tostring()
$shell_app = new-object -com shell.application
$zip_file = $shell_app.namespace("N:\path\directories\from\$filename")
$destination = $shell_app.namespace(N:\path\directories\to\")
$destination.Copyhere($zip_file.items(), 0x14)}

I load that up as a powershell job step, runs without issue and does nothing. Run it under a normal powershell as me or the service account, runs like a champ.

# ? Jun 13, 2013 04:50

New Yorp New Yorp: Jul 18, 2003; Only in Kenya.; Pillbug

Alleric posted:

So this was still bugging me and I took some time to build a test process to do this same thing on one of our Server 2008 R2 with SQL 2008 R2 servers in our quality control environment. Same issue. Exactly the same. No errors, no nothing. Known good scripts, known good accounts, known good rights assignments.

Here's the script. It's very simple:

$filelocation = dir N:\path\directories\*.zip
foreach ($file in $filelocation{
$filename = $file.name.Tostring()
$shell_app = new-object -com shell.application
$zip_file = $shell_app.namespace("N:\path\directories\from\$filename")
$destination = $shell_app.namespace(N:\path\directories\to\")
$destination.Copyhere($zip_file.items(), 0x14)}

I load that up as a powershell job step, runs without issue and does nothing. Run it under a normal powershell as me or the service account, runs like a champ.

Is it a big zip file? It's probably running out of memory. I had the exact same issue with zipping files. You need to increase the memory for a powershell session with something like set-item wsman:localhost\Shell\MaxMemoryPerShellMB 2048.

# ? Jun 13, 2013 04:52

Alleric: Dec 10, 2002; Rambly Bastard...

Ithaqua posted:

Is it a big zip file? It's probably running out of memory. I had the exact same issue with zipping files. You need to increase the memory for a powershell session with something like set-item wsman:localhost\Shell\MaxMemoryPerShellMB 2048.

No, the test file is measurable in k. This is not any of the standard issues, and this is why I'm going nuts. All of the mundane, normal troubleshooting fodder is coming up dry.

However, I think I've had an epiphany.

As I said previously, the fact that SQL runs all of its powershell commands through its own internal, dll-based parsing subsystem as opposed to the powershell you or I might use... that got me thinking. The only things I can verify that are not actually doing anything are things outside variable declarations. So what if powershell in side of SQL using the SQLps provider... can't actually do these things? What if their scope is limited to just... well... SQL?

And lo:

https://www.simple-talk.com/sql/database-administration/practical-powershell-for-sql-server-developers-and-dbas-%e2%80%93-part-1/

and part two:

https://www.simple-talk.com/sql/database-administration/practical-powershell-for-sql-server-developers-and-dbas-%E2%80%93-part-2/

So you can list directory contents, even remove a file, but I see nothing in there about copying, access to windows folder compression, etc...

I guess it's back to CMDEXEC.

# ? Jun 13, 2013 05:15

turn it up TURN ME ON: Mar 19, 2012; In the Grim Darkness of the Future, there is only war.

...and delicious ice cream.

Alleric posted:

No, the test file is measurable in k. This is not any of the standard issues, and this is why I'm going nuts. All of the mundane, normal troubleshooting fodder is coming up dry.

However, I think I've had an epiphany.

As I said previously, the fact that SQL runs all of its powershell commands through its own internal, dll-based parsing subsystem as opposed to the powershell you or I might use... that got me thinking. The only things I can verify that are not actually doing anything are things outside variable declarations. So what if powershell in side of SQL using the SQLps provider... can't actually do these things? What if their scope is limited to just... well... SQL?

And lo:

https://www.simple-talk.com/sql/database-administration/practical-powershell-for-sql-server-developers-and-dbas-%e2%80%93-part-1/

and part two:

https://www.simple-talk.com/sql/database-administration/practical-powershell-for-sql-server-developers-and-dbas-%E2%80%93-part-2/

So you can list directory contents, even remove a file, but I see nothing in there about copying, access to windows folder compression, etc...

I guess it's back to CMDEXEC.

Yeah, based on what you're saying it really does sound like it's something specific to how the SQL Agent is executing the Powershell stuff. CMDEXEC should get you up and running at least.

Honestly, if I were you I'd try to engage Microsoft in some meaningful way at this point. There is obviously a peculiarity within SQLps that you or I weren't aware of, and if you're running SQL Enterprise 2012 I'd assume you've got some sort of support contract with them. Try to get them to do a little of the hard work here.

# ? Jun 13, 2013 14:45

RICHUNCLEPENNYBAGS: Dec 21, 2010

I love PowerShell. It took me a while to wrap my head around it (as opposed to a more traditional scripting language), but now that I have I think it's about my favorite scripting language. It's also really nice to have an interactive .NET environment when you're writing C# or something and want to test something out.

That said, a lot of PowerShell stuff talks about how great it is because you can start out with a simple script and expand it into a full-blown thing without having to switch languages. Where are all these big PowerShell projects? I'm curious to see something that goes beyond a single script into being a real, full-scale PowerShell project, if they're out there.

# ? Jul 2, 2013 04:20

Vulture Culture: Jul 14, 2003; I was never enjoying it. I only eat it for the nutrients.

RICHUNCLEPENNYBAGS posted:

I love PowerShell. It took me a while to wrap my head around it (as opposed to a more traditional scripting language), but now that I have I think it's about my favorite scripting language. It's also really nice to have an interactive .NET environment when you're writing C# or something and want to test something out.

That said, a lot of PowerShell stuff talks about how great it is because you can start out with a simple script and expand it into a full-blown thing without having to switch languages. Where are all these big PowerShell projects? I'm curious to see something that goes beyond a single script into being a real, full-scale PowerShell project, if they're out there.

http://chocolatey.org/

# ? Jul 2, 2013 13:28

capitalcomma: Sep 9, 2001; A grim bloody fable, with an unhappy bloody end.

Has anybody had any luck putting outputting variables into e-mail bodies? I was hoping it was as easy as:

code:

Send-MailMessage -Body "blah blah $variable blah blah"

But it just outputs "blah blah blah blah".

I'm trying to generate a table and print it out in an e-mail. I've got a workaround--outputting the variable to a text or CSV file and attaching it to the e-mail--but some of these reports should be right in the body of the message, if possible.

capitalcomma fucked around with this message at 21:04 on Jul 3, 2013

# ? Jul 3, 2013 21:01

AreWeDrunkYet: Jul 8, 2006

Sounder posted:

Has anybody had any luck putting outputting variables into e-mail bodies? I was hoping it was as easy as:

I figured the same thing initially and various articles seem to back that up, but eventually had to do this to make it work (although I was using array elements at the time). Try:

code:

Send-MailMessage -Body "blah blah $($variable) blah blah"

edit: Actually, it sounds like your variables may not have a value set. Try this as a check.

code:

write-host $variable
Send-MailMessage -Body "blah blah $variable blah blah"

AreWeDrunkYet fucked around with this message at 21:22 on Jul 3, 2013

# ? Jul 3, 2013 21:16

capitalcomma: Sep 9, 2001; A grim bloody fable, with an unhappy bloody end.

AreWeDrunkYet posted:

I figured the same thing initially and various articles seem to back that up, but eventually had to do this to make it work (although I was using array elements at the time). Try:
code:
Send-MailMessage -Body "blah blah $($variable) blah blah"
edit: Actually, it sounds like your variables may not have a value set. Try this as a check.
code:
write-host $variable
Send-MailMessage -Body "blah blah $variable blah blah"

Nope, the variable's set. Write-host outputs all of the objects I've got stored in it.

Unfortunately $($variable) didn't work either.

capitalcomma fucked around with this message at 21:39 on Jul 3, 2013

# ? Jul 3, 2013 21:25

capitalcomma: Sep 9, 2001; A grim bloody fable, with an unhappy bloody end.

Well poo poo, I think I've narrowed down the problem a bit. Here's the original code for gathering the variable's data:

code:

$variable = Search-AdAccount -AccountInactive -DateTime ((Get-Date).AddMonths(-3)) -ComputersOnly | sort Name | Select-Object Name,LastLogonDate

When I get rid of the last pipe it works just fine. Select-Object is doing something to the output that breaks it.

# ? Jul 3, 2013 21:42

Dobermaniac: Jun 10, 2004

Sounder posted:

Has anybody had any luck putting outputting variables into e-mail bodies? I was hoping it was as easy as:
code:
Send-MailMessage -Body "blah blah $variable blah blah"
But it just outputs "blah blah blah blah".

I'm trying to generate a table and print it out in an e-mail. I've got a workaround--outputting the variable to a text or CSV file and attaching it to the e-mail--but some of these reports should be right in the body of the message, if possible.

Try this:

code:


$emailMessage.Body = @"
        
"@

I use this in a ton of scripts and just toss the variables in.

This is the full function I use:

code:

Function mail ($EmailIn){
#email start
$emailSmtpServer = ""
$emailSmtpServerPort = ""
$emailSmtpUser = ""
$emailSmtpPass = ""

$emailFrom = ""
$emailTo = ""
 
$emailMessage = New-Object System.Net.Mail.MailMessage( $emailFrom , $emailTo )
$emailMessage.Subject = " "
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = @"
        $EmailIn
"@
 
$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( $emailSmtpUser , $emailSmtpPass )
$SMTPClient.Send( $emailMessage )
#Email End
}

Dobermaniac fucked around with this message at 00:47 on Jul 4, 2013

# ? Jul 4, 2013 00:45

capitalcomma: Sep 9, 2001; A grim bloody fable, with an unhappy bloody end.

Dobermaniac posted:

Try this:

snip

I'll give it a shot, thanks!

capitalcomma fucked around with this message at 19:40 on Jul 5, 2013

# ? Jul 5, 2013 19:34

Dobermaniac: Jun 10, 2004

I have a little parsing problem with PS. I want to query using Get-WinEvent a specific computer and parse the result to pull an IP (4 or 6) to determine what hostname is using RDC to connect to a computer. I'm having a bit of trouble parsing the IP out of the event log.
Here is my current script:

code:

clear
$events=$null
$RemoteComputer = Read-Host 'What computer are you trying to connect to?'
$User = Read-Host 'What user do you want to connect with?'
$events = Get-WinEvent -Computername $RemoteComputer -Credential $User -logname Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational -MaxEvents 50| Where-Object {$_.Id -eq '1149'}
foreach($event in $events){
    $event.TimeCreated
    $event.Message
    #[System.Net.Dns]::gethostentry(IP parsed from $event.Message).Hostname
    }

I have everything up to pulling the message from the remote computer working. I just can't seem to figure out how to get the IP address out of the $event.Message string.
Here is the output of $event.Message:

code:

Remote Desktop Services: User authentication succeeded:

User: 
Domain: 
Source Network Address: fe80::417f:ff76:5c2f:b729%11

Anyone a parse/trim master? I've tried everything from $_.trim().$trimStart(xxx)

# ? Jul 7, 2013 17:00

brosmike: Jun 26, 2009

The best way to do what you're looking for doesn't actually involve string parsing at all. All you need is $Event.Properties[2].

# ? Jul 7, 2013 19:12

Dobermaniac: Jun 10, 2004

brosmike posted:

The best way to do what you're looking for doesn't actually involve string parsing at all. All you need is $Event.Properties[2].

Thank you good sir. Worked perfectly.
For anyone who wants the finished script:

code:

 
clear
$events=$null
$RemoteComputer = Read-Host 'What computer are you trying to connect to?'
$User = Read-Host 'What user do you want to connect with?'
    Try{
    $events = Get-WinEvent -Computername $RemoteComputer -Credential $User -logname `
Microsoft-Windows-TerminalServices-RemoteConnectionManager/Operational -MaxEvents 50 | Where-Object {$_.Id -eq '1149'}
    foreach($event in $events){
        $event.TimeCreated
        Try{
            [System.Net.Dns]::gethostentry($event.Properties[2].Value).Hostname
        }Catch{"Host unkown"}
        }
    }Catch{"Unable to connect to remote computer"}

This is for Win2008R2. Had to enable RPC firewall rule to get it to work remotely.

# ? Jul 7, 2013 20:56

Swink: Apr 18, 2006; Left Side <--- Many Whelps

Red Robin Hood posted:

Has anyone gone through the CBT Nuggest Powershell videos? I just finished the first video and it seems like it is going to be a good learning experience but I wanted to get some more input.

This video from Don Jones taught me everything and its free - https://www.youtube.com/watch?v=-Ya1dQ1Igkc

# ? Jul 8, 2013 03:57

PuTTY riot: Nov 16, 2002

I'm writing baby's first powershell script which will shortly be turned into a function. All it's doing is taking a list of file names, querying SQL to get the entire path and copying it to a folder. Where I'm stuck is how I want to pass the list of files. Eventually I want to pull them directly from Jira, but I want to make a separate function to do the Jira stuff. The list of files could be anywhere from 1-2 to a couple hundred. They need to be separated by commas for SQL to not freak out. Would a CSV be the best way to do this, or something else? I'm assuming a string with 50-100 long file paths wouldn't work so well. I guess I could make the function take a string or CSV, but I'm not sure how I want to proceed.

Edit: ^^^thanks for posting that video, I watched it the other day and its a great crash course and convinced me to write this script first before I tackle the 'drainstop and reboot an NLB cluster one node at a time' script.

PuTTY riot fucked around with this message at 19:08 on Jul 12, 2013

# ? Jul 12, 2013 19:06

Dr. Arbitrary: Mar 15, 2006; Bleak Gremlin

I wrote something that might actually be useful some day. I've been learning about permissions and ACL and the hypothetical situation occurred to me.

Suppose someone needed to move some shared folders that had some very careful permissions set up.

Suppose that instead of using Xcopy or some other tool they just copy pasted and all permissions were lost. Also, it was a few weeks ago so you can't just delete and try again. I can picture myself screwing up in this way so I wrote this:

code:


Get-ChildItem -Path "C:\Destination01" -Recurse | ForEach-Object {
$Sourcepath = $_.FullName.Replace("Destination01","Source01");
Set-Acl -Path $_.FullName (Get-Acl -path $Sourcepath) 
}

From my phone, so I apologize if I messed up the format a little.

Am I reinventing the wheel here or is this potentially useful? I'm not sure how to handle missing files though, I'm going to try to see if I can get it to spit out a report at the end that lists every file that needs to be manually configured.

# ? Jul 12, 2013 21:26

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 06:53

Drumstick: Jun 20, 2006; Lord of cacti

Is there a simple way to change user passwords? I have a csv of the usernames, and the new passwords but Im not sure how to set the new passwords. I have about 11,500 passwords that need changed...

# ? Jul 15, 2013 20:31

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future

«‹›94 »