Tag Archives: powershell

UPDATED: PowerShell – WE HAZ YUR ACTIVE DIRECTORY RIGHT HERE!

I discovered something last week – I had not blogged about little things that I thought I had blogged about. What the heck does that mean? It means that I tried to reference my blog for something because I thought “I totally blogged about that”…and found out that was not the case.

Starting now, I am fixing this situation. There was something that popped up today that called for a PowerShell script and the Get-ADGroupMember cmdlet – get a list of users from a list of groups. Some users are in there more than once so this needs to be a distinct list, unless you are into manually cleaning up things like this, and then I will be sad for you. Because that is kinda sad.

I originally wrote a script with two arrays (one for the initial list and one for the de-duped list of users), but even though this is quick and dirty, that was a little too dirty. Enter the Group-Object cmdlet – it takes this list of names and groups them. No black magic this time. Just a cmdlet, that comes baked into PowerShell giving me what I need.

What? You wanted the code too? Oh, OK.

There you have it – quick, dirty and to the point. Enjoy. ๐Ÿ™‚

UPDATE:ย Mathias Jessen tweeted a one liner for this….so no need for the one array! Woohoo!

I was trying to do this but was also just trying to get it done, and if in doubt, I slap things in arrays. Thanks Mathias!

 

Wanna Get the Kids Interested in Coding? Make it a Game. Literally!

Former co-worker and friend Daniel Janik is trying do something that I will eventually have to do – get a teenager interested in something that can lead to a career.

Iโ€™ve been teaching my 17 yr old SQL Server and other various topics. I have quite a bit of experience training technology and speaking but it never fails; after about 5 minutes his eyes glaze over and he nods and โ€œumhumsโ€ and then starts checking his phone.

I have one nephew that is already intrigued by the PowerShell that I am doing and seems to have a genuine desire to write code. This makes me SOOOOOOOO HAPPY! My hope is that he is able to continue what he is learning now and migrate into a skill set that will afford him a good career. And if he happens to maybe “hack” into something that he shouldn’t have, I will help with his bail, smother him with hugs and kisses and tell him “OMG…I AM SO PROUD OF YOU!!!”

Check out Daniel’s blog post, download the game and give it a whirl. I plan on showing my nephews this to get the opinions of the younger, not-a-teenager set. I doubt my niece will be interested in this right now…unless there was a Disney princess in there with the sea monsters. I will leave it to Daniel to incorporate that in the 2.0 version.ย ๐Ÿ™‚

 

Speaking – SQL Saturday Baton Rouge!

This Saturday will mark the fourth year that I will be speaking at SQL Saturday Baton Rouge at LSU.ย Out of all the SQL Saturday events I have participated in since 2014, Baton Rouge is one of the few that I have been to every year since. Houston is another one…and actually, these may be the only ones only because they have had an event every year.

Not only am I speaking on Automation with PowerShell and Deadlocks and Blocking, but I am also participating in a panel discussion on Careers in IT. I’m excited to be invited along with some of the other speakers to be a part of this. Looking back on my own school days, I knew I would have a career in IT, but little did I know I would detour from a path in development to the world of SQL Server, and becoming a DBA.

How do they make this happen? Work. Lots of hard work. After helping with SQL Saturday Dallas 2015, joining the NTSSUG board in 2016, and then having an organizing role in 2016, I found out how much goes into the planning of these events. If you have attended a SQL Saturday or you are going to in the future, be sure to say “THANK YOU!” to all the organizers and sponsors. If you want to get more involved in the SQL Server community, SQL Saturday is a great way to do that – just show up at the event, find an organizer and tell them that the SQL Kitten sent you to be their humble servant volunteer for the day…or you could just say you want to volunteer and leave out the other stuff because it might make it weird.

Blogging reboot and PowerShell – Get System Information

After whining about not blogging enough, I am going to do something about this. Whether it is PowerShell or SQL, simple or complex, I know that others can benefit from my knowledge and expand their skill sets.

And you are like “That’s great Amy…where’s the PowerShell we are here for?” Ok, Ok….keep your drawers on! ๐Ÿ˜‰

Recently I was tasked with gathering the system information from all of the servers at a client. Another opportunity for some PowerShell dominance.

With this script you can generate system information files and save them to a specified location. It makes sure a connection can be made to the server first, and then outputs the file. The files are created one at a time, so if you pass in a longer list of servers, you shouldn’t crash your machine. From my testing, this will take some time to run as these files don’t output quickly. Despite that, the output is worth it. This can be modified to pull your list of servers from a file or from a Central Management Server (CMS) instance.

Hope you find this useful. Happy PowerShellin’ ๐Ÿ™‚

Code Upload – Pragmatic Works webinar

For those that attended my webinar last week with Pragmatic Works I have uploaded the code…finally. For what it is worth, the weekend was busy and I was working on my SQL PASS Summit submissions. Then, just as I was getting this together on Monday night, I was not feeling right and realized I was making mistakes and that is no good. Tried to go to work on Tuesday but even they knew something was wrong with me. Went home and found out later my nephew was also sick. No need for any further details. Just trying to get better. Thanks for your patience. ๐Ÿ™‚

 

The lovely people at Pragmatic Works sent me some of your questions from the webinar. Without further adieu…

Do you have a suggestion for BEGINNERS learning books to get a basic understanding of PS??

Great question! While I have a TON of books on all kinds of things (SQL Server, C#, web dev stuff, etc.) I do not have a single book on Powershell. How did I accomplish all this then? Simple – using this pesky thing we call the internet. I have also attended some great Powershell sessions at SQL Saturday events.

Did they leave any beer bottles?

The roofers? Thankfully no.

Do not criticize the king of soda pop, Dr. Pepper!

Preach it!

When will VBScript stop functioning for SQL Server or Windows Server. As in will there be a total switch?

VBScript? Hmmmm…great question. No clue. This might be a Microsoft cockroach. Like replication.

why we need a PS-Drive – why we can just use an arraay directly?

You mean passing in the server name from the array to the Invoke-SqlCmd? Yeah, you could do that. That’s just not how I wrote this. ๐Ÿ™‚

what is her email again?

Leave a comment here if you would like to email me and I will get back to you.

does the ( $servers | Measure-Object).count syntax do?

That counts the number of items in the array.

What is the purpose of the New-PSDrive?

New-PSDrive is a method of connecting to a SQL Server instance. Another method would be to declare a connection with SMO (Server Management Objects).

Does it work on windows 2003 as well?

I have not tested this against Windows Server 2003.

so, ps-drive is the equivalent of object explorer in SSMS?

Well, kinda. Maybe. More the equivalent of browsing a drive in a cmd.exe window.

what city in Austin?

Ummmm….Austin is a city.

What permission you require on AD for this excercise

To make the AD changes, I have no clue. Not my area and I also intentionally created the Azure VM so I could make all the changes I needed to without worrying about a silly thing like permissions. As far as reading from AD (which is the main Powershell script for this part) I am not sure about this either but I am assuming you need some level of read permissions (which is all that script is doing).

There are some things where I may tend to run something, and if anyone says anything, ask forgiveness later. Security is pretty tight where I am – I have gotten dinged on xp_cmdshell but so far nothing on reading from AD. If for some reason you can’t run anything against AD, I would find the person over that and find out why, and let them know what you are trying to do.

Thanks again to everyone who attended. If you have any other questions leave a comment. ๐Ÿ™‚

3/12 – Webinar : Beginning Automation with Powershell

Anything I can do to reduce the time I have to spend dealing with otherwise time-sucking tasks gives me a happy. Currently, where I am, I have reduced our daily deployment process to a single Powershell cmdlet with a log file review. All the steps that normally went into each deployment (applying SQL scripts, data updates, updating the TFS task, sending an email that the deployment is done) are handled by Powershell, and the process accepts multiple deployments at the same time. As long as there are no errors in the log file for a deployment, there is nothing more to do. There’s an error? Run the same cmdlet with the rollback option.

I am still working on improving this process, from trying to get the error handling to work the way I want it to, to incorporating the automation of check-in to source control of the changes that were just applied. It is a work-in-progress that will continue to reduce the amount of time spent on what would otherwise be manual tasks.

This work led me to the idea for a session on Powershell and automation of processes. Join me and Pragmatic Works on Thursday, March 12th from the comfort of your own desk at 11am EST/10am CST for Beginning Automation with Powershell. This demo-heavy session will go over some different Powershell cmdlets and automation concepts that you can take and build upon in your own environment. Powershell is a tool that no DBA should work without. A development background is helpful but not necessary – what DBA would not want to spend a few hours writing code for a process that will save them from having to do a repetitive manual task over and over again? Automation frees up time for us to do other projects that we want to spend time on and grow our skill sets.

Have questions about this? Leave a comment below. See y’all there. ๐Ÿ™‚

SQL Saturday #309 – Oklahoma City

It is my second time at the SQL Saturday in OKC and my first time speaking at it. For the people who attended my session on Beginning Automation with Powershell, thanks again for coming. I hope everyone enjoyed the session as much as I enjoyed giving it.

As promised, the code is available for download below. If you have any questions, leave a comment here or reach out to me on twitter.

 

SQLSat#324 – Session Noms

Thanks to everyone who attended my sessions at SQL Satruday #324 in Baton Rouge. This was really a awesome event – thanks also to all the people that helped put it on and all the sponsors who helped make it happen.

I had a great time presenting and I think my demos went ok (for the first time ever doing demos). I also really appreciate the feedback since I want to keep on presenting. If you were in one of the sessions and have any further comments or questions feel free to leave a comment here and I will get back to you.

Also, I have gotten the scripts posted here and the download appears to be working now. Let me know if you have any questions about those. Enter the password below to access and download the scripts from Beginning Automation with Powershell.

Thanks again and see everyone next year!

SQL Saturday #324 – I’m headed South!

It is just a few more days until SQL Saturday #324 – Baton Rouge. I will be giving two sessions there – Making the Leap from Developer to DBA and Beginning Automation with Powershell. So looking forward to this event!

The last time I was in southern Louisiana I was barely in my 20’s and on a family vacation. I found that my normal routine for straightening my natural curls was no match for theย level of humidity in New Orleans. This memory prompted me to check the weather for this weekend – yeah…this could get interesting.

See everyone there! ๐Ÿ™‚

Kill Oracle Sessions With PoSH

Recently I have been tasked with taking over the database related tasks for an Oracle database that is the backend for a third party application. Two of the monthly processes require the all the user sessions connecting to the application be killed before hand. Straight forward enough…there are just a lot of sessions.

Last time I did this I had forgotten that I had this task and on the day of I was not prepared with any type of automation. As a result, I was killing off connections by hand. Yes, there was sadness with each arrow up and the changing of the SID and SERIAL# values.

Moving on, I knew I wanted to automate this with powershell so I did some searching and came across thisย article on querying Oracle with PS. I walked each of the segments, modifying the code for our environment and for my needs. Everything was going great…until I decided to run the code in production. There was more sadness –

Once I was done cursing and swearing I started to research the error. Our pre-prod and production environments should be the same. After some digging I found some small differences in the listener.ora files and thought this might be the clue I was looking for.

Cut to Monday moring – a quick chat with our Oracle person (read: the guy on our team that has Oracle training…training that I do not have) brings me my solution – something far more simple than I had imagined. Since the production server was set up with a different port than the one Oracle defaults to all I needed to do was include that with the server reference in my connection string. Duh!

I began testing in the UAT environment and uncovered one small issue – a semi-colon in my kill statement that was not needed and causing my code to fail. Once that was removed everything looked good with the exception of getting error messages when trying to kill a session related to a system process (there weren’t that many sessions to begin with here, not to mention, it didn’t matter if I broke anything on this server).

Feedback is welcome. Hoping you find this helpful. ๐Ÿ™‚