Import a bunch of ICS calendar files with PowerShell

One of the fun parts of getting ready for the PASS Summit (or any conference for that matter) is checking out all of the available sessions, and the PASS site’s schedule builder makes it easy! I add a ton of sessions to my schedule without paying attention to time conflicts, then save all the ICS files to add to my calendar in Outlook.

So… this year I went a little overboard and added 35 sessions. I didn’t feel like opening all 35 files, changing free/busy, setting the category (gotta have the color coding!), and clicking save– but I knew somehow PowerShell could do it all for me. I had some scripts that create appointments, but none that import ICS files.

This was another one of those “someone HAS to have done this before…” kinds of things, but no dice.  Of course I went ahead and spent an hour or two to save myself five minutes. Hopefully someone else will find it useful, or at the least I’ll stumble upon it when I inevitably try to do this again next year. Enjoy!

# Put all your ICS files in one folder and set that here:
$ICSpath="C:\Users\emily\temp-pass2013"
$ICSlist = get-childitem $ICSPath

Foreach ($i in $ICSlist ){
     $file= $i. fullname
     $data = @{}
     $content = Get-Content $file -Encoding UTF8
     $content |
     foreach-Object {
      if($_.Contains(':')){
            $z=@{ $_.split( ':')[0] =( $_.split( ':')[1]).Trim()}
           $data. Add( $z. Keys, $z. Values)
       }
     }
     $outlook = new-object -com Outlook.Application
    $calendar = $outlook.Session.GetDefaultFolder(9) 
    $appt = $calendar.Items.Add(1)

     # The body spacing/encoding was a PAIN, excuse the ugliness.
     $Body=[regex]::match($content,'(?<=\DESCRIPTION:).+(?=\DTEND:)', "singleline").value .trim ()
     $Body= $Body -replace "\r\n\s"
     $Body = $Body.replace("\,",",").replace("\n"," ")
     $Body= $Body -replace "\s\s"

     $Start = ($data.getEnumerator() | ?{ $_.Name -eq "DTSTART"}).Value -replace "T"
     $Start = [datetime]::ParseExact ($Start ,"yyyyMMddHHmmss" ,$null )

     $End = ($data.getEnumerator() | ?{ $_.Name -eq "DTEND"}).Value -replace "T"
     $End = [datetime]::ParseExact ($End ,"yyyyMMddHHmmss" ,$null )

     $Subject = ($data.getEnumerator() | ?{ $_.Name -eq "SUMMARY"}).Value
     $Location = ($data.getEnumerator() | ?{ $_.Name -eq "LOCATION"}).Value

     $appt.Start = $Start
     $appt.End = $End
     $appt.Subject = $Subject
     $appt.Categories = "Presentations" #Pick your own category!
     $appt.BusyStatus = 0   # 0=Free
     $appt.Location = $Location
     $appt.Body = $Body
     $appt.ReminderMinutesBeforeStart = 15 #Customize if you want 

     $appt.Save()
    if ($appt.Saved)
        { write-host "Appointment saved."}
    Else {write-host "Appointment NOT saved."}
}

Make a bunch of SQL Server log path shortcuts with PowerShell

At some point, I created several local shortcuts to the base directories of all of my SQL instances to save a little time. It definitely came in handy, but I still had to click through way too many levels to get to the LOG directory.  I know I should probably just use sp_readerrorlog, but I’m a little old school. Whatever.

So I started making a few shortcuts for those and realized, “Oh yeah, PowerShell…”
 
Given:
1. Your default shares of \\servername\drive$ are accessible.
2. If clustered, your instance’s virtual server name resolves properly.
3 (optional). You have a bunch of registered servers in SSMS to query all at once.

Use a multi-instance query to run this little guy:

DECLARE @logdir nvarchar (100);
SET @logdir = CAST(SERVERPROPERTY('ErrorLogFileName' ) AS nvarchar(100));
DECLARE @host nvarchar (25);
SET @host = CAST(SERVERPROPERTY('MachineName' ) AS nvarchar(25));

SELECT @logdir = '\\' + @host + '\'+ LEFT(@logdir ,1)
+ '$\' + REPLACE(RIGHT(@logdir ,LEN( @logdir)-3 ),'\ERRORLOG', '');

--That will take the path of your error log file and fix it up into a UNC dir, such as:
-- \\[INSTANCE]\[DRIVE]$\[INSTALLDIR]\MSSQL10_50.[INSTANCE]\MSSQL\Log

