#Get-winevent in powershell

[verside]

PowerShell to find out when a computer was last shutdown cleanly and why. Example is from AVD auto shutdown.

get-winevent -FilterHashtable @{ ProviderName = 'user32' ; id =1074 } -MaxEvents 1|ft -wrap

[dotnet-helpers-blog]

How to Use PowerShell to Detect Logins and Alert Through Email using SendGrid

How to Use PowerShell to Detect Logins and Alert Through Email using SendGrid

From Microsoft MSDN, The Get-WinEvent data from event logs that are generated by the Windows Event Log technology introduced in Windows Vista.And, events in log files generated by Event Tracing for Windows (ETW).By default, Get-WinEvent returns event information in the order of newest to oldest.

Get-winevent: Gets events from event logs and event tracing log files on local and remote computers.…

[hunterpro283]

Convert Evtx File To Text

Convert Evtx File To Text Excel

Convert Evtx File To Xml

I’ve been doing IR for a long time and I can’t believe I have only now discovered the power of LogParser. Perhaps I was too spoiled by Splunk to actually be forced to learn this awesome tool. But now that I have gotten familiar with it, I see why it is so beloved. It’s powerful and SQL-friendly command line capabilities give it a ton of flexibility and provide lots of opportunity for automation. While getting acquainted with it, and wanting to document my learning, I decided to create some batch files which capture syntax and intent.

Background

Need a way to convert multiple.EVTX files to.CSV format. Need to search about 50+ evtx files from our archieve.

Convert Evtx File To Text File / Windows Seven netsh, trace, convert, cmd, command, Windows, Seven Quick - Link: netsh winhttp help netsh interface tcp add chimneyapplication netsh trace show provider netsh interface isatap show state netsh rpc dump netsh interface ipv6 set route. EVTX file: Windows 7 Event Log. Read here what the EVTX file is, and what application you need to open or convert it. If you are seeking information about file extensions. Windows Event files were classified as.evt files up until Windows XP. Windows Vista and newer started calling event log files.evtx. You can convert between the two!:) (this is a lot like.doc vs.docx in MS Word between the Office '03 and '07 releases) EDIT TO FOLLOW YOUR EDIT. The problem is in converting the binary XML.evtx files to csv. All the existing libraries and tools I have ever tried only break out the major fields in the event logs, leaving all the fields in the “Message” part of the events lumped together. All the good stuff I want to filter and search on is in locked away in there!

LogParser.exe has been around a long time. Version 2.2 was released around 2006 and there are a few GUI front-ends available (e.g. LogParser Lizard and Log Parser Studio). A quick google search suggests it is more popular among IIS log searchers than EVT(X) uses.

Goal 1. Converting EVTX to CSV

I am often handed a set of IR triage artifacts that includes a file system containing event log files in EVTX format. This binary format is truly unfriendly and neither Excel, nor Splunk can work with it. However, LogParser can! If this were all it could do, it woudn’t be worth mentioning since there are Powershell options to do this as well:

get-winevent -path .filescwindowssystem32winevtlogs*.evtx| export-csv FileName.csv -useculture

To quote on Redditor (’13cubed’): “While you can certainly obtain logs with Get-WinEvent, Log Parser can query just about any text-based data source, not just logs. It is more scalable, and allows for fast searches of massive amounts of data allowing you to filter on a wide variety of things, such as event ID’s, usernames, IP addresses, and more.”

Since I wanted to learn LogParser anyway, I figured it would be helpful to figure this out for starters.

LogParser doesn’t work well with pipes (e.g. logparser.exe > eventlog.csv). Instead, since it uses SQL-like syntax. You have to “INSERT INTO” the location you want to export to. The following syntax works well for “point and shoot” batch-file double-clicking at the root of a mounted directory of artifacts.

logparser.exe “select * INTO Security.csv from ‘.cwindowssystem32winevtlogsSecurity.evtx'” -i:EVT -headers:ON

A batch file to pull only to the log files mentioned in the SANS poster and JP Cert paper (see Goal 3) can be found here.

Now that I have CSVs I can use grep, Splunk, ELK or Excel to do further analysis. But I want to be able to do blue-team work even when my fancy analytics tools aren’t available.

Goal 2. Push Button Event Log Triage

We are all busy. Even if we have the appetite to trawl through thousands of logs manually, if we can speed up the identification of weird/suspicious events, we can apply our brain power elsewhere and be more efficient. I wanted a quick way to summarize certain kinds of information in the logs such that an analyst could look at the output and more quickly identify things which may warrant a closer look.

Since LogParser seems to think in T-SQL, it is a great command line option for some simple data stacking (aka frequency analysis and anomaly detection). I created a set of queries which stack things like users, processes, services, scheduled tasks, domains, remote machines. I found a great resource with many examples of these commands at this github page and borrowed a lot of it making small tweaks here and there.

Since “pipes” don’t work, I had to figure out how to export/append the results to a single file for quick review by an analyst. Adding “INTO exportfile.txt” before “FROM” in the SQL gets the export done, but the append operation also requires ” -filemode:0″ at the end of each query. I chose to name my export file “WELDS.txt” as a corny acronym for “Windows Event Log Data Summaries.”

These queries dump numerous histogram-like count summaries of interesting data elements. It may be helpful to search at the lower end of the frequency table to fin things which are relatively rare.

My favorite part of this script is the summary of process execution events where I have paired the parent process with the child process. Typically, Proc2 is the parent and Proc1 is the child.

LogParser.exe -stats:OFF -i:EVT “SELECT COUNT(*) AS CNT, EXTRACT_TOKEN(Strings, 5, ‘|’) AS Proc1, extract_token(strings, 13, ‘|’) as Proc2 INTO WELDS.txt FROM ‘.filescwindowssystem32winevtlogsSecurity.evtx’ WHERE EventID = 4688 AND (Proc1 LIKE ‘%.%’ AND Proc2 LIKE ‘%.%’) GROUP BY Proc1, Proc2 ORDER BY CNT ASC”

The results are found near the end of the WELDS.txt file. In the absence of EDR or a memory capture, this can be very helpful in determining strange processes relationships (e.g. we would not want to see cmd.exe starting iexplore.exe).

Goal 3. Know Normal, Find Evil

While there are seemingly endless ways to “find evil” SANS has provided us with a “greatest hits” of suspicious event IDs to pay close attention to in the form of the 2018 “Know Normal – Find Evil” poster. This is a quick reference for event logs, registry entries, and prefetch artifacts which incident responders can use to focus their first review of a suspect endpoint.

The Japanese CERT has also provided a wonderful paper on detecting lateral movement with similar artifacts.

The third batch file seeks to capture each of these pearls of wisdom in a “push-button” friendly way to cull the massive number of events in the evtx files down to only those which are highlighted in these two documents as likely to reveal suspicious activity. I made an attempt to ECHO helpful comments about what each query is doing. This script output is very verbose and most likely needs additional tuning to make it worth while. However, it’s a handy quick reference you can copy/paste from to target specific EventIDs of interest when responding to a suspected compromise.

My final batch file was inspired by the SANS DFIR Summit presentation on AppCompatProcessor. Among many other promising things (e.g. advance statistical anomaly detection), this tool uses a list of “recon” strings to identify clusters of commands which are more likely to be indicative of an adversary performing recon on the machine or network in search of additional opportunities. Commands such as net.exe, whoami.exe, ping.exe, etc are collected and displayed in timeline format.

That’s all for now. Hopefully, this shows you the power of LogParser and gives some ideas on how it can be used to quickly triage evidence in incident response.

P.S. this is a small taste of the kind of information I’ll be teaching at the SANS FOR508 Class starting in Richmond, VA on March 6th. Details here: https://www.linkedin.com/feed/update/urn:li:activity:6483781362825392128/

File typeMicrosoft Windows Vista, Windows 7, Windows 8 Event Log FormatDeveloperMicrosoftAds

How to open EVTX files

If you cannot open the EVTX file on your computer - there may be several reasons. The first and most important reason (the most common) is the lack of a suitable software that supports EVTX among those that are installed on your device.

A very simple way to solve this problem is to find and download the appropriate application. The first part of the task has already been done – the software supporting the EVTX file can be found in the table. Now just download and install the appropriate application.

Program(s) that can open the .EVTX file

Windows

Possible problems with the EVTX format files

The inability to open and operate the EVTX file does not necessarily mean that you do not have an appropriate software installed on your computer. There may be other problems that also block our ability to operate the Microsoft Windows Vista, Windows 7, Windows 8 Event Log Format file. Below is a list of possible problems.

Convert Evtx File To Text Excel

Corruption of a EVTX file which is being opened

Incorrect links to the EVTX file in registry entries.

Accidental deletion of the description of the EVTX from the Windows registry

Incomplete installation of an application that supports the EVTX format

The EVTX file which is being opened is infected with an undesirable malware.

The computer does not have enough hardware resources to cope with the opening of the EVTX file.

Drivers of equipment used by the computer to open a EVTX file are out of date.

If you are sure that all of these reasons do not exist in your case (or have already been eliminated), the EVTX file should operate with your programs without any problem. If the problem with the EVTX file has not been solved, it may be due to the fact that in this case there is also another rare problem with the EVTX file. In this case, the only you can do is to ask for assistance of a professional staff.

Similar extensions

.admAdministrator Policy Template Format.admlMicrosoft Administrative Language-specific XML Template Format.admxMicrosoft Administrative XML Template Format.amlMicrosoft Assistance Markup Language.aniAnimated Cursor.annMicrosoft Windows Help Annotation Format.aosArchos Signed Encrypted Data Format.asecGoogle Android Encrypted Application Package Format

How to associate the file with an installed software?

If you want to associate a file with a new program (e.g. my-file.EVTX) you have two ways to do it. The first and the easiest one is to right-click on the selected EVTX file. From the drop-down menu select 'Choose default program', then click 'Browse' and find the desired program. The whole operation must be confirmed by clicking OK. The second and more difficult to do is associate the EVTX file extension to the corresponding software in the Windows Registry.

Is there one way to open unknown files?

Convert Evtx File To Xml

Many files contain only simple text data. It is possible that while opening unknown files (e.g. EVTX) with a simple text editor like Windows Notepad will allow us to see some of the data encoded in the file. This method allows you to preview the contents of many files, but probably not in such a structure as a program dedicated to support them.

[enterinit]

Microsoft Endpoint Configuration Manager current branch Update 1910

Microsoft Endpoint Configuration Manager current branch Update 1910. NOTE: In Windows 10, when you open the Start menu, just start typing the name to find the icon. For example, config for the Configuration Manager console, and software for Software Center. CMPivot now works better together with Microsoft Defender Advanced Threat Protection (ATP) software, by linking the CMPivot output with relevant ATP details. The performance of CMPivot has been improved by offloading querying to the client to reduce network traffic and load on the servers. You now have the ability to run queries just locally on “This PC”, for WMI related data. Running on “This PC” saves the need to use the Configuration Manager infrastructure at all and returns data faster, so you can pivot and hone your query to be precisely what you want, before you consume network bandwidth resources. This aids in writing the correct query. We have added joins and more operators (+,-,*,/,%) and exposed file hashes (MD5 and SHA256) to find files masquerading as others. To make sharing queries easier, we have added a query shortcuts feature, that lets you copy & paste the query to a clipboard and send it via email to collaborators. When the collaborator clicks the link to the query, it will auto-launch CMPivot standalone and provide the same query for them to run.

Real-time management

Optimizations to the CMPivot engine We've added some significant optimizations to the CMPivot engine that allows us to push more of the processing to the ConfigMgr client. The optimizations drastically reduce the network and server CPU load needed to run CMPivot queries. With these optimizations, we can now sift through gigabytes of client data in real time. Additional CMPivot Entities and Enhancements We've added a number of new CMPivot entities and entity enhancements to aid in troubleshooting and hunting. We've included the following entities to query: Windows event logs (WinEvent)File content (FileContent)Dlls loaded by processes (ProcessModule)Azure Active Directory information (AADStatus)Endpoint protection status (EPStatus) Microsoft Connected Cache support for Intune Win32 apps When you enable Microsoft Connected Cache on your Configuration Manager distribution points, they can now serve Microsoft Intune Win32 apps to co-managed clients. NOTE: Configuration Manager current branch version 1906 included Delivery Optimization In-Network Cache (DOINC), an application installed on Windows Server that's still in development. Starting in current branch version 1910, this feature is now called Microsoft Connected Cache. When you install Connected Cache on a Configuration Manager distribution point, it offloads Delivery Optimization service traffic to local sources. Connected Cache does this behavior by efficientl caching content at the byte range level.

Desktop Analytics

Support for Desktop Analytics - This release provides support for Desktop Analytics which is now generally available. Desktop Analytics provides the insight and automation you need to efficiently get current and stay current with Windows. By integrating with Configuration Manager, Desktop Analytics adds cloud value to your on-premises infrastructure.

Site infrastructure

Reclaim SEDO lock - Starting in current branch version 1906, you could clear your lock on a task sequence. Now you can clear your lock on any object in the Configuration Manager console.Extend and Migrate on-premises Configuration Manager environment to Microsoft Azure - This new tool helps you to programmatically create Azure virtual machines (VMs) for Configuration Manager. It can install with default settings site roles like a passive site server, management points, and distribution points. Once you validate the new roles, use them as additional site systems for high availability. You can also remove the on-premises site system role and only keep the Azure VM role

Client Management

Include custom configuration baselines as part of compliance policy assessment - You can now add evaluation of custom configuration baselines as a compliance policy assessment rule. When you create or edit a configuration baseline, you have an option to Evaluate this baseline as part of compliance policy assessment. When adding or editing a compliance policy rule, you have a condition called Include configured baselines in compliance policy assessment.Enable user policy for Windows 10 Enterprise multi-session – Configuration Manager current branch version 1906 introduced support for Windows Virtual Desktop. In this release if you require user policy on these multi-session devices, and accept any potential performance impact, you can now configure a client setting to enable user policy.

Application Management

Deploy Microsoft Edge, version 77 and later - The all-new Microsoft Edge is ready for business. You can now deploy Microsoft Edge, version 77 and later to your users. Admins can pick the Beta or Dev channel, along with a version of the Microsoft Edge client to deploy.Improvements to application groups – This release includes the following improvements: Users can Uninstall the app group in Software Center.You can deploy an app group to a user collection.

Operating System Deployment

Task sequence performance improvements - power plans - You can now run a task sequence with the high performance power plan. This option improves the overall speed of the task sequence.Task sequence download on demand over the internet – Starting in this release, the task sequence engine can download packages on-demand from a content-enabled CMG or a cloud distribution point. This change provides additional flexibility with your Windows 10 in-place upgrade deployments to internet-based device.Improvements to the task sequence editor You can now search in the task sequence editor. This action lets you more quickly locate steps in the task sequence.If you want to reuse the conditions from one task sequence step to another, you can now copy and paste conditions in the task sequence editor. Improvements to OSDBoot image keyboard layoutImport a single index of an OS upgrade packageOutput the results of a Run Command Line step to a variable during a task sequenceImprovements to task sequence debuggerImproved language support in task sequence Improved language support in task sequence This release adds control over language configuration during OS deployment. If you're already applying these language settings, this change can help you simplify your OS deployment task sequence. Instead of using multiple steps per language or separate scripts, use one instance per language of the built-in Apply Windows Settings step with a condition for that language. Use the Apply Windows Settings task sequence step to configure the following new settings: Input locale (default keyboard layout)System localeUI languageUI language fallbackUser locale New variable for Windows 10 in-place upgrade To address timing issues with the Window 10 in-place upgrade task sequence on high performance devices when Windows setup is complete, you can now set a new task sequence variable SetupCompletePause. When you assign a value in seconds to this variable, the Windows setup process delays that amount of time before it starts the task sequence. This timeout provides the Configuration Manager client additional time to initialize.

Protection

Bitlocker Management (MBAM) - Configuration Manager now provides the following management capabilities for BitLocker Drive Encryption: Deploy the BitLocker client to managed Windows devicesManage device encryption policesCompliance reportsAdministration and monitoring website for key recoveryA user self-service portal

Software updates

Additional options for third-party update catalogs - You now have more granular controls over synchronization of third-party updates catalogs. Starting in Configuration Manager version 1910, you can configure the synchronization schedule for each catalog independently. When using catalogs that include categorized updates, you can configure synchronization to include only specific categories of updates to avoid synchronizing the entire catalog.Use Delivery Optimization for all Windows updates - Previously, Delivery Optimization could be leveraged only for express updates. With Configuration Manager version 1910, it’s now possible to use Delivery Optimization for the distribution of all Windows Update content for clients running Windows 10 version 1709 or later.Additional software update filter for ADRs - You can now use Deployed as an update filter for your automatic deployment rules. This filter helps identify new updates that may need to be deployed to your pilot or test collections.

Office Management

Office 365 ProPlus Pilot and Health Dashboard - The Office 365 ProPlus Pilot and Health Dashboard helps you plan, pilot, and perform your Office 365 ProPlus deployment. The dashboard provides health insights for devices with Office 365 ProPlus to help identify possible issues that may affect your deployment plans.

Configuration Manager Console

View active consoles and message administrators through Console Connections – You now have the ability to message other Configuration Manager administrators through Microsoft Teams. Also, the Last Console Heartbeat column has replaced the Last Connected TimeClient diagnostics actions - You can now enable and disable verbose and debugging logging for the CCM component from the console.

Windows PowerShell MECM 1910

New cmdlets New-CMDuplicateHardwareIdGuid Use this cmdlet to add duplicate hardware identifiers by GUID. PowerShell New-CMDuplicateHardwareIdGuid -Id 24D0F753-B2E2-4D9C-B07C-099C4FC1EF3C New-CMDuplicateHardwareIdMacAddress Use this cmdlet to add duplicate hardware identifiers by MAC address. PowerShell New-CMDuplicateHardwareIdMacAddress -MacAddress 01:02:03:04:05:E0 New-CMThirdPartyUpdateCatalog Use this cmdlet to create a new third-party updates catalog. PowerShell New-CMThirdPartyUpdateCatalog -DownloadUrl $downloadUrl -PublisherName $publisher -Name $name -Description $description -SupportUrl $supportUrl -SupportContact $supportContact Get-CMThirdPartyUpdateCatalog Use this cmdlet to get a third-party updates catalog. PowerShell Get-CMThirdPartyUpdateCatalog Get-CMThirdPartyUpdateCatalog -Id $id Get-CMThirdPartyUpdateCatalog -Name $name Get-CMThirdPartyUpdateCatalog -SiteCode $siteCode Get-CMThirdPartyUpdateCatalog -IsSyncEnabled $true Get-CMThirdPartyUpdateCatalog -IsCustomCatalog $true Set-CMThirdPartyUpdateCatalog Use this cmdlet to modify a third-party updates catalog. PowerShell Set-CMThirdPartyUpdateCatalog -Name $name -NewName $newName Set-CMThirdPartyUpdateCatalog -ThirdPartyUpdateCatalog $catalog -Description $newdescription $catalog | Set-CMThirdPartyUpdateCatalog -SupportContact $newSupportContact -SupportUrl $newSupportUrl Remove-CMDuplicateHardwareIdGuid Use this cmdlet to remove duplicate hardware identifiers by GUID. PowerShell Remove-CMDuplicateHardwareIdGuid -Id 24D0F753-B2E2-4D9C-B07C-099C4FC1EF3C Remove-CMDuplicateHardwareIdGuid -InputObject $myGuid #() Remove-CMDuplicateHardwareIdMacAddress Use this cmdlet to remove duplicate hardware identifiers by MAC address. PowerShell Remove-CMDuplicateHardwareIdMacAddress -MacAddress 01:02:03:04:05:E0 Remove-CMDuplicateHardwareIdMacAddress -InputObject $myMacAddress #() Remove-CMThirdPartyUpdateCatalog Use this cmdlet to remove a third-party updates catalog. PowerShell Remove-CMThirdPartyUpdateCatalog -Id $catalog.ID -Force Remove-CMThirdPartyUpdateCatalog -Name $catalog.Name -Force Remove-CMThirdPartyUpdateCatalog -ThirdPartyUpdateCatalog $catalog -Force $catalog | Remove-CMThirdPartyUpdateCatalog -Force Removed cmdlets None Deprecated cmdlets The following cmdlets are deprecated with the end of hybrid service: Add-CMIntuneSubscriptionAdd-CMMdmEnrollmentManager (Add-CMIntuneDeviceEnrollmentManager)Export-CMWindowsEnrollmentProfileGet-CMConditionalAccessPolicy (Get-CMOnPremConditionalAccessPolicy)Get-CMCorpOwnedDeviceGet-CMDeviceActionState (Get-CMDeviceAction)Get-CMIntuneSubscriptionGet-CMIosEnrollmentProfileGet-CMMdmEnrollmentManager (Get-CMIntuneDeviceEnrollmentManager)Get-CMWindowsEnrollmentProfileGet-CMWindowsEnrollmentProfilePackageInvoke-CMDeviceAction New-CMApnsCertificateRequest New-CMConditionalAccessPolicy (New-CMOnPremConditionalAccessPolicy)New-CMDepTokenRequestNew-CMIosEnrollmentProfileNew-CMWindowsEnrollmentProfileRemove-CMConditionalAccessPolicy (Remove-CMOnPremConditionalAccessPolicy)Remove-CMCorpOwnedDeviceRemove-CMIntuneSubscriptionRemove-CMIosEnrollmentProfileRemove-CMMdmEnrollmentManager (Remove-CMIntuneDeviceEnrollmentManager)Remove-CMWindowsEnrollmentProfileRemove-CMWindowsEnrollmentProfilePackageSet-CMConditionalAccessPolicy (Set-CMOnPremConditionalAccessPolicy)Set-CMIntuneSubscriptionSet-CMIntuneSubscriptionAndroidProperty (Set-CMIntuneSubscriptionAndroidProperties)Set-CMIntuneSubscriptionAppleDepPropertySet-CMIntuneSubscriptionAppleProperty (aliases:)Set-CMIntuneSubscriptionMacOSPropertiesSet-CMIntuneSubscriptionIosPropertiesSet-CMIntuneSubscriptionMacOSPropertySet-CMIntuneSubscriptionIosPropertySet-CMIntuneSubscriptionAppleMdmPropertySet-CMIntuneSubscriptionPassportForWorkPropertySet-CMIntuneSubscriptionWindowsPhoneProperty (Set-CMIntuneSubscriptionWindowsPhoneProperties)Set-CMIntuneSubscriptionWindowsProperty (Set-CMIntuneSubscriptionWindowsProperties)Set-CMIosEnrollmentProfileSet-CMIosEnrollmentProfileAssignmentSet-CMWindowsEnrollmentProfile Read the full article

[marcosplavsczyk]

Database status overview

Every database in a SQL Server environment has two basic states: full availability (online state) or full unavailability (offline state).

SQL Server incorporates and utilizes seven possible database states, which are present in the grid below, ordered by availability (from fully available to fully unavailable), and with a short explanation for each state:

Database state Description Availability Online Database is functioning normally, and it is available for use. Available Restoring Database is in process of restoration, which means that user initiated the database restoring. Unavailable (without errors, user-induced) Recovering Database is in process of recovering. If succeed, it will change state to online. If process fails, it will change state to suspect. Unavailable (without errors) Recovery pending Recovery process failed in between, but database is not damaged. Further user action is required in order to solve the issue (see in the next paragraph). Unavailable (error occurred) Suspect In this state, there is possibility that database is or was damaged during the recover process. Further user action is required in order to solve the issue. Unavailable (error occurred) Emergency This database state change is user-induced, in order to safely perform maintenance, restore or recovering process on particular database. One note: sysadmin rights are required to manage this database state. Unavailable (without errors, user-induced) Offline Database is not functioning, and is unavailable for use. This state is also user-induced, and it requires further action, in order to change a database state. Unavailable (without errors, user-induced)

Quick reference when transition between database states is interrupted

There are several occasions when a smooth transition between database states could fail. Transitions from restoring, recovering or recovery pending database states to online state can be interrupted by events that stop previously active processes of database back up, restoring or recovery. These events could be disk failures, network connection issues, corrupted database files and other.

In order to solve these database states, perform actions shown below with caution, and with note that causes why interruptions happen can be various (already mentioned issues during the process of database restoration/recover etc.):

If the database is in a permanent restoring state: run this script, to force the recovering process and set database state to online:

RESTORE DATABASE < database_name > WITH RECOVERY

If the database is in a permanent recovering state:

stop SQL Server service;

move the log file for that database (usually in c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\<database_name>_log.ldf) to another place;

take problematic database offline;

move back the log file to its original folder and take database online.

If the database is in a permanent recovery pending state: take database offline, then online:

ALTER DATABASE < database_name > SET OFFLINE GO ALTER DATABASE < database_name > SET ONLINE GO

If needed, run this script if the database is in a suspect state:

EXEC sp_resetstatus < database_name > GO ALTER DATABASE < database_name > SET EMERGENCY GO DBCC CHECKDB (< database_name >) GO ALTER DATABASE < database_name > SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC CHECKDB ( < database_name >, REPAIR_ALLOW_DATA_LOSS ) GO ALTER DATABASE < database_name > SET MULTI_USER GO

Determine a database(s) status changes

Database status changes can be determined programmatically with PowerShell, by parsing events related to offline and online database(s) states from Application log within Event Viewer:

#The first part of the script is fetching events related to OFFLINE status; Get-WinEvent -FilterHashtable @{logname=’application’;id=5084;} | ?{$_.message -match "Setting database option OFFLINE"} -ErrorAction SilentlyContinue | Out-File d:\DatabaseStatusChange.txt -Append -Force ## #The second part of the script is fetching events related to ONLINE status; Get-WinEvent -FilterHashtable @{logname=’application’;id=5084;} | ?{$_.message -match "Setting database option ONLINE"} -ErrorAction SilentlyContinue | Out-File d:\DatabaseStatusChange.txt -Append -Force #After data fetching, all events will be parsed into one text file, and every next attempt of executing this script will be appended in the same text file;

The result should appear like this:

Within this filtered log file, moments when particular database went offline or online can be easily compared by timestamp (in TimeCreated column).

To constantly monitor database status change, include the script from above within SQL Server Agent job (refer to this article in order to create mentioned job), if needed.

Monitoring database status change with ApexSQL Monitor

As SQL Server performance monitoring tool, ApexSQL Monitor is continuously auditing status of all databases and their changes present on a monitored SQL server instance, with corresponding metric and alerts.

Information about database status change from Dashboard

On the left pane, when All instances is selected, the information on all databases status will appear in the Dashboard’s grid:

In this case, shown above, all databases are in function.

If some of the databases on the selected instance changed the status in some moment, the Databases counter on the top will show the corresponding number of alert(s), and alert icon will appear in the grid, like shown below:

Select the particular instance in the left pane, scroll down within Overview tab, and check all databases, for their status, database size, log size and related alerts:

In the picture above, there is an alert (or alerts) related to the Test database, which means that Test database’s status has changed.

Also, information about status and related alerts about databases are present in Database performance tab, along with information like recovery model, compatibility level etc.:

Status changed special performance counter

Placed under Database metrics, “Status changed” is the special performance counter, which actually tracks these events:

Status changed counter can be set to particular databases by clicking icon close to the metric name, and Database metric dialog will appear:

Resolving alerts related to Status changed counter

To examine and resolve alerts related to the database status change, go to the Alerts view:

In the General view, present on the picture above, it can be easily seen on which instance particular database (Test) changed its status. Also, the previously mentioned seven database statuses are present on the graph to show the transition between the states.

The selected alert represents the moment when Test database went offline, and the next shows when the same database changed status to online, with exact dates and times.

Automating alerts for database status changes

During monitoring of database status changes, to be effectively notified when a database changes status, set the email profile and/or use the custom command alert action, to make sure that particular database is always functioning.

Change the status of the particular database from offline to online (PowerShell)

In order to set this alert action, download Change_Database_Status.ps1 PowerShell script from this location, and place it on a desired location.

This script collects information on particular database’s status, and if the status is offline, the script will set it online. If it is already online (aka normal), the script will terminate.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') <server_name> $db = $s.Databases.item('<database_name>') $db.status if ($db.status -eq 'OFFLINE, AUTOCLOSED'){$db.SetOnline()} else {Break}

Next, customize the downloaded script, particularly <server_name> and <database_name> strings with valid and desired ones, bolded above.

After downloading and customizing the script file, set the custom command alert action within the Status Changed counter, and include this script:

powershell.exe "d:\change_database_status.ps1"

Every time the monitored database status is changed, the alert action will call the Change_Database_Status file and execute the script. Also, within alert action profile, multiple custom command alert actions can be included, just set different servers and databases in mentioned PowerShell script.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

The post How to monitor database status changes in SQL Server appeared first on Solution center.

[dotnet-helpers-blog]

How to Use PowerShell to Detect Logins and Alert Through Email using SendGrid

How to Use PowerShell to Detect Logins and Alert Through Email using SendGrid

From Microsoft MSDN, The Get-WinEvent data from event logs that are generated by the Windows Event Log technology introduced in Windows Vista.And, events in log files generated by Event Tracing for Windows (ETW).By default, Get-WinEvent returns event information in the order of newest to oldest.

Get-winevent: Gets events from event logs and event tracing log files on local and remote computers.…