Sometimes I go into these simple explorations that make me learn something new that gets me off the routine. SQL Server is always on the agenda and that passion will never die. This exploration was based on the fact when I was talking to someone about some of the super cool stuff people work on – I heard a DBA complain to me that he was working on a “Windows Server Core” machine and he had no UI. I was pleasantly taken aback and got back search on the internet about. I also got to know “SQL Server” can be installed on these server machines. It was fascinating for me to hear such things and I wondered, how can someone work on such machines.
I thought, let me see if we can use some sort of scripting to work on things I take for granted when troubleshooting some error messages. I always look at opening up the Windows Event Viewer to watch through the errors. Now I thought it would be great not to open up Event Viewer and yet be able to query. That was surely a challenge and I wanted to learn something new and interesting to share.
This blog is at the moment a rudimentary shot at going the scripting route using Powershell. Here are some commands that I used. Let me progress from few simple commands to little interesting, complex queries I figured out to write:
1) List the event viewer logs on a given system. This is simple because I need to know what can be queried at any point in time.
get-eventlog -list
As you can see I have about some 27k+ messages and this is a great place to make our query.
2) Let us next try to select the newest 50 messages of application log:
Get-EventLog -LogName Application -newest 50
The next, I was wondering if we can dump on data based on a date value. This gets me to the next query that was written.
3) Gathering logs after a particular date, we can also use “before” to select the messages prior to given dates as shown below:
Get-EventLog -LogName Application -after 1/10/2016
I wanted to make the query less complex by searching on a specific Event type and Event Source. Since I work with SQL Server and the source had to be MSSQLSERVER (default instance name).
4) Selecting only the messages which are logged as “information” for a source like “MSSQLSERVER” and using a clip to basically copy the output to the clipboard:
Get-EventLog -logname application -EntryType information -newest 50 -source *MSSQLSERVER* | clip
When I was writing the above query, I was little clueless to what are valid source types we can use. So I made a query to identify the source names.
5) Find the relevant source to be used in a query:
Get-EventLog -logname "Application" | Select-Object Source -unique
As you can see, if we know how to play around with Powershell and know the query we need – we can always find nice and easy way to get the data. How many SQL DBA’s who read this blog have already tried playing around with PowerShell? What have you been doing with it? Please let me know via the comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