SELECT '$wshshell = New-Object -ComObject WScript.Shell;
$lnk = $wshshell.CreateShortcut("C:\shortcut-path\' + @@servicename + ' Logs.lnk");
$lnk.TargetPath = "' + @logdir + '";
$lnk.Save()' as [Run in PS];

Then run the resulting command(s) in PS to create your shortcuts. Boom!

I’m sure this can all be done with PS in one swoop, but connecting to various versions may give you grief, and this way gives you a little more control. Plus assuming you have all your servers registered in SSMS, you save the hassle of making any sort of list for a for-each.

Getting around schedule limitations in Multi-Server Administration

Attendees at my WMSSUG presentation last night (Tim Ford mainly) quickly pointed out a dilemma that I previously spent a couple months working around: Multi-Server jobs can have a schedule or no schedule, BUT they can’t have unique schedules based on Target Server.

This didn’t seem to be a popular topic out on the internets so I ended up having to create my own scheduling solution. I called it “Agent Job Starter” and it consists of 4 tables, 2 stored procedures, and 1 agent job.

For us visual learners:

In short: All your schedules are stored in a table AgentJobSchedules. Every 10 minutes you run a job that starts the procedure usp_PopulateAgentJobQueue, which figures out which jobs should run in the next 10 minutes and puts them in a semi-transitory table called AgentJobQueue. Next, the job starts the procedure usp_ProcessAgentJobQueue, which takes each item in the queue and starts it on the specified server. One by one rows are removed from the queue and added to the table AgentJobSubmitted, showing when they were submitted and whether or not the attempt was successful.  All of the above objects are held in a database called DBAdmin.

Optionally, your TSX jobs can write their status to another table, AgentJobHistory, making it easy to query all your instances’ job histories in one place.

Tables:

The pieces:

Tables

AgentJobSchedules

([Instance], [Host], [Type], [JobName], [Enabled], [Frequency], [WeekOfMonth], [ScheduleDay], [ScheduleHour] ,[ScheduleMin], [ExcludeDay], [Description])

AgentJobQueue

([rownum], [Instance], [JobName], [TimeQueued], [StartCommand])

AgentJobSubmitted

([Instance], [JobName], [TimeQueued], [TimeSubmitted], [SubmitResult])

AgentJobHistory (optional)

([Instance], [JobName], [StartDate], [StartTime] , [EndDate], [EndTime], [Result])

Procedures

usp_PopulateAgentJobQueue

Checks for the following conditions:

Enabled = Yes, WeekOfMonth = (This week) or All

ScheduleDay = (Today) or All,

ExcludeDay NOT = (Today)

ScheduleHour must match the current hour (Odd, Even, All) for bi-hourly schedules (TLog).

Requires function dbo.fn_getNthDay.

usp_ProcessAgentJobQueue

Goes through the Queue table and starts each job on its remote server (with 10 seconds in between each).

Functions

ufn_getNextJobRunTimes () (optional)

[RunDay], [RunDate],[RunTime],[Freq],[Instance],[JobName],[Description]

Requires function dbo.fn_getNthDay.

ufn_getNextHourJobs() (optional)

Jobs that should run in next hour.

[Freq], [Instance], [JobName], [Time to Run], [Description]

ufn_getLastHourJobs() (optional)

Jobs that should have run in last hour (but not checking actual history).

[Freq], [Instance], [JobName], [Time to Run], [Description]

dbo.fn_getNthDay(@year,@month,@weekno,@weekday)

Gets the date in a month (ie “third wednesday”) given year, month, week number and week day.

Views 

v_History_view (optional)

([Instance], [JobName], [Start], [End], [Result])

v_History_failures (optional)

([Instance], [JobName], [Start], [End], [Result])

v_All_Job_Status (optional – but recommended!)

Requires function dbo.ufn_getNextJobRunTimes() and view v_History_view.

([Instance], [JobName], [LastRun], [Result], [Enabled], [Days Ago], [Description], [Frequency], [ExcludeDay], [Status] ,[NextRunDate] ,[NextRunTime])

v_last_job_run (optional)

Requires view v_History_view.

([Instance], [JobName], [LatestRun], [DaysAgo], [Result])

v_MissedJobs_LastHour (optional)

Jobs that should have run in last hour and whether or not they did.

Requires function ufn_getLastHourJobs().

([Instance], [JobName], [ScheduledTime], [TimeSubmitted], [TimeStampSubmitted], [Result])

