SCCM – extracting the number of SEP definitions stored on the server

There is a well known issue with Symantec Endpoint Protection clients – they are not able to delete old definitions. This is a really big trouble as the C drive is getting full all the time.
Unfortunatelly I still haven’t developed a relyable way for dealing with this in an automated way [Tamper protection is the main reason] and as you may imagine, when the number of servers is high, Administrator is always busy πŸ™‚
As a temporary reactive solution I was using SEP Manager notifications showing Out-Of-Date Clients. In 80% of cases out-of-date means C drive is full. But I’ve decided that I need something more intelligent, some additional indication that could show which servers are suffering from the old SEP definitions. SCCM reporting will be described bellow.

So, affected servers would have more then one definition. Here is an example of what you may see:

In this particular case 20121020.007 is the only thing SEPΒ is really need. All other definitions are just sitting there with no use.

In the report I would like to see a number of stored definitions on each of the server in my environment.
By default SCCM Software Inventory Client does not collect information about all the files, therefore information is not in the database. Go to your Configuration Manager console, open “Site Settings” and then “Client Agents”.

Open Software Inventory Client Agent properties. Inventory Collection needs to be configured:

I’m using TCDEFS.DAT since this file is located in every definition folder.

Location is set to “C:\ProgramData\Symantec”, because I’m assuming that all the servers are Windows Server 2008. For example if you are dealing with Windows Server 2003 then location will be different.

Now you have to wait for the Software Inventory cycle. Check your schedule to understand how long will it take. Or of cource you can initiate the action manually. A quick way to check that the data was collected is SCCM Resource Explorer.

Data will be stored in the SQL database, meaning we can generate a Report.
Create a new Report with the following query:

SELECT
v_R_System.Name0,
v_GS_SoftwareFile.FileName,
v_GS_SoftwareFile.FilePath,
v_GS_LOGICAL_DISK.Size0/1024 AS [C: Total, GB],
v_GS_LOGICAL_DISK.FreeSpace0/1024 AS [C: Free, GB]

FROM
dbo.v_R_System

INNER JOIN
v_GS_SoftwareFile ON v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
INNER JOIN
v_GS_LOGICAL_DISK ON v_GS_LOGICAL_DISK.ResourceID=v_R_System.ResourceID

WHERE v_R_System.Netbios_Name0 LIKE 'ServerName%' AND v_GS_SoftwareFile.FileName = 'TCDEFS.DAT' AND v_GS_LOGICAL_DISK.Size0 IS NOT NULL

ORDER BY dbo.v_R_System.Name0, v_GS_SoftwareFile.FilePath

Here ‘ServerName%’ is your server name mask.

Running the report is going to generate you something similar to this:

If you are interested only in numbers, then use this query:

SELECT 
v_R_System.Name0 AS [Server Name],
Count(v_R_System.Name0) AS [Number of stored Definitions]

FROM         
dbo.v_R_System  

INNER JOIN 
v_GS_SoftwareFile ON v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID

WHERE v_R_System.Netbios_Name0 LIKE '%' AND v_GS_SoftwareFile.FileName = 'TCDEFS.DAT'

GROUP BY dbo.v_R_System.Name0

ORDER BY dbo.v_R_System.Name0

That’s pretty much it. Surelly you can optimize the output according to your needs.
What is important to know is that information about the files is stored in the “v_GS_SoftwareFile” table [view to be exact]. It took me some time to find this table πŸ™‚

Advertisements

Leave a Comment here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s