My friend Reza Rad ( blog | twitter ) reached out to me this morning because he was running into some issues with a PowerShell cmdlet for gathering some log data. To make a long story short, the cmdlet in question had apparently been built with expectation that customers would want to gather results from the last day, or maybe even the last week, but not much more than that.
My friend was trying to grab 3 months work of data all at once and it just wasn’t working. The logs went back for months on end, but you just couldn’t pull that many days at once without running into problems. We quickly realized that just asking for the logs one day at a time was the easiest way to guarantee that we got everything. But who wants to sit there and change the to/from dates 90 times??
I joked that you could write that code in Excel if you wanted but I was sure that there was an easy way to accomplish this by combining a few simple PowerShell tricks:
· Number range 90..1
· Foreach foreach {}
· Date Add (Get-Date).AddDays(–1)
· -Append
PowerShell has a feature which allows you to emit all the numbers in a range. I admit it can sound like a useless trick until you have a reason to combine it with something else. (Like the simple random raffle number generator I still haven’t blogged about.) At which point you’re all like: OMG this is stupid-simple and yet perfect!
The number rage is normally shown as something 1..10, but the range could start anywhere, like 1433..1521. In our case, we can use this to count backwards from 90 [days] like this:
90..1
Again, that’s not much of a big deal, but since we can pass those numbers in to DateAdd via a foreach loop, it completely amplifies what you’re doing.
90..1 | foreach { (Get-Date).AddDays(-$_) }
Now that we have our list of 90 dates ready, all we have to do is combine it with the cmdlet that retrieves our data. In lieu of outing the product team who built the less-than-optimal cmdlet which started this problem, I’ll substitute the Get-SqlErrorLog cmdlet in its place.
90..1 | foreach { $Start=((Get-Date).Date).AddDays(-$_); Get-SqlErrorLog -ServerInstance localhost\sql2017 -After $Start -Before $Start.AddDays(1) | Out-File -Append -FilePath C:\temp\SQLErrors.txt }
3 quick things to point out here.
· I made sure that we were working with the date as of midnight instead of the current time by adding ().Date) around Get-Date.
· It’s probably obvious but just to be clear, the $Start= is setting the date as we’re looping through the code, and to give us a full day worth of logs I’m using .AddDays(1) to add a single day to date within the $Start variable to get the next day.
· Since the problem here is retrieving the logs, not storing their results, I’m using the -Append parameter to store them all in the same file. Of course, we don’t even need the -Append parameter if we were piping the results to the Write-SqlTableData cmdlet we wouldn’t even need the -Append switch, but maybe a SQL Server table was overkill for this particular task.
Obviously, the cmdlet that will not [yet] be named needs to be fixed. In the meantime, a couple minutes & a couple lines of PowerShell code to save yourself from an hour or more of manual work downloading daily log files is a great thing to have in your back pocket.
