List SSAS User Roles Using PowerShell (Part 2)

A while ago, I have a blog post in regards to using PowerShell to list user roles and also their members. The blog post can be found here. I’ve gotten a few questions about expanding the PowerShell script to:

1. List user roles and their members for all databases within a particular SQL Server Analysis Services (SSAS) server.
2. Output the result into one table

Here’s a PowerShell script that can be use to accomplish those goals

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
    # Get the SSAS database
    $SSASDatabase = $SSASServer.Databases.Item($DB.name)

    # Get the roles available within the SSAS database and loop thru each of them
    foreach ($Role in $SSASDatabase.Roles)
    {
        # Get the members within the role and loop thru each one of them
        foreach ($UserName in $Role.Members)
        {
            # Create a new object that would store the database name, role name and member user name
            $ItemResult = New-Object System.Object
            $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
            $ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
            $ItemResult | Add-Member -type NoteProperty -name UserName -value $UserName.Name

            # Put the item result and append it to the result object
            $Result +=$ItemResult
        }
    }
}

$Result | Select DatabaseName, RoleName, UserName | format-table -auto -wrap | Out-String

List SQL Server Analysis Services Database Properties Using PowerShell

If you have SQL Server Analysis Services (SSAS) and want to list the SSAS databases using PowerShell, here’s a PowerShell script that can do that:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS database Name
$SSASServer.Databases | Select Name

It turns out that you can do much more than listing your SSAS database name with this. You can get the SSAS database collation, compatibility level, created date, last processed, etc. To get the full list of properties that are available to you, try to run the following PowerShell script:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS database properties that are available to you
$SSASServer.Databases | Get-Member -MemberType property | FT -wrap | Out-String

So now, if I want to get a list of SSAS database name, collation, compatibility level, created data and last processed date, I can run the following PowerShell script:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS Database Name, Collation, Compatibility Level, Created Time, Estimated Size, Last Processed Date  
$SSASServer.Databases | Select Name, Collation, CompatibilityLevel, CreatedTimeStamp, EstimatedSize, LastProcessed | FT -wrap | Out-String

Related Blog Post:

List SSAS User Roles Using PowerShell

Ubuntu + VirtualBox 4.2.12 Guest Additions = Low-Graphic Mode Error

I am using VirtualBox to run several test virtual machines. For most part, it has been pretty good. It’s lightweight and relatively easy and simple to use. Recently, I’ve upgraded VirtualBox to version 4.2.12. The upgrade process itself went well.

Then I started to update the guest additions on each of the virtual machines. Everything seems to be good… until it’s time to update the guest additions on Ubuntu virtual machine (I have Ubuntu 12.04 LTS). When I installed the guest additions, it seems to went well, it uninstall the previous version and install the guest additions for version 4.2.12. But then after I reboot that virtual machine, I got the following error message/ warning:

The system is running in low-grahics mode

Your screen, graphic card, and input device settings could not be detected correctly. You will need to configure these yourself.

I could not even get into the logon screen. Interesting …

Researching around the internet, it looks like there is a problem with Ubuntu and VirtualBox 4.2.12 guest additions. So I decided to try to revert back the guest addition to VirtualBox 4.2.10 guest additions. I was able to get the ISO for VirtualBox 4.2.10 guest additions from here.

Then I do the following:

1. Start the Ubuntu virtual machine
2. When I get “The system is running in low-graphics mode” error/warning, I press the CRTL-ALT-F1 to enter the tty1
3. In VirtualBox menu for that Ubuntu system, I mount the VirtualBox 4.2.10 guest additions ISO that I have downloaded earlier. I just went to Devices – CD/DVD Devices – Choose a virtual CD/DVD disk file. Then select the ISO file.
4. After login in tty1, I ran the command below. It would mount the cd and then run the VirtualBox 4.2.10 guest additions for Linux. It would uninstall the VirtualBox 4.2.12 guest additions and install the VirtualBox 4.2.10 guest additions.
   

   sudo mount /dev/cdrom /cdrom
   cd cdrom
   sudo sh VBoxLinuxAdditions.run

5. Then once it’s done, I reboot the system by running the following command:
   

   sudo reboot

Once I did those, the Ubuntu virtual machine would boot fine.

I also have tried a fresh install of Ubuntu (creating a new virtual machine in VirtualBox 4.2.12). It was fine before I installed the guest additions on it... well I can only get 1024x768 as the max screen resolution, but I can access the Unity desktop. However, after I installed the VirtualBox 4.2.12 guest additions and reboot the system, I got the same error/warning message. Installing the VirtualBox 4.2.10 guest additions seems to be ok.

Good Bye Google Reader, Hello Microsoft Outlook

