Filtering SSRS subscriptions from Sqlserver Jobs in Powershell

James Easton - The Salisbury Guide - Poultry CrossIf a Sqlserver instance hosts a Reporting Services database, then users’ report subscriptions are set up as Sql Server Agent jobs.

They are named like this:
E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
E668784B-0AA9-4DEC-8C3B-C9EB6CBC7ADE
E72E1822-408E-4008-B2D4-6065843A9C86
E8658059-BEF6-493A-908F-928B9B3F2065
EA5D435F-1095-4578-9325-EC3AB14327AC

If you query Sqlserver jobs from Powershell (or in SSMS) these can tend to ‘swamp’ the ‘normal’ jobs like so:
CAEC2E40-7AF5-4CB1-B7D2-66343A79471B
CDAEDD6F-07C6-4849-A8AA-5643F771B39B
CDF9A96D-747F-4439-855C-CE916E261646
CEEC38CC-3FCA-439E-8C32-33719F9291D2
database1 backup
DB64DBA4-D51E-43A0-BA17-A4B247075F70
DB834C0D-6206-4A22-87E6-821414EF95E4
DEF15AC7-8142-4909-A4A2-9D40B8CAC3AD
DF4E92E3-E689-44EF-B6C6-8065C18734F0
DF9471A5-4829-4C3E-ACF9-B0D912E26AD2
E05EC820-D572-4168-B14A-24A9102AB1EB
E13A783B-DAED-4F51-87AE-058B10AFCF60
E1E6746A-8F87-4DF3-BD30-3D88285F5305
E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
F441C4C6-342A-476B-B9B4-9B1FA7AFC545
F46BC346-24A6-41A8-8965-CC91E8F8BE26
F6EC8655-0487-435C-A390-622B692B63DE
F9174393-7A59-4E71-A74B-0E27A9A0118B
F9BB8EEA-BD93-4580-A5F8-DA754EF7DC32
F9C775B1-F724-4146-A4CD-64D0735ECE15
FC0FECED-BCC6-46AF-B7C9-E0551DA01E15
FDC6412F-C153-449E-B7E5-FD03281B69E4
FF2A1611-1763-4790-B7C9-64ABD83B710C
FF4A61A4-2580-4E94-AAEA-66FB7C7ABD9D
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

You can filter the SSRS stuff out in Powershell by filtering on the ‘Category’ property as follows:
dir Sqlserver:sql$ServerInstanceJobserverJobs | where {$_.Category -ne 'Report Server'}

This gives you a more useful list
Name
db1 backup
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

Advertisements