Show
Applies to:  This topic covers how to monitor SQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file. Monitor log space useMonitor log space use by using sys.dm_db_log_space_usage. This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation. For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.
Important Avoid overloading the log disk. Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load. Shrink log file sizeTo reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space. You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. In some cases, shrinking the log may not be possible until after the next log truncation.
Note Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. For information, see Factors that can delay log truncation. Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.
Important Before shrinking the transaction log, keep in mind Factors that can delay log truncation. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. For more information, see the Recommendations in this topic. Shrink a log file (without shrinking database files)
Monitor log-file shrink events
Monitor log space Add or enlarge a log fileYou can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.
For more information, see the Recommendations in this topic. Optimize tempdb transaction log sizeRestarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. This can reduce the performance of the tempdb transaction log. You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. For more information, see tempdb Database. Control transaction log file growthUse the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Note the following:
For more information, see the Recommendations in this topic. RecommendationsFollowing are some general recommendations when you are working with transaction log files:
See alsoBACKUP (Transact-SQL)
A common theme identified by the Australian Cyber Security Centre (ACSC) while performing investigations is that organisations have insufficient visibility of activity occurring on their workstations and servers. Good visibility of what is happening in an organisation’s environment is essential for conducting an effective investigation. It also aids incident response efforts by providing critical insights into the events relating to a cyber security incident and reduces the overall cost of responding to them. This publication has been developed as a guide to the setup and configuration of Windows event logging and forwarding. This advice has been developed to support both the detection and investigation of malicious activity by providing an ideal balance between the collection of important events and management of data volumes. This advice is also designed to complement existing host-based intrusion detection and prevention systems. This publication is intended for information technology and information security professionals. It covers the types of events which can be generated and an assessment of their relative value, centralised collection of event logs, the retention of event logs, and recommended Group Policy settings along with implementation notes. This publication does not contain detailed information about analysing event logs. Accompanying this publication is the ACSC’s Windows Event Logging Repository. The repository contains configuration files and scripts to implement the recommendations in this publication. All files and folders referred to in this publication are available from this repository. ConsiderationsThis publication’s recommendations require the use of Microsoft Windows Server 2008 R2 and Microsoft Windows 7 SP1, or newer versions. Some Group Policy settings used in this publication may not be available or compatible with Professional, Home or S editions of Windows. To enable accurate correlation of events, accurate and consistent time stamps must be used. Organisations are recommended to ensure all devices in their environment (e.g. Windows hosts and network equipment) are configured to use an accurate time source. As detailed in the Strategies to Mitigate Cyber Security Incidents, the recommended event log retention time is at least 18 months; however, some organisations may have a regulatory requirement to retain event logs for a longer period. To assist with the management of recommendations in this publication, the Group Policy settings discussed should be placed in a separate Group Policy Object (GPO) with the scope set for all Windows hosts on the domain. All changes made to systems should be fully tested to ensure there are no unintended side effects to an organisation’s normal business processes. Testing should focus on the volume of logging generated and any impact on the network’s performance, particularly where information may be transmitted across low bandwidth connections. The recommended Group Policy settings in this publication use advanced audit policies which may override existing legacy audit policies. Care should be taken to ensure that existing legacy audit policies are migrated to advanced audit policies. System Monitor (Sysmon), a tool published by Microsoft, provides greater visibility of system activity on a Windows host than standard Windows logging. Organisations are recommended to use this tool in their Windows environment. Event log retentionThe Windows default settings have log sizes set to a relatively small size and will overwrite events as the log reaches its maximum size. This introduces risk as important events could be quickly overwritten. To reduce this risk, the Security log size needs to be increased from its default size of 20 MB. The Application and System log sizes should also be increased, but typically these do not contain as much data and hence do not need to be as large as the Security log. The default log sizes are acceptable in environments where local storage is limited (e.g. virtual infrastructure environments) provided logs are being forwarded. The Group Policy settings provided in the table below will increase the maximum Security log size to 2 GB and the maximum Application and System log sizes to 64 MB. This will provide a balance between data usage, local log retention and performance when analysing local event logs. Note that these changes will increase the data storage requirements for each Windows host on the network.
Event categoriesThe default Windows settings provide only a subset of the desired logging events that assist in detecting and investigating malicious activity. This section covers the event categories that will significantly enhance technical analysis. Each event category can be deployed independently and categories in the table below are ordered by the usefulness of the data source for detection and investigation. In general, most event categories are highly recommended. The list is not exhaustive and organisations should include additional event logs specific to their auditing requirements. Each of the event categories below are accompanied by supplied subscription files. The subscriptions are used by Windows Event Forwarding to forward the locally generated events while filtering out the less valuable events.
Event category configurationSysmonSysmon records key events that will assist in an investigation of malware or the misuse of native Windows tools. These events include process creation and termination, driver and library loads, network connections, file creation, registry changes, process injection, named pipe usage and WMI-based persistence. Sysmon also supports filtering of events to keep logging at a manageable level. The Sysmon configuration file defines what events will be recorded. A default Sysmon configuration file is supplied in events/sysmon/sysmon_config.xml and should be suitable for most environments. To further filter or control events that are forwarded, the Sysmon configuration may be customised and Sysmon subscriptions may be enabled or disabled. As with all software, Sysmon should be installed by following the agreed software deployment practices for the network. Sysmon can be deployed by Group Policy settings or the System Centre Configuration Manager (SCCM). No other Group Policy setting changes are necessary as all Sysmon’s configuration information is contained in the configuration file. Guidance on the creation of an installation file (i.e. MSI file) that may simplify the deployment of Sysmon is supplied in events/sysmon/msi/README.txt. Alternatively, the following commands can be used to maintain Sysmon from a script or command line tool:
The end-user license agreement must be accepted before using Sysmon. Account lockoutThe following Group Policy setting can be implemented to record events related to accounts being locked and unlocked.
Account modificationsThe following Group Policy settings can be implemented to record events related to account creation or deletion, as well as modifications to account groups.
Event collectionThis event category records and forwards auditing policy changes, when event logs are cleared and failures with event logging. Many of these events are recorded by default, but the following Group Policy settings further increase visibility. The subscription will forward, if possible, warnings and errors resulting from problems with Windows Event Forwarding. These logs can detect errors related to incorrectly formed subscriptions and can assist with debugging.
Account logonThe following Group Policy settings can be implemented to record logon and logoff events including interactive logons, network logons and logons using explicit credentials. The subscription will not forward Kerberos logon events which produce a high level of noise on a typical network. This may obscure the misuse of Kerberos tickets; however, this information will still be available on each local machine.
Process trackingThe following Group Policy settings can be implemented to record process creation and termination events. Organisations are recommended to collect this information through Sysmon. If Sysmon can’t be used, process tracking events can be collected through this native Windows logging. It is important to increase the value of the process creation events by including command line arguments with process creation events. This feature is enabled for Microsoft Windows 8.1 and Microsoft Windows Server 2012 R2, and newer versions. For earlier versions of Windows, an update is available. For more information see Microsoft Security Advisory 3004375 and Update to improve Windows command-line auditing.
Microsoft AppLockerThis event category will forward audit or deny events from Microsoft AppLocker. Microsoft AppLocker must be configured in either auditing or enforcement mode for events to be generated. For more information, see the Hardening Microsoft Windows 10 version 21H1 Workstations publication and the Implementing Application Control publication. If third party application control is used, follow the documentation to enable and forward logging. At a minimum, blocked execution events should be logged. ServicesThis event category will forward events when services have been installed. It does not require any change to Group Policy settings. This category will also forward events related to the event log service being shut down. Microsoft DefenderThis event category will forward configuration changes, update issues and malware detected by Microsoft Defender Antivirus. If third-party antivirus software is used, the vendor’s documentation should be followed to enable and forward logging to a central location. At a minimum, configuration changes, update issues and malware detection events should be logged and forwarded. Microsoft Defender Exploit Guard has been available since Microsoft Windows 10 version 1709, and this event category will forward exploit mitigations being applied. Audit mode events can also be forwarded by enabling the supplied audit subscription. Microsoft Defender Exploit Protection, which is a component of Microsoft Defender Exploit Guard, will still run if third-party antivirus software is used. Exploit Protection is enabled by default and can be configured as required. Events from the Microsoft Defender Exploit Guard components, Attack Surface Reduction, Network Protection and Controlled Folder Access require the Microsoft Defender Antivirus real-time antivirus scanning engine to be enabled. Windows Error ReportingThis event category will forward application crashes and it does not require any change to Group Policy settings. Code integrityThis event category will forward code integrity violations, and the following Group Policy settings will increase integrity logging. Recorded events include unsigned or untrusted drivers and protected processes attempting to load untrusted code. When Windows Defender Application Control (WDAC) is configured, events will be generated for code integrity violations against a defined list of trusted executable hashes and signatures. Audit mode events can also be forwarded by the supplied subscription. For further information, see Microsoft’s Deploying Windows Defender Application Control (WDAC) policies guide.
File sharesThe following Group Policy settings can be implemented to record events for file share creation, modification and access.
Scheduled tasksThe following Group Policy setting can be implemented to record events associated with scheduled tasks being registered, modified or disabled. The subscription will not forward common task modification events.
Windows Management Instrumentation auditingWindows Management Instrumentation (WMI) auditing, like file and registry auditing, is native to Windows and provides visibility of WMI activity on a Windows host. The following Group Policy settings can be implemented to record events from sensitive WMI paths including local and remote activity. Setting auditing records (System Access Control Lists (SACLs)) on WMI nodes can’t be done directly through Group Policy settings. Instead, this can be achieved by using the supplied PowerShell script events/wmi_auditing/wmi_auditing.ps1 and through the respective Group Policy setting below, which will configure it to run on host startup. This script can also be deployed through software deployment services such as System Centre Configuration Manager (SCCM). Microsoft Windows 10 version 1607 and newer includes WMI persistence logging by default. This feature is almost identical to Sysmon’s WMI logging.
NTLM authenticationThe following Group Policy settings will log events for outgoing NTLM authentication, which can be vulnerable to relay and brute force attacks. The events generated include information on the user, process responsible and target server. To reduce logging if NTLM is commonly used on the domain (e.g. by servers that require proxy authentication) you can specify servers to be exempt from auditing. Although the NTLM protocol has weaknesses, disabling NTLM is not recommended on a typical network.
Object access auditingMicrosoft Windows 10 and Microsoft Windows Server 2016 have a default SACL on the Local Security Authority Subsystem Service (LSASS) process. With kernel object access auditing enabled by the respective Group Policy settings below, this will record read and write access to the memory of LSASS and is valuable in detecting malicious activity such as credential theft. Sysmon contains the Process Access event, which can detect this activity on earlier versions of Windows. Windows also has registry keys and file paths for a number of pre-existing SACLs which can be logged if the respective Group Policy settings below are enabled. These can be valuable, but some may cause a significant number of low-value events to be created. To reduce the amount of data to a manageable level, the subscription will not forward object access auditing from the System, Local Service and Network Service accounts. It is possible to define registry keys and file paths to be audited through Group Policy settings. The value of this is reduced as it can be difficult to define and maintain rules and it may introduce security flaws by defining incorrect permissions. Given these potential issues, the Sysmon file creation and registry auditing features are preferred. The following Group Policy settings can be implemented to record auditing policy changes, kernel object auditing and optionally file system and registry auditing.
PowerShell loggingThis event category will forward PowerShell engine start events, and with the following Group Policy settings implemented it will forward detailed logging of PowerShell scripts and interactive access. It may produce an excessive level of noise if large PowerShell scripts are used frequently within the environment and it is recommended that testing is conducted before it is deployed across the enterprise. For information on securing and logging using PowerShell, see the Securing PowerShell in the Enterprise publication. The Turn on PowerShell Script Block Logging Group Policy setting requires PowerShell version 5.0 or above to be installed. A known bypass for this feature is to downgrade to an older version of PowerShell. Organisations are recommended to uninstall or restrict access to older versions of PowerShell where possible. The following Group Policy settings can be implemented to enable the PowerShell Script Block Tracing feature in PowerShell version 5 or above. If the Group Policy settings are not visible, this requires the Group Policy administrative templates be updated. Alternatively, organisations can follow the registry method contained in Appendix C of the Securing PowerShell in the Enterprise publication.
Event forwardingWindows has the native ability, known as Windows Event Forwarding (WEF), to forward events from Windows hosts on the network to a log collection server. WEF can operate either via a push method or a pull method. This publication uses Microsoft’s recommended push method of sending events to the log collection server. Subscriptions are added to determine which events are to be transferred, the source hosts and how frequently they are transferred. From the log collection server, events may be forwarded to a secure centralised logging capability such as a Security Information and Event Management (SIEM) system. This will enable centralised detection, correlation and discovery of cyber security incidents. This publication addresses the most common deployment scenarios; but there are many ways to achieve a similar result. These instructions primarily use the Windows Event user interface, but it is possible to achieve a similar outcome using the wevtutil and wecutil command-line utilities. To implement event forwarding, the following is required:
ScalabilityThe instructions provided in this publication are for a Windows domain with one log collection server. The Microsoft Use Windows Event Forwarding to help with intrusion detection article mentions that, as a general rule, a log collection server on commodity hardware should be limited to 10,000 Windows hosts and below a total of 10,000 events per second. To scale to multiple collection servers, the Group Policy settings can be modified to direct groups of Windows hosts to their closest available log collection server. These configurations need to consider the location of the collection server and bandwidth available from Windows hosts across Wide Area Network (WAN) links or remote access connections when forwarding event logs. Client configurationThe event forwarding client configuration adjusts the Windows Remote Management (WinRM) configuration, which Windows Event Forwarding relies upon, and specifies the log collection server. The following Group Policy settings should be defined in a separate GPO, with the scope set for all Windows hosts on the domain. In the case of multiple collection servers, GPOs need to be defined to direct the Windows hosts to their respective log collection server (Subscription Manager). To allow event log files to be read by the forwarding service, the Event Log Readers group needs to be modified. This configuration does not take effect until the Windows Event Collector service is restarted. To restart the service, the Windows Event Collector service type needs to be set to start in a separate process, and then the service needs to be restarted. This can be achieved by running the below command on each Windows host. sc config wecsvc type=own && sc stop wecsvc && sc start wecsvc Alternatively, restarting each Windows host will achieve the same result. Failure to do either of these will result in the Security and Sysmon logs not being forwarded and error events will be generated (i.e. Event ID 102 from the log Microsoft-Windows-Forwarding/Operational). Forwarding will use global proxy settings on clients if enabled. The log collection server may need to be added to the proxy exclusion list unless this is required.
Server configurationThe log collection server requires the Windows Event Collector service to be running, WinRM to be setup as a server and the firewall to be configured appropriately. This is implemented by the following Group Policy settings which should be applied to the log collection servers as a separate GPO.
Setting forwarded log sizeTo set forwarding log sizes:
Adding subscriptionsTo collect each event category, a relevant subscription needs to be added and enabled. The subscriptions contain query filters that forward events of potential interest. In some cases query filters are based on full paths and these would need to be modified if non-standard paths or drives are used. To add subscriptions:
The default configuration should now be loaded and computer groups need to be added to enable the subscriptions on the domain. Typically, this would include both the Domain Computers and Domain Controllers groups. This can be customised to include or exclude specific computers or groups. To subscribe the Domain Computers and Domain Controllers groups to all subscriptions:
If desired, the source hosts or computer groups for a specific subscription can be edited:
To speed up the testing of subscriptions changes you can force hosts to perform a Group Policy update by running gpupdate /force on Windows hosts that are forwarding events. Subscriptions can also be viewed and edited using the Event Viewer (eventvwr.msc) interface. This includes enabling or disabling subscriptions, or updating filters. By default, the subscriptions are enabled to read existing events in the log archive. This may cause a higher than average number of events to be forwarded and place additional load on the network where Windows hosts are forwarding events for the first time. The ReadExistingEvents subscription setting can be modified for each subscription to enable or disable the forwarding of previous events by using the command-line utility wecutil. Verification and debuggingTo verify that event logs are being forwarded to the log collection server:
Alternatively you can view which hosts are sending data per subscription:
To diagnose potential errors, the event collection server has the EventCollector log (Microsoft-Windows-EventCollector/Operational) and the clients have the Eventlog-ForwardingPlugin log (Microsoft-Windows-Forwarding/Operational). These logs are forwarded where possible and can also be accessed using the Event Viewer (eventvwr.msc) and navigating to Applications and ServicesLogs/Microsoft/Windows. ArchivingEvents should be archived if they are not going to be forwarded to a secure centralised logging facility. Regular backups of the event collection server’s archived logs can help mitigate the risk of data loss. To ensure all forwarded events are archived on the event collection server:
An alternative log path may optionally be set. This is useful in situations where log files are being stored on a separate high capacity drive. The path must first have an access control list defined on the folder to match the permissions on the default Windows event log path, as listed below:
To set the Forwarded Events log to use the alternative path:
Organisations must appropriately secure their Windows event log archives to ensure only authorised users and services are able to access these files. Unauthorised access to these files could provide an adversary with sensitive information or an opportunity to remove or tamper with event logs. When the ForwardedEvents log is full, archive files will be created. This should occur when they are approximately 2 GB. By default, this will be in %SystemRoot%\System32\winevt\Logs and will have a format similar to Archive-ForwardedEvents-2016-05-18-05-23-46-723. Over time archive logs will be created and not overwritten or deleted. Adequate disk space needs to be allocated to the server and disk usage should be monitored. It is recommended that a procedure is created to backup or move archived logs on a regular basis, or when the disk is reaching capacity. Further informationThe Information Security Manual is a cyber security framework that organisations can apply to protect their systems and data from cyber threats. The advice in the Strategies to Mitigate Cyber Security Incidents, along with its Essential Eight, complements this framework. Further information on logging when using application control is available in the Implementing Application Control publication. Further information on logging and securing PowerShell is available in the Securing PowerShell in the Enterprise publication. Further information on hardening and logging for Microsoft Windows workstations is available in the Hardening Microsoft Windows 10 version 21H1 Workstations publication. External references and further reading about Windows event logging and forwarding can be found at: Contact detailsIf you have any questions regarding this guidance you can write to us or call us on 1300 CYBER1 (1300 292 371). |
What command would you use at the end of a period of logging to ensure that the logs were closed out properly and archived safely choose the best answer?
zusammenhängende Posts
Urheberrechte © © 2024 frojeostern Inc.