I’ve been using Google Reader to display RSS feeds from different sources (blogs, news sites, etc.). It certainly has done its job well. Recently, Google has decided to discontinue Google Reader and it will go offline on 1st July 2013.

Truth be told, there are many alternatives out there that I can use to substitute Google Reader. But for now, I am going to use Microsoft Outlook. It makes sense for me since I already have Microsoft Outlook installed on my computer as part of the Microsoft Office Suite and I have been using Microsoft Outlook on daily basis for a while now. In addition, the RSS feed would appear just like a typical Outlook e-mail message (a format in which I have already familiar with).

To add RSS feed to Microsoft Outlook is pretty straight forward. On the left folder navigation, there should be “RSS Feeds” folder. Right click on that folder and select “Add a New RSS Feed”. Then after that you can enter the link to the RSS subscription that you have. Once done, it would create a new folder and Microsoft Outlook would drop any new feed from that RSS subscription to that new folder. Each RSS subscription would have a different folder.

If you have several RSS subscriptions that you want to import from Google Reader to Microsoft Outlook, you should be able to do that by exporting the data from your Google Reader account by going to Google Takeout. You can just export and download your Google Reader account (in the form of a zip file). Inside that zip file, there should be “Subscriptions.xml” file. Extract that file. Then, in Microsoft Outlook, right click on “RSS Feeds” folder, and select “Import an OPML file”. When given the chance, select the XML file that you have extracted. Microsoft Outlook should be able to read the XML file and list the subscriptions that you have in Google Readers. It would also allow you to select which RSS subscription that you want to add in Microsoft Outlook.

I must admit that Microsoft Outlook lacks the features that Google Reader have. For one, in Google Reader, when you enter it, you can see the new RSS feeds from all your subscription in one page (Google Reader was able to aggregate the RSS feeds). In addition, you can organize your RSS subscriptions very easily in Google Reader. These features just don’t seems to be there in Microsoft Outlook (at least as far as I am aware of).

Google Reader is in the “cloud”. So as long as you have access to a compatible browser and the Internet, you can access your RSS subscriptions easily. With Microsoft Outlook, currently you will be limited to your local computer, where you have Microsoft Outlook installed and the RSS subscription(s) setup. Unless, if you are using Microsoft Exchange hosted e-mail. In that case, you might be able to see your RSS subscription(s) from web mail as well. Just be aware that the RSS feeds might be counted against your Exchange mailbox size quota.

Microsoft Outlook as RSS reader certainly has its limitations compared to Google Reader. However, for now, it satisfies my need as a simple and easy to use RSS reader (at least until I find a better one).

Additional Notes:

Alternatively, instead of Microsoft Outlook, you can also use Windows Live Mail (you might also need to have Windows Live Account), which is part of the Windows Essentials and its free.

Windows 7: Burn ISO Image

In Windows 7, we can burn ISO image to DVD or CD, provided that you have either DVD writer or CD writer on your computer and blank DVD(+/-)-RW/R or CD-RW/R, without using third party software. Windows 7 has Windows Disc Image Burner build in. In the beginning, when I tried to use it, it was not quite obvious on how to get to it. But here’s how to use Windows Disc Image Burner to burn ISO Image to blank DVD or CD:

1. Right click on the ISO file that you want to burn to DVD or CD
2. Select Open with and then Windows Disc Image Burner
3. It should then open up the WIndows Disc Image Burner window. You can select the DVD or CD writer drive. You also can choose if you want to verify the disc after burning the ISO image. Remember to insert the blank DVD or CD into your DVD or CD writer. Once you are ready, just click on the Burn button.

It is pretty basic, not much of advanced options. But it gets the job done.

SQL Server Agent Job Last Run Date and Time from Sysjobsteps Table

A couple of weeks ago, I wrote a blog entry in regards to finding out the last successful run date and time of SQL Server Agent job by querying the sysjobhistory table in the msdb database. That blog entry can be found here. The sysjobhistory table stores the execution history of each SQL Server agent job and job steps.

There is another way in which you can get the last run date and time of the SQL Server agent jobs (well, kind of). You can do this by querying the sysjobsteps table in the msdb database. The sysjobsteps stores information of each SQL Server job step, including the last_run_outcome (whether the job step execution failed, succeeded, retry, canceled or unknown), the last_run_date and the last_run_time. Similar to the run_date and run_time columns in the sysjobhistory table, the last_run_date and the last_run_time columns in sysjobsteps have integer data type. Thus, to convert it to date and time, we can use the agent_datetime system function in the msdb database. Unlike, the sysjobhistory, there is no step 0 in the sysjobsteps table, which would give the overall job outcome.

An sample query that we can use to retrieve the last run date and time for each SQL Server job steps:

select j.job_id,
  j.name,
  js.step_id,
  js.step_name,
  last_run_outcome = case when js.last_run_outcome = 0 then 'Failed'
        when js.last_run_outcome = 1 then 'Succeeded'
        when js.last_run_outcome = 2 then 'Retry'
        when js.last_run_outcome = 3 then 'Canceled'
        else 'Unknown'
       end,
  last_run_datetime = msdb.dbo.agent_datetime(
       case when js.last_run_date = 0 then NULL else js.last_run_date end,
       case when js.last_run_time = 0 then NULL else js.last_run_time end)
from msdb.dbo.sysjobs j
  inner join msdb.dbo.sysjobsteps js
   on j.job_id = js.job_id;

The last_run_outcome can be a bit misleading. If the job step has not been run, the last_run_outcome is 0, which indicates that the job step has failed on its last run.

Purging the SQL Server agent history (see my blog post) would not remove the last_run_date and last_run_time value from the sysjobsteps table.

Purge SQL Server Job History

The msdb database stores SQL Server job information, including the job and job steps history. The job history information can grow really fast, especially if you have a lot of SQL Server jobs which run frequently. In turn, this can cause the msdb database to grow in size rather quickly. It is a good idea to purge old SQL Server job history on regular basis. You might want to check with your company policy or legal team on how much of job history you need to keep.

How do you go about purging the SQL Server Job History? There are several ways in which you can do this.

sp_purge_jobhistory

The sp_purge_jobhistory stored procedure in msdb database can be used to delete the SQL Server agent job history. It accepts 3 input parameters:

• (Optional) @job_id - the SQL Server job id of the job history that you want to delete

• (Optional) @job_name - the SQL Server job name of the job history that you want to delete

• (Optional) @oldest_date - delete SQL Server job history that is older than the @oldest_date

One thing to know is that when running the sp_purge_jobhistory, you can specify the job_id or job_name or neither of them. But you can not specify both the job_id and job_name. If you do, you will most likely get the following error message:

"Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25
Supply either @job_id or @job_name to identify the job."

Some sample usage:

To delete all job history:

exec msdb.dbo.sp_purge_jobhistory;

To delete all job history of SQL Server job named "Backup TestDB":

exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB';

Alternatively:

declare @job as uniqueidentifier;
set @job = (select job_id from dbo.sysjobs where [name] = 'Backup TestDB');
exec msdb.dbo.sp_purge_jobhistory @job_id = @job;

To delete job history that is older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @oldest_date = @jobdate;

To delete job hisotry for SQL Server job named "Backup TestDB", which are older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB', @oldest_date = @jobdate;

It is pretty straight forward. To do this on regular basis, you can create a SQL Server agent job to run the sp_purge_jobhistory with the appropriate input parameter(s) on a scheduled time.

SQL Server Agent Properties

If you go to the SQL Server Agent Properties window, you have the option to "remove agent history". Here, you need to specify the cut-off time.

If you try to script this out, you will see that it is generating the following script:

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-12T22:54:55'

So basically it is executing the same sp_purge_jobhistory stored procedure on the background. One thing to remember about purging the job history this way is that it is just one time only. It will not actually create any SQL Server Agent that would automatically delete job history that are older than 4 weeks.

syspolicy_purge_history SQL Server Job

By default, if you are using SQL Server 2008, 2008 R2 or 2012, you might see the syspolicy_purge_history SQL Server agent job. The job is scheduled to run daily at 2 AM. This SQL Server job would look into the Policy Management "HistoryRetentionInDays" property (You can get to this window by going to Management on the Object Explorer and then right click Policy Management and select Properties).

By default, the HistoryRetentionInDays is set to 0, which means that it will not delete any SQL Server job history. If you set it 30, then on daily basis at 2 AM, it will delete SQL Server job history that are older than 30 days.

Last Successful SQL Server Agent Job Run Date and Time

SQL Server stores SQL Server Agent job history in sysjobhistory table within the msdb database. We can query the table to get the date and time of last successful SQL Server agent job(s) run. The table stores each job step history, where step_id 0 for each job actually give the overall job outcome.

The sysjobhistory table also stores each step's run date and time. However, they are being stored in separate columns, run_date and run_time. Also instead of having date and/or time data type, they have integer data type. But don't worry, there is a system function in msdb database, called agent_datetime, which can be used to convert those run_date and run_time into a datetime data type.

So, to get the date and time of the last successful SQL Server Agent job(s) run, we can use the following script:

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
ORDER BY j.name ASC

The script should be pretty straight forward. Basically, first we build a CTE to get the last successful time of each job from the sysjobhistory table. We do this by filtering those SQL Server agent jobs that have run status equal to 1 (success) for job step with id 0. Once we do that, we then create a join of the sysjobs table (which contains information such as the job name), with the CTE.