Scripts:

Enjoy!

You’ll need to schedule a job on your Master server to run every 10 minutes and execute the two stored procedures in order. Obviously you’ll also need some schedules in the schedule table, example insert:

INSERT INTO [DBAdmin]. [dbo].[AgentJobSchedules]
([Instance] ,[Host], [Type],[JobName] ,[Enabled], [Frequency],[WeekOfMonth] ,
[ScheduleDay], [ScheduleHour],[ScheduleMin] ,[ExcludeDay])
VALUES
( 'SERVER\INSTANCE'  --Instance **Use Server\SID if non clustered
, 'SERVER'      --Host
, 'Development'     --Environment
, 'TSX Test Job'    --Job Name
,'Yes'        --Enabled = Yes or No
, 'Weekly'     --Frequency Daily, Weekly, Monthly, Hourly, TLog
, 'All'        --Week of month: 1,2,3,4,5, or All
, 'All'        --Day of Week: All, Monday, Tuesday, Wednesday,etc...
, '12'   --Start Hour: 0-23, Even, Odd, or All
, '00'   --Start Minute: 0-59, Even, Odd, or All
, 'Thursday'  --Exclude Day (if applicable)
GO

Deciphering a Cron Schedule, for Non-Robots

Can you read these?

00 00-23 * * *
00 12 * * 0
58 05 * * 0
30 00 15 * *
0 1,7,13,19 * * 1-5

Maybe. But you had to think about it, right? Or are you a robot? Well, I’m not a robot, so of course I had to write a script to parse out a cron schedule in unix.

So, given the basic rules:

* * * * * command to be executed
┬ ┬ ┬ ┬ ┬
│ │ │ │ │
│ │ │ │ │
│ │ │ │ └───── day of week (0 – 6) (0 is Sunday, or use names)
│ │ │ └────────── month (1 – 12)
│ │ └─────────────── day of month (1 – 31)
│ └──────────────────── hour (0 – 23)
└───────────────────────── min (0 – 59)

This kornshell script is run like this:  

parse_schedule.ksh ‘* * * * *’

And it will just read it to you like a bedtime story. Then you can go home early and take a nap. You can also use it within other scripts to print out nice pretty English cron comments for you. With no further adieu…

#  script: parse_schedule.ksh
#  purpose: take a cron schedule entry and turn it into a readable sentence.
#
#  author:  emily johnson, July 2012
#!/bin/ksh

function awksched
{
# First 5 are schedule, populate array- removing # if commented out
	SCHED=`print "$SCHED" | sed 's/\*/A/g'`
	Z=`print $SCHED | awk '
	{
	sched = $1 " " $2 " " $3 " " $4 " " $5
	min = $1
	hour = $2
	daymonth = $3
	month = $4
	dayweek = $5
	gsub(/^#/,"",sched)
	print min "#" hour "#" daymonth "#" month "#" dayweek
	}
	'`
	echo $Z | IFS='#' read -r MIN HOUR DAYMONTH MONTH DAYWEEK
}

function getmins
{
	if [[ $MIN != "A" ]] then
		set -A min_array $(echo $MIN | tr ',' ' ')
		MINCOUNT=${#min_array[*]}
		MIN=":"${MIN}
   		MIN=`print "$MIN" | sed 's/,/, :/g'`
	fi
}

function gethours
{
	if [[ $HOUR != "A" ]] then
		set -A hour_array $(echo $HOUR | tr ',' ' ')
		HOURCOUNT=${#hour_array[*]}
	fi
}

function gettime
{
	getmins
	gethours
	if [[ $HOUR = "A" ]] then
		if [[ $MIN = "A" ]] then
	# every hour, every minute
			TIME="every minute of every hour"
		else
	# every hour, certain minutes
			MIN=`print "$MIN" | sed 's/\(.*\),/\1 and/'`
			TIME="every hour at $MIN"
		fi
	else
		if [[ $MIN = "A" ]] then
	# certain hour, every minute
			HOUR=${HOUR}:00
			HOUR=`print "$HOUR" | sed 's/,/:00, /g'`
			HOUR=`print "$HOUR" | sed 's/\(.*\),/\1 and/'`
			TIME="every minute of $HOUR"
		else
			TIMECOUNT=$((HOURCOUNT * MINCOUNT))
			if [[ $TIMECOUNT -eq 1 ]] then
	# exactly once
				TIME="at ${HOUR}${MIN}"
			elif [[ $HOURCOUNT -eq 1 ]] then
	# one hour, multiple minutes
				MIN=`print "$MIN" | sed 's/:/'$HOUR':/g'`
				MIN=`print "$MIN" | sed 's/\(.*\),/\1 and/'`
				TIME="at $MIN"
			elif [[ $MINCOUNT -eq 1 ]] then
	# one minute, multiple  hours
				HOUR=${HOUR}${MIN}
				HOUR=`print "$HOUR" | sed 's/,/'$MIN', /g'`
				HOUR=`print "$HOUR" | sed 's/\(.*\),/\1 and/'`
				TIME="at $HOUR"
			else
	# multiple hours and minutes
				HOUR=${HOUR}:00
				HOUR=`print "$HOUR" | sed 's/,/:00, /g'`
				HOUR=`print "$HOUR" | sed 's/\(.*\),/\1 and/'`
				MIN=`print "$MIN" | sed 's/\(.*\),/\1 and/'`
				TIME="at $MIN after $HOUR"
			fi
		fi
	fi
}

function getmonths
{
	if [[ $MONTH = "A" ]] then
		MONTH="the month"
	else
		MONTH=`print "$MONTH" | sed 's/12/December/'`
		MONTH=`print "$MONTH" | sed 's/11/November/'`
		MONTH=`print "$MONTH" | sed 's/10/October/'`
		MONTH=`print "$MONTH" | sed 's/9/September/'`
		MONTH=`print "$MONTH" | sed 's/8/August/'`
		MONTH=`print "$MONTH" | sed 's/7/July/'`
		MONTH=`print "$MONTH" | sed 's/6/June/'`
		MONTH=`print "$MONTH" | sed 's/5/May/'`
		MONTH=`print "$MONTH" | sed 's/4/April/'`
		MONTH=`print "$MONTH" | sed 's/3/March/'`
		MONTH=`print "$MONTH" | sed 's/2/February/'`
		MONTH=`print "$MONTH" | sed 's/1/January/'`
		MONTH=`print "$MONTH" | sed 's/\(.*\),/\1 and /'`
		MONTH=`print "$MONTH" | sed 's/,/, /g'`
	fi
}

function getdayweek
{
	if [[ $DAYWEEK = "A" ]] then
		DAYWEEK="every day"
	else
		DAYWEEK=`print "$DAYWEEK" | sed 's/0/Sunday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/1/Monday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/2/Tuesday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/3/Wednesday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/4/Thursday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/5/Friday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/6/Saturday/'`
		DAYWEEK=`print "$DAYWEEK" | sed 's/\(.*\),/\1 and /'`
   		DAYWEEK=`print "$DAYWEEK" | sed 's/,/, /g'`
	fi
}

function getdaymonth
{
	set -A daymonth_array $(echo $DAYMONTH | tr ',' ' ')
	DAYMONTHCOUNT=${#daymonth_array[*]}
	elementDM=0
	while [ $elementDM -lt $DAYMONTHCOUNT ]
		do
		typeset -i VAR="${daymonth_array[$elementDM]}"
		if [[ $((VAR % 10)) = 1 ]] && [[ $((VAR % 100)) != 11 ]] then
			VARN="${VAR}st"
		elif [[ $((VAR % 10)) = 2 ]] && [[ $((VAR % 100)) != 12 ]] then
			VARN="${VAR}nd"
		elif [[ $((VAR % 10)) = 3 ]] && [[ $((VAR % 100)) != 13 ]] then
			VARN="${VAR}rd"
		else
			VARN="${VAR}th"
		fi
		daymonth_array[$elementDM]=$VARN
		let elementDM=$elementDM+1
	done
	DAYMONTH=`print ${daymonth_array[@]}`
    DAYMONTH=`print "$DAYMONTH" | sed 's/ /, /g'`
	DAYMONTH=`print "$DAYMONTH" | sed 's/\(.*\),/\1 and/'`
}

function getdaysummary
{
	if [[ $DAYWEEK = "A" ]] then
		if [[ $DAYMONTH = "A" ]] then
	# All weekdays and days of month
			if [[ "$MONTH" = "the month" ]] then
				if [[ $HOUR = "A" ]] then
					DAY=""
				else
					DAY=" every day"
				fi
			else
				DAY=" every day in $MONTH"
			fi
		else
	# All weekdays and certain days of month
			getdaymonth
			DAY=" on the $DAYMONTH day of $MONTH"
		fi
	else
		getdayweek
		if [[ $DAYMONTH = "A" ]] then
	# All days of month, certain weekdays
			if [[ "$MONTH" = "the month" ]] then
				DAY=" every $DAYWEEK"
			else
				DAY=" every $DAYWEEK of $MONTH"
			fi
		else
	# Certain weekdays and certain days of month
			getdaymonth
			if [[ "$MONTH" = "the month" ]] then
				DAY=" every $DAYWEEK and the $DAYMONTH day of $MONTH"
			else
				DAY=" every $DAYWEEK of $MONTH and the $DAYMONTH day of $MONTH"
			fi
		fi
	fi
}

set -o noglob

SCHED="$1"

awksched
gettime
getmonths
getdaysummary

print "Executes ${TIME}${DAY}."

Test time!!

> parse_schedule.ksh ’00 12 * * 0′
Executes at 12:00 every Sunday.

> parse_schedule.ksh ’00 1,7,13,19 * * 1-5′
Executes at 1:00, 7:00, 13:00 and 19:00 every Monday-Friday.

> parse_schedule.ksh ’01 2 3 4 5′
Executes at 2:01 every Friday of April and the 3rd day of April.

Not that you’d ever use that last one, unless you have really weird requirements.. or you’re a weirdo yourself.
Hooray!

One-step DB Mail configuration

I was just asked about a SQL Server DB Mail setup script, so I figured I’d share. I use this when building new instances, as it takes care of the account and profile at the same time with no user input.

The profile will be called “INSTANCENAME_Mail” and the email address will be (Agent startup account)@yourdomain.com.

All you need to do is update the script with the SMTP server name and your domain name for the outgoing email address. The agent service account will be retrieved from the registry. NOTE: this assumes your SQL Agent is running as a domain account (ie DOMAIN\ServiceAccountName).

USE msdb
GO

DECLARE @ProfileName VARCHAR(128)
DECLARE @AccountName VARCHAR(128)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @ServiceAccount varchar(128)

--Get service account name (for email account name and email address)
EXECUTE master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
	N'ObjectName',
	@ServiceAccount OUTPUT,
	N'no_output'
	
--Trim "DOMAIN\" off service account
Set @ServiceAccount = RIGHT(@ServiceAccount, len(@ServiceAccount)-CHARINDEX('\',@ServiceAccount))

--Set up ProfileName, AccountName, and EmailAddress.
SET @ProfileName = @@Servicename + '_Mail';
SET @AccountName = @ServiceAccount;
SET @EmailAddress = @ServiceAccount + '@YOUR-DOMAIN.com';  --  <-- UPDATE THIS

--Add account to the system.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = 'SQL Server Service',
@description = 'SQL Server Service Account',
@mailserver_name = 'YOUR-SMTP-SERVER',  --  <-- UPDATE THIS
@port = 25

--Add Profile to the system.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName 

--Add Profile-Account association to the system.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,@account_name = @AccountName,
@sequence_number = 1 ;

Query ReportServer for SSRS Subscription and Snapshot Schedules – Part 1

Happy big-ass SSRS query Friday!

This one will retrieve all subscriptions, all history snapshots and their schedules. I wasn’t able to find anything online about querying snapshot information, much less their schedules, so I DIY’ed it.

Knowing this information is extremely helpful – especially in an “unregulated” environment where schedules have accumulated over the years and nobody knows what’s what. ALSO when you’re investigating why the heck everything starts failing at certain times of day (see this blog post at PerformancePoint). Snapshots and subscriptions at the same time can be troublemakers.

Anyway, here it is.. only tested on 2005 for now.
Part 2 will be posted soon, which is a condensed way to see exactly how many execute at any time on any day.

SELECT ALLSCH.Type,ALLSCH.RptName,ALLSCH.Path,ALLSCH.UserName,ALLSCH.Description,
ALLSCH.scheduletype,ALLSCH.ScheduleName,ALLSCH.LastRuntime,
--ALLSCH.SQLjobID, --(optional)
 JB.occurs_detail, JB.Sun,JB.Mon,JB.Tue,JB.Wed,JB.Thu,JB.Fri,JB.Sat, JB.frequency, JB.IsEnabled
 
FROM

   ------- Scheduled subscriptions ------
(SELECT 'Subscription' AS Type, cat.[Name] AS RptName, cat.[Path], U.UserName, sub.Description, sub.DeliveryExtension AS scheduletype
,CASE WHEN sub.DeliveryExtension='SharedSchedule' THEN sc.name ELSE 'Unique' END AS ScheduleName
,res.ScheduleID AS SQLJobID, sub.LastRuntime
FROM ReportServer.dbo.Catalog AS cat
    INNER JOIN Reportserver.dbo.Subscriptions AS sub ON cat.ItemID = sub.Report_OID
    INNER JOIN Reportserver.dbo.ReportSchedule AS res ON cat.ItemID = res.ReportID AND sub. SubscriptionID = res.SubscriptionID
    INNER JOIN msdb.dbo.sysjobs AS job ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
    INNER JOIN msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
    INNER JOIN Reportserver.dbo.Users U ON U.UserID = sub.OwnerID 
    LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = res.ScheduleID

UNION ALL

   ------- Scheduled snapshots ------
SELECT 'Snapshot' AS type, z.* FROM
(SELECT distinct C.Name AS rptname, c.path,u.username,S. Description,ScheduleType = sc.EventType
,CASE WHEN sc.EventType='SharedSchedule' THEN Sc.name ELSE 'Unique' END AS ScheduleName
,sc.scheduleID AS SQLjobID,sc.LastRunTime
  FROM Reportserver.dbo.History H JOIN Reportserver.dbo.SnapshotData S ON H.SnapshotDataID = S.SnapshotDataID
		JOIN Reportserver.dbo.Catalog c ON C.ItemID = H.ReportID
		LEFT JOIN Reportserver.dbo.ReportSchedule Rs ON RS.ReportID = H.ReportID AND RS.ReportAction = 2
		LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = rs.ScheduleID
		JOIN Reportserver.dbo.Users U ON U.UserID = sc.createdbyid
		WHERE sc.scheduleID is not null) z ) ALLSCH
   
    JOIN
    
   ------- Agent job schedule detail ------
(SELECT cast(j .Name AS varchar( 36)) AS JobName
,CASE s.freq_type 
	WHEN  1 THEN 'Once'
	WHEN  4 THEN 'Every ' + CASE WHEN s.freq_interval=1 THEN 'day' 
								 ELSE CONVERT (varchar, s.freq_interval )  + ' days' END
	WHEN  8 THEN (CASE WHEN s.freq_recurrence_factor =1 THEN 'Weekly on '
			 ELSE 'Every '  + CASE WHEN s.freq_recurrence_factor =1 THEN 'week on ' 
								   ELSE CONVERT (varchar, s.freq_recurrence_factor ) + ' weeks on ' END  END )
		+ CASE s.freq_interval
			WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 4 THEN 'Tuesday' WHEN 8 THEN 'Wednesday'
			WHEN 16 THEN 'Thursday' WHEN 32 THEN 'Friday' WHEN 64 THEN 'Saturday' WHEN 127 THEN 'all days'
			WHEN 126 THEN 'all days except Sunday' WHEN 125 THEN 'all days except Monday' WHEN 123 THEN 'all days except Tuesday' 
			WHEN 119 THEN 'all days except Wednesday' WHEN 111 THEN 'all days except Thursday' WHEN 95 THEN 'all days except Friday'
			WHEN 63 THEN 'all days except Saturday' WHEN 62 THEN 'Monday through Friday' WHEN 34 THEN 'Monday and Friday' 
			WHEN 30 THEN 'Monday through Thursday' WHEN 48 THEN 'Thursday and Friday' WHEN 65 THEN 'Saturday and Sunday' 
			WHEN 60 THEN 'Tuesday through Friday' WHEN 124 THEN 'Tuesday through Saturday' WHEN 31 THEN 'Sunday through Thursday' 
			ELSE (CASE WHEN s.freq_interval &amp; 1 = 0 THEN '' ELSE 'Sunday,' END +
				   CASE WHEN s.Freq_interval &amp; 2 = 0 THEN '' ELSE 'Monday,' END + 
				   CASE WHEN s.freq_interval &amp; 4 = 0 THEN '' ELSE 'Tuesday,' END + 
				   CASE WHEN s.freq_interval &amp; 8 = 0 THEN '' ELSE 'Wednesday,' END +
				   CASE WHEN s.freq_interval &amp; 16 = 0 THEN '' ELSE 'Thursday,' END + 
				   CASE WHEN s.freq_interval &amp; 32 = 0 THEN '' ELSE 'Friday,' END +
				   CASE WHEN s.freq_interval &amp; 64 = 0 THEN '' ELSE 'Saturday' END)  END
	WHEN 16 THEN 'Day ' + CONVERT (varchar, s.freq_interval )
        + ' of every '  + CASE WHEN s.freq_recurrence_factor=1 THEN 'month' 
							   ELSE CONVERT (varchar, s.freq_recurrence_factor ) + ' months' END
	WHEN 32 THEN 'The ' + CASE s.freq_relative_interval WHEN  1 THEN 'first' WHEN  2 THEN 'second'
            WHEN  4 THEN 'third' WHEN  8 THEN 'fourth' WHEN 16 THEN 'last' END
        + CASE s.freq_interval
            WHEN  1 THEN ' Sunday' WHEN  2 THEN ' Monday' WHEN  3 THEN ' Tuesday' 
            WHEN 4 THEN ' Wednesday' WHEN  5 THEN ' Thursday' WHEN  6 THEN ' Friday' 
            WHEN 7 THEN ' Saturday' WHEN  8 THEN ' Day' WHEN 9 THEN ' Weekday' 
            WHEN 10 THEN ' Weekend Day' END
        + ' of every ' + CASE WHEN s.freq_recurrence_factor = 1 THEN 'month' 
							  ELSE CONVERT (varchar, s.freq_recurrence_factor ) + ' months' END 
		END AS Occurs_detail    
,CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 1 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (1,8,10)) THEN 'X' ELSE '' END AS Sun,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 2 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
				OR (s.freq_type=32 AND s.freq_interval IN (2,8,9)) THEN 'X' ELSE '' END AS Mon,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 4 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (3,8,9)) THEN 'X' ELSE '' END AS Tue,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 8 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (4,8,9)) THEN 'X' ELSE '' END AS Wed,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 16 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (5,8,9)) THEN 'X' ELSE '' END AS Thu,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 32 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (6,8,9)) THEN 'X' ELSE '' END AS Fri,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 64 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (7,8,10)) THEN 'X' ELSE '' END AS Sat
,CASE s.freq_subday_type
     WHEN 1 THEN 'at ' + LEFT(RIGHT( '000000' + CONVERT(VARCHAR,s.active_start_time), 6),2 ) + ':' 
		+ SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
		+ RIGHT(RIGHT('000000' + CONVERT (VARCHAR, s.active_start_time ),6), 2)
     WHEN 2 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval ) + ' Seconds(s) Starting at '
        + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' 
        + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
     WHEN 4 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval )
        + ' Minute(s) Starting at ' + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' 
        + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
     WHEN 8 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval ) + ' Hour(s) Starting at '
        + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' + SUBSTRING(RIGHT('000000' 
        + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' + RIGHT(RIGHT('000000' 
        + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
	END AS Frequency
,CASE s.enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END AS IsEnabled
FROM  msdb.dbo.sysjobs j (NOLOCK)
	INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
	INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
	WHERE j.category_id =104) JB 
ON JB.jobname = ALLSCH.SQLjobid
--WHERE JB.IsEnabled='Enabled' ---Optional also, if you want to show only active schedules.
ORDER BY JB.occurs_detail,JB.frequency

See Part 2!

Query ReportServer for SSRS Subscription and Snapshot Schedules – Part 2

That was quick.

So if you want to know exactly how many reports are scheduled to run (either via subscription or snapshot) as any given time of day or week, this is your guy.

Sample results:

Again only 2005 for now, but here it is:

SELECT T2.type,T2.frequency,
SUM(CASE WHEN sun = 'X' then totreports ELSE 0 END) AS sunday,
SUM(CASE WHEN mon = 'X' then totreports ELSE 0 END) AS monday,
SUM(CASE WHEN tue = 'X' then totreports ELSE 0 END) AS tuesday,
SUM(CASE WHEN wed = 'X' then totreports ELSE 0 END) AS wednesday,
SUM(CASE WHEN thu = 'X' then totreports ELSE 0 END) AS thursday,
SUM(CASE WHEN fri = 'X' then totreports ELSE 0 END) AS friday,
SUM(CASE WHEN sat = 'X' then totreports ELSE 0 END) AS saturday
FROM
(
SELECT T1.type, T1.Sun,T1.Mon,T1.Tue,T1.Wed,T1.Thu,T1.Fri,T1.Sat, T1.frequency, count(T1.path) AS totreports
FROM
(SELECT ALLSCH.Type,ALLSCH.Path
, JB.Sun,JB.Mon,JB.Tue,JB.Wed,JB.Thu,JB.Fri,JB.Sat, JB.frequency
FROM
   ------- Scheduled subscriptions ------
(SELECT 'Subscription' AS Type
, cat.[Path],res.ScheduleID AS SQLJobID
FROM Reportserver.dbo.Catalog AS cat
    INNER JOIN Reportserver.dbo.Subscriptions AS sub ON cat.ItemID = sub.Report_OID
    INNER JOIN Reportserver.dbo.ReportSchedule AS res ON cat.ItemID = res.ReportID AND sub. SubscriptionID = res.SubscriptionID
    INNER JOIN msdb.dbo.sysjobs AS job ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
    INNER JOIN msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
    INNER JOIN Reportserver.dbo.Users U ON U.UserID = sub.OwnerID 
    LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = res.ScheduleID

UNION ALL

   ------- Scheduled snapshots ------
SELECT 'Snapshot' AS type, z.* FROM
(SELECT distinct c.path ,sc.scheduleID AS SQLjobID
  FROM Reportserver.dbo.History H JOIN Reportserver.dbo.SnapshotData S ON H.SnapshotDataID = S.SnapshotDataID
		JOIN Reportserver.dbo.Catalog c ON C.ItemID = H.ReportID
		LEFT JOIN Reportserver.dbo.ReportSchedule Rs ON RS.ReportID = H.ReportID AND RS.ReportAction = 2
		LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = rs.ScheduleID
		JOIN Reportserver.dbo.Users U ON U.UserID = sc.createdbyid
		WHERE sc.scheduleID is not null) z ) ALLSCH
   
    JOIN
    
   ------- Agent job schedule detail ------
(SELECT cast(j .Name AS varchar( 36)) AS JobName
,CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 1 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (1,8,10)) THEN 'X' ELSE '' END AS Sun,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 2 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
				OR (s.freq_type=32 AND s.freq_interval IN (2,8,9)) THEN 'X' ELSE '' END AS Mon,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 4 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (3,8,9)) THEN 'X' ELSE '' END AS Tue,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 8 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (4,8,9)) THEN 'X' ELSE '' END AS Wed,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 16 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (5,8,9)) THEN 'X' ELSE '' END AS Thu,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 32 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (6,8,9)) THEN 'X' ELSE '' END AS Fri,
       CASE WHEN (s.Freq_type=8 AND s.freq_interval &amp; 64 != 0) OR (s.freq_type=4 AND s.freq_interval=1) 
       		OR (s.freq_type=32 AND s.freq_interval IN (7,8,10)) THEN 'X' ELSE '' END AS Sat
,CASE s.freq_subday_type
     WHEN 1 THEN LEFT(RIGHT( '000000' + CONVERT(VARCHAR,s.active_start_time), 6),2 ) + ':' 
		+ SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
		+ RIGHT(RIGHT('000000' + CONVERT (VARCHAR, s.active_start_time ),6), 2)
     WHEN 2 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval ) + ' Seconds(s) Starting at '
        + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' 
        + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
     WHEN 4 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval )
        + ' Minute(s) Starting at ' + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' 
        + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' 
        + RIGHT(RIGHT('000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
     WHEN 8 THEN 'every ' + CONVERT (varchar, s.freq_subday_interval ) + ' Hour(s) Starting at '
        + LEFT(RIGHT( '000000' + CONVERT(VARCHAR ,s.active_start_time),6 ),2) + ':' + SUBSTRING(RIGHT('000000' 
        + CONVERT(VARCHAR ,s.active_start_time),6 ),3, 2) + ':' + RIGHT(RIGHT('000000' 
        + CONVERT(VARCHAR ,s.active_start_time),6 ),2)
	END AS Frequency
,CASE s.enabled WHEN 1 THEN 'Enabled' WHEN 0 THEN 'Disabled' END AS IsEnabled
FROM  msdb.dbo.sysjobs j (NOLOCK)
	INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
	INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
	WHERE j.category_id =104) JB 
ON JB.jobname = ALLSCH.SQLjobid
WHERE JB.IsEnabled='Enabled') AS T1
group by T1.type, T1.Sun,T1.Mon,T1.Tue,T1.Wed,T1.Thu,T1.Fri,T1.Sat, T1.frequency
) T2 group by T2.type,T2.frequency
order by T2.frequency

If you missed it, Part 1 shows how to use a similar query for detailed item information.