Get configuration manager update size before deploying

Configuration manager update size

This blog post will help you query your configuration manager update size, so that you have an idea about the network bandwidth consequences when deploying your updates.

By now you might be wondering, what is so special about this? You might think that you can go to your configuration manager console, and just highlight the details of one of the Windows updates and see the size of its package. Let us do this together and see what we will see.

I am opening the configuration manager console and browsing to Software Library > Software Updates > All Software Updates. What you see is a list of updates, but without information about the size of each update. You must double click each update, and go to the Content Information tab, and then see the size. Sometimes, an update might have many items in in the content information tab, and each item has size information.

If you have a Software Update Group that contains your updates that you want to deploy, you have no idea what the total size of updates in that group is. You need either to:

Download updates in your Software Update Group, and then go to the folder where updates are downloaded and check the size of that folder. That might work, but it is time consuming. What if you figured out that the size is too big, would go then and create a new Software Update Group, and pick fewer updates?

Double click each update inside your software update group, and check the Content Information tab, and start adding the size of each update.

A better way I believe is whenever I want to deploy software updates, I would start first by querying software updates in the previous month, and then pick those that I see fit and add them to a software update group for further deploy. What I want to see is the size of each update in that list.

The SQL query that solves this

I built an SQL query, that will get all software updates filtered by a time range (so you can say, give me all software updates between 1st of April and 1st of May), and this query will display the size of each update.

This becomes handy as the networking team will not allow me to deploy updates that are more than 3 GB in size that are targeting to certain offices with low internet bandwidth. BranchCache would be an option here, but this is a different conversation. So, now I get the overall view of all software updates, their severity, their size, and other information.

How updates are represented inside the database?

I almost gave up trying to go through all these tables inside the configuration manager database and trying to make sense of the JOINT statements.

I guess I understand how updates are represented inside the configuration manager database. It is important to learn that there is a concept of Software Bundle, and inside it, you will find one or more update files. Think of software bundles as a logical unit to group multiple update files.

What you see in the configuration manager console under software update, is the list of software bundle. If you double clicked any of those bundles, you can see the actual files under the content information tab. There might be one of more files there.

To build the SQL query, I must:

Get a list of all software bundles [available in
v_UpdateCIs database view]

For each bundle, I shall get a list of the actual files inside it. [available in
v_UpdateContents database view]

For each file, I will get the size and other information about it. [available in the
CI_ContentFiles database table.

Note that there is a One-to-Many relationship between software bundles and the actual files each bundle contains. This is why we need a mapping table
v_UpdateContents , which lists each software bundle, and the list of actual files it contains.

Here you can see me querying the
v_UpdateCIs view. This will get me all software bundles identified by
CI_ID , which is an important identifier. You can see that for each of those bundles, I can see the ArticleID, Sevirity, IsSuperseded, and more information. This is what you see when you browse software updates inside the configuration manager console.

Now, I will go to the
v_UpdateContents view, where I can see for each software bundle (identified by
CI_ID ), which files (identified by
ContentCI_ID ) are member of that software bundle. This is just a mapping table between software bundles and the actual update files.

Finally, now that we know how to pull the actual files, and what software bundles they are member of, we can go and get some information (including the file size) about them. We can do that by going to the
CI_ContentFiles table.

And here is a simple representation about the three tables, and their relationship:

configuration manager update size 5

The SQL Query that will bring all this together

I wrote this simple, yet great SQL query, that you can run against your SCCM database and it will get you a list of all your software updates (same view you see in your configuration manager console under software updates), but this time, with the size information for each software update item “Bundle”.

Just make sure you change this line [
WHEREUI.DatePostedBETWEEN'1/1/2015'AND'2/1/2015' ] to match your filter criteria.

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

SELECT

CI.CI_ID,

(SUM(FileSize)/1024)/1as'Size in KB',

CI.ArticleID,

CI.BulletinID,

LOC.DisplayName,

CI.CustomSeverity,

CI.Severity,

CI.MaxExecutionTime,

TYP.CategoryTypeName,

TYP.CategoryInstanceName,

Case(UI.IsDeployed)

When0Then'No'

Else'Yes'

Endas'Deployed',

Case(UI.IsExpired)

When0Then'No'

Else'Yes'

Endas'Expired',

CASE(UI.Severity)

When2Then'Low'

When6Then'Moderate'

When8Then'Important'

When10Then'Critical'

Else'NA'Endas'Severity',

UI.DatePosted,

CASE(ui.IsSuperseded)

When0Then'No'

Else'Yes'

Endas'Superseded'

FROM

v_UpdateContentsJOINv_UpdateCIs

ONv_UpdateCIs.CI_ID=v_UpdateContents.CI_ID

JOINCI_ContentFiles

ONv_UpdateContents.content_id=CI_ContentFiles.Content_ID

JOINCI_UpdateCIsASCI

ONCI.CI_ID=v_UpdateContents.CI_ID

LEFTJOINv_LocalizedCIProperties_SiteLocASLOC

ONLOC.CI_ID=ci.CI_ID

LEFTJOINv_CICategoryInfoTYP

ONTYP.CI_ID=ci.CI_ID

AND

TYP.CategoryTypeName='UpdateClassification'

LEFTJOINCI_UpdateInfoINF

ONinf.CI_ID=ci.CI_ID

LEFTJOINv_UpdateInfoUI

ONUI.CI_ID=ci.CI_ID

WHEREUI.DatePostedBETWEEN'1/1/2015'AND'2/1/2015'

GROUPBY

CI.CI_ID,

CI.ArticleID,

CI.BulletinID,

LOC.DisplayName,

CI.CustomSeverity,

CI.Severity,

CI.MaxExecutionTime,

TYP.CategoryTypeName,

TYP.CategoryInstanceName,

Case(UI.IsDeployed)

When0Then'No'

Else'Yes'

End,

Case(UI.IsExpired)

When0Then'No'

Else'Yes'

End,

CASE(UI.Severity)

When2Then'Low'

When6Then'Moderate'

When8Then'Important'

When10Then'Critical'

Else'NA'End,

UI.DatePosted,

CASE(UI.IsSuperseded)

When0Then'No'

Else'Yes'

End

ORDERBYDisplayName

Now, the results will look like this:

It comes with a PowerShell functionality

I am not sure why I did it, but seems like most network administrators are more comfortable with PowerShell than playing with SQL queries. I created a PowerShell wrapper [Get-CMSoftwareUpdateSize] that you can use to get you the same information. Detailed about the PowerShell wrapper can be found at Microsoft TechNet Gallery here. Running the script will get you a nice view like this, and a CSV file containing all updates and their sizes. The script need the following information to run:

About The Author

Ammar is a digital transformer, cloud architect, public speaker and blogger.
He is considered a trusted advisory with the ability to quickly navigate complex multi-cultural organizations and continuously improve and motivate cross-functional teams to achieve higher productivity, collaboration, revenue gain and cross-group knowledge sharing.
His contributions to the tech community helped him get awarded the Microsoft Most Valuable Professional.
Ammar appears in a lot of global conferences, and he has many publications about digital transformation and next generation technologies.