Category Archives: Powershell

(UPDATED) PASS Summit 2017 Session Data (You know you want it…so go get it!)

UPDATE #1 –
The following changes were made to the PowerShell script for the PASS Summit 2017 session scrape:

  • Abstract field is now varchar(max). ‘Cause precons get all the chars.
  • Added SubmittedSession column.
  • Upper bound increased to include precons.

UPDATE #2 –
Before I could get update #1 posted, it appeared all the general sessions and lightning talks were removed from the submissions page and replaced with submitted precons. Don’t fret – the PowerShell script will still grab all the submitted sessions (for now). There were also some more precons added so the upper bound has been changed yet again.

 

The deadline for PASS Summit submissions came and went. Either you got yours in or you didn’t. What does this mean? Time to gather the data.

Go get it! You know you want to!

Never having done a web scrape before, this was the perfect subject for my first time – grabbing all the sessions submitted to PASS Summit 2017…and doing it with PowerShell! Here is the script I used for this. I have accounted for the following:

  • Apostrophes (aka single quote). They will break your insert unless you have two of them, and for some reason, people seem to use them all over the place.
  • Formatting the string data for insert. No, your data will not magically come out right in your insert with single quotes so you need to add them.
  • Additional ID and deleted fields.
  • Speaker URL and ID. Will be using this to scrape speaker details later.
  • Accurate lower and upper bounds. These were arrived at by trial and error (you’re welcome), as well as the clean up of the data I scraped. More on this later.

Now for the code –

 

There will also be some webids that error out – this means the session doesn’t exist for that specified webid. You will see an error in the output to the PowerShell window, but the script will keep running.

Once you run the script and it completes, you will have your table of session data. You will notice that there are more sessions there than have been submitted, according to the website. If you do a count of the sessions by speaker you will also find that there are more than three sessions (the maximum allowed) for quite a few speakers. Why is this? You are pulling from a data source that contains sessions other than the ones that were submitted to Summit (apparently). This means you will have to clean up the data – I included the deleted field for this purpose.ย Look at the duration and filter for the 75 minute and 10 minute sessions and update your deleted field accordingly.

After this, you will have sessions that meet the duration, but you will find the speaker is not listed on the submission site when doing an advanced search. Clean the data some more.

Are you done cleansing your data yet? You might think so, but you aren’t. You see sessions that look like they were submitted for Summit but are not on the site. These appear to be sessions that were created and saved, but not officially submitted. You can rule out quite a few of these if you look for speakers that have more than three sessions. Get those counts, check the website for those speakers, and clean your data.

“Good lord, am I done yet?”

No. No you’re not.

There will be speakers that have three sessions in your table but only submitted one or two. This sounds worse than it is, but it is a little painful. How do you do this? You’re going to search the speakers with the advanced search on the website. Do these in groups based on the number of sessions a speaker has, ordering by speaker name. You can exclude the the speakers you have already reviewed in the previous clean-up steps, deleting their names from your list as you go. When you find a speaker with three sessions in your list and fewer sessions submitted, mark the session that was not submitted as deleted. Next, do the speakers with two sessions as they may have only submitted one. Finally, look at the speakers with one session as there may be ones that didn’t officially submit anything.

“But what about the code for the clean-up?”

No. You know how to do this, and may write it differently (and better) than me. My code was ugly and I don’t feel like cleaning it up and posting it here. I gave you the script to grab it all. Stop complaining or I will send you to time-out.

The speaker info grab will be a separate blog post. With the script in this post you might be able to use the syntax and craft your own PowerShell script for this. Or you could wait for my blog on it.

Yeah…I haz the datas!

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. ๐Ÿ™‚

Multidimensional Array from Array Data in Parameter

While I am not new to the world of coding (and not just T-SQL) I am somewhat new to Powershell. I cobbled together a monitor process that works with GoldenGate a while back and just recently made it less ugly and improved its performance. Beyond that, I am still on the green side.

Enter my current project – automating the SQL portion of our deployments using Powershell. Before this I was taking the list of items to be deployed and generating my own scripts to run using the Red Gate SQL Compare tool. Then came the day when I called one of the scripts from a developer in Powershell. There was no going back. I was hooked. I immediately saw how amazing this could be. And also how my work load would drop.

While I am doing my part in Powershell, there is also some other development going on with the end the developers use – in other words, things are in flux. I have code for the old way they do things and the new way. The new is better in that some of the things I need are built-in to certain aspects so I am able to grab pieces of information and get the things from that I need. With the old, this is not the case. Instead I am passing that information in through parameters.

I had the idea of passing in strings that would then be broken up and inserted into a multidimensional array. With that, I could loop through the array one element at a time, have all the things I needed for each deployment and just sit back and watch the magic happen.

Uhhhhh…did I mention I was green in Powershell?

I had another array already going so I thought I had this. I don’t know if it was because it was late in the day, or if I was a little too confident in my array abilities, but the darn thing was not happening the way I had envisioned. I set the code aside to revisit later with a fresh pair of eyes and a brain that wasn’t so full.

Fast forward to the following week – went back to the same code and FIGURED OUT HOW TO DO IT IN 5 MINUTES. Not only that, but it was far less code than I had previously attempted before.

Here is how I have handled this. Obviously, I would be replacing the text in the strings with information that is relevant to my deployments but the concept is the same. Once I have the array created, I have all the elements I need and it is just a matter of knowing where they are in the array and accessing them.

$strings = @("The cat in the"
,"hat ate a rat"
,"and then sat and"
,"got fat from the rat")

$array = New-Object System.Collections.ArrayList
$array.Clear();

foreach($item in $strings)
{
$array.Add(@($item.Split(" "))) | out-null;
}

for ($i = 0;$i -lt ($array | Measure-Object).count;$i++)
{
Write-Host $array[$i][0]
}

For the sample I am writing the first element. For my project the first element might be the server name for the deployment or some other relevant piece of information and it would be in the same place throughout the array. Since variables and their contents persist in Powershell I added the statement to clear the array before it is loaded. I am also wrapping the code up in a function to keep things tidy.

If you find this useful let me know. Enjoy. ๐Ÿ™‚