I have seen some question on the forums and heard from customers , is it possible to change the retention period on the databas how long data is saved for Forefront Endpoint Protection Managed by ConfigMgr, so I tried to find the information and I found it so Id though id share the information with you.
You need to open the SQL Management Studio and execute the following command on your FEP DW Database
XXX Corresponds to your ConfigMgr SiteCode and value is the retention period a value between 3 and 12 (months)
EXEC FEPDW_XXX.dbo.spAN_Common_Report_UpdateMaintenanceConfiguration value
So for a site with sitecode P01 and I want to keep tha data for 3 months its this command to execute.
EXEC FEPDW_P01.dbo.spAN_Common_Report_UpdateMaintenanceConfiguration 3
If you want to use the lastLogontimeStamp value in Config Mgr reports its fully possible. The value will give you a date when your machine last was in contact with your Active Directory with some modifications, In fact the date will be between 9-14 days behind the actual date with default settings. But at least it gives you a hint on your clients activity. More about the value here: http://blogs.technet.com/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx .
So first you need to collect the value this is possible to do with your system discovery task. All we need to to is to configure an extra attribute to be collected “lastLogontimeStamp”.
Go to your discovery task and the Active Directory Attribute and add a new value lastLogontimeStamp, and press ok and at the next discovery the value will be collected.
The Value itself is is stored in INT8 se we need to convert the value to Date and Time when we Display it. My SQL Skills are note the best in the world so I asked my friend John to help me out and so he did. So for further references please read the blog post he wrote about it.
In my case I am on SQL 2008 so I did my function and used it in my query to get the machine name and the lastLogontimeStamp in my Report.
Function to Convert Int8 to Date Time
go
CREATE FUNCTION dbo.udf_Int8_to_DateTime(
@Int8 BIGINT
)
RETURNS DATETIME2
AS
BEGIN
RETURN (DATEADD(mi,@Int8 / 600000000 + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST(’1/1/1601′ AS DATETIME2)))
END
GO
SQL Report to get Machine Name, Usaername and Last AD Contact using the function created in the previous step.
SELECT v_R_System.Name0, v_R_System.User_Name0, CAST(dbo.udf_Int8_to_DateTime(v_R_System.LastLogonTimeStamp0) as varchar(11)) as LastADContact
FROM v_R_System
ORDER BY LastADContact asc
This has been tested on SQL 2008, but you should always test on lab systems before moving any code to production. This is released “AS IS” and confers no rights.
References
Well the case is that Configuration Manager doesnt support patches that require User input and isnt therefor replicated to the Configuration Manager environment. So theese updates need to be deployed in another way, although many customers patch clusters manually so it is not a problem for them, but you wont see the compliance status for thoose patches either. So Config Mgr admins out there keep an eye on this´so you wont miss important patches for your environment.
To find updates that cant be installed with Configuration Manager you can query your SUSDB for patches that require User Input.
USE SUSDB
SELECT SecuirtyBulletin, DefaultTitle, KnowledgebaseArticle, InstallationCanRequestUserInput
FROM PUBLIC_VIEWS.vUpdate Where InstallationCanRequestUserInput = 1
Source of material: http://social.technet.microsoft.com/Forums/en-US/configmgrsum/thread/a4ea7e76-0589-4eb4-a31f-a17b6ab9ea41
select SYS.Netbios_Name0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
Where SF.FileName LIKE ‘beremote.exe’
ORDER BY SYS.Netbios_Name0
ConfigMgr 2007, FEP, SQL | admin | 
February 14, 2011 22:30 | 
Comments (0) 
Subscribe 