Hello there, I hope someone is able to help. I have a problem I am trying to solve involving two sheets.

I have attached screenshots, which I hope will be useful in understanding what I am trying to achieve here. 

Take a look at the formula in H7 on the Void Tracker. It’s nearly there, but it isn’t quite working. What I’m trying to get it to do is to look at column B in the property list and find all the properties listed as Void for all the cells in Column D with the address “99 The Avenue” in column D and return all the relevant rooms in column E. So, what I want it to do, is return me a list of “Room 2 Room 3 Room 4” in Cell H7 of the void tracker, as those rooms are listed as Void in column B under that address. It should not return Room 1 as that is not listed as Void.

However, it isn’t doing what I want, as you can see. The cell value that it is retuning is E14 of the Property list, but it’s just retuning that cell value four times.

If anyone can help, I’d greatly appreciate it.

Many thanks.

​Hello there, I hope someone is able to help. I have a problem I am trying to solve involving two sheets.I have attached screenshots, which I hope will be useful in understanding what I am trying to achieve here. Take a look at the formula in H7 on the Void Tracker. It’s nearly there, but it isn’t quite working. What I’m trying to get it to do is to look at column B in the property list and find all the properties listed as Void for all the cells in Column D with the address “99 The Avenue” in column D and return all the relevant rooms in column E. So, what I want it to do, is return me a list of “Room 2 Room 3 Room 4” in Cell H7 of the void tracker, as those rooms are listed as Void in column B under that address. It should not return Room 1 as that is not listed as Void. However, it isn’t doing what I want, as you can see. The cell value that it is retuning is E14 of the Property list, but it’s just retuning that cell value four times.If anyone can help, I’d greatly appreciate it. Many thanks.   Read More

​

This is Part 2 of our Blog series on how to collect events using DCRs for advanced use cases. For Part 1, please check The power of Data Collection Rules: Collecting events for advanced use cases in Microsoft USOP – Microsoft Community Hub.

PowerShell is a great tool for administrators to manage devices and servers in their environment. When using it to administer remote systems with PSRemoting, you don’t leave credentials behind on the target systems – as opposed to RDP with which your credentials would be stored in the Local Security Authority (LSA). This provides many security benefits and helps prevent Pass-The-Hash attacks and other credential theft scenarios

Since it is a preinstalled tool, adversaries have been known to use PowerShell to attack organizations. Companies that have set up a robust PowerShell configuration and monitoring have a clear advantage against those adversaries! Thanks to PowerShell’s numerous built-in security and monitoring features, it is easy to detect and disrupt adversaries.

In this article we will look how you can set up your own monitoring mechanism to spot executed PowerShell code in your environment using Microsoft Sentinel and the Unified SecOps Platform. We will not discuss the various security features that can be configured for a robust PowerShell environment, there are other resources for it, as mentioned in our first article A good resource to learn more about PowerShell security is for example the book “PowerShell Automation and Scripting for Cybersecurity: Hacking and defense for red and blue teamers” written by Miriam C. Wiesner: https://aka.ms/mw-book.

Step 1: Configure ScriptBlockLogging

In PowerShell, you can imagine a script block as a collection of commands and expressions that are executed together as one command: the “script block”.

Many companies become aware to the importance of logging only after an incident has occurred. At that point, it is not possible to detect what happened. Therefore, the PowerShell team decided to implement basic script block logging, starting with PowerShell 5, which can be useful to trace malicious activities performed prior to the incident.

The basic script block logging feature only captures some basic security-relevant script blocks as the default if not configured otherwise. This way, in case of a security incident, you have insights of what basic malicious activities were executed on your machine, however this also means that with this default configuration not every activity is captured. Therefore, to keep track of all the activities on business-critical servers and high value assets, we need to configure script block logging before we can start collecting and reviewing all relevant events.

This can be done by using Group Policy. Depending on the PowerShell version for which you want to configure script block logging, navigate to the following GPO path(s):

Windows PowerShell: Computer Configuration > Policies > Administrative Templates > Windows Components > Windows PowerShell > Turn on PowerShell Script Block Logging
PowerShell Core: Computer Configuration > Administrative Templates > PowerShell Core > Turn on PowerShell Script Block Logging

Configure this Setting as “Enabled” and confirm with “OK”. Do not check the box to “Log script block invocation start / stop events” as this setting is verbose and would generate a lot of noise.

If you have both PowerShell versions in use in your environment, we recommend configuring and monitoring both.

In the PowerShell Core policy, you can find an option to “Use Windows PowerShell Policy Setting”. Unless you have a use case to have different configurations for both versions, you can simply enable this option to sync your Windows PowerShell script block settings.

Please note that the PowerShell Core administrative template files (*.admx)might need to be imported first before you can use them. This article points out how you can locate and install them: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_group_policy_settings

Step 2: Detect and review executed PowerShell code

Now that you have configured everything, it is time to collect PowerShell script block logging events and review the executed code.  The following script retrieves and filters event ID 4104 from both Windows PowerShell and PowerShell Core (excluding certain paths and user IDs) and then formats and displays the filtered event details :

$PSWinEventLog = @{ ProviderName = “Microsoft-Windows-PowerShell”; Id = 4104 }

$PSCoreEventLog = @{ ProviderName = “PowerShellCore”; Id = 4104 }

$PSWinEventLog, $PSCoreEventLog | ForEach-Object {
try {
Get-WinEvent -FilterHashtable $_ | Where-Object {
!($_.Properties[4].Value -Match “C:\ProgramData\Microsoft\Windows Defender Advanced Threat Protection\DataCollection\”) `
-and ($_.Properties[2].Value -ne “prompt”) `
-and ($_.UserId -ne “S-1-5-18”) `
-and ($_.UserId -ne “S-1-5-19”) `
-and !( $_.Properties[4].Value -Match “.vscode\extensions\”) `
-and !($_.Properties[4].Value -Match “C:\Windows\TEMP\SDIAG_([A-Za-z0-9]+(-[A-Za-z0-9]+)+)\CL_Utility.ps1”)
} | Select-Object TimeCreated, `
@{Name=’ExecutedCode’;Expression={ $_.Properties[2].Value }},`
UserId, `
LevelDisplayName, `
@{Name=’Path’;Expression={$_.Properties[4].Value}}, `
ProviderName, `
@{Name=’ScriptblockId’;Expression={$_.Properties[3].Value}},`
@{Name=’CurrentPart’;Expression={$_.Properties[0].Value}},`
@{Name=’TotalParts’;Expression={$_.Properties[1].Value}}`
| fl
}
catch {}

PowerShell script block events can be split into multiple events if the executed script block was too large. In that case, the “CurrentPart” field would indicate this by containing a value higher than 1. The “CurrentPart” field indicates the order of the script block pieces.

It is recommended to adjust this query to your environment; are there script blocks that are being run by certain programs in your environment that you can safely exclude from your review. You can use the following documentation as a reference for filtering: Creating Get-WinEvent queries with FilterHashtable – PowerShell | Microsoft Learn.

PowerShell is a great tool for administrators to manage devices and servers in their environment. When using it to administer remote

PowerShell script block events can be split into multiple events if the executed script block was too large. In that case, the “CurrentPart” field would indicate this by containing a value higher than 1. The “CurrentPart” field indicates the order of the script block pieces.

It is recommended to adjust this query to your environment; are there script blocks that are being run by certain programs in your environment that you can safely exclude from your review. You can use the following documentation as a reference for filtering: Creating Get-WinEvent queries with FilterHashtable – PowerShell | Microsoft Learn.

Step 3: Configure the data collection Rule (DCR) to collect the required events

Using the Azure Monitor agent (AMA), you can select the events you would like to collect from your servers using Xpath queries (please see Filter Windows events using Xpath queries for reference). If you are not familiar with the agent or you have not installed AMA yet in the servers you would like to monitor, please check the first article of this series: include link.

To create your DCR, as this time we are collecting non-Security events, go to Connectors and select Windows Forwarded Events. From here, select create DCR, add your servers, and under Collect select Custom. Paste the following xPath queries:

For Windows PowerShell:  

Microsoft-Windows-PowerShell/Operational!*[System[(EventID=4104)]]

For PowerShell Core:

PowerShellCore/Operational!*[System[(EventID=4104)]]

To prevent getting events that we don’t need (e.g. events from background processes or system accounts), based on the query we constructed in Step 2, we can create a transformation in our DCR that will prevent those events from being ingested into Microsoft Sentinel, as they could be too verbose and we may not need them. For this purpose, you can add this transformation in your DCR:

“transformKql”: “source | where SystemUserId !in (‘S-1-5-18’, ‘S-1-5-19’) | extend ScriptBlockText = parse_json(EventData).ScriptBlockText, ScriptBlockId = tostring(EventData.ScriptBlockId), MessageNumber = tostring(EventData.MessageNumber), MessageTotal = tostring(EventData.MessageTotal), Path = tostring(EventData.Path) | where tostring(ScriptBlockText) != ‘prompt’ | where Path != ‘.vscode\\extensions\\’ and Path != ‘C:\\Windows\\TEMP\\SDIAG_([A-Za-z0-9]+(-[A-Za-z0-9]+)+)\\CL_Utility.ps1’ and Path != ‘C:\\ProgramData\\Microsoft\\Windows Defender Advanced Threat Protection\\DataCollection\\'”

Alternatively, you can deploy this template from GitHub, which already includes the xPath queries described above, plus the transformation: Azure-Sentinel/DataConnectors/WindowsEvents/DataCollectionRulePowerShellEvents at master · Azure/Azure-Sentinel (github.com)

Step 4: Creating your detections

Now, we can go to Microsoft Sentinel or to the Unified security operations platform, which brings Microsoft Sentinel and Microsoft Defender XDR into a single unified portal (see how to Connect Microsoft Sentinel to Microsoft Defender XDR) and start querying your logs and generate detections.

It also makes sense to monitor for devices that never ran PowerShell code before, from which PowerShell code is executed unexpectedly. Are there devices that should never run PowerShell code (e.g. devices from Accounting or Marketing)? For this purpose, we could use a watchlist. Watchlists allow you to create a list of items you would like to use for correlation (e.g. high-value assets, terminated employees, service accounts, etc.). In our scenario, we have created a watchlist that determines to which team the machine belongs (Operations, Security, Marketing).

First, let’s have a look at our logs. On Microsoft Defender XDR, we can find them under Advanced Hunting. We are using this query to detect machines that are not part of the groups (Security and Operations) that we would expect to run PowerShell code using this query:

let AllowedGroups = dynamic([“Security”,”Operations”]);
WindowsEvent
| where EventID == 4104
| extend ScriptBlockText = parse_json(EventData).ScriptBlockText, ScriptBlockId = tostring(EventData.ScriptBlockId), MessageNumber = tostring(EventData.MessageNumber), MessageTotal = tostring(EventData.MessageTotal), Path = tostring(EventData.Path)
| lookup kind=inner _GetWatchlist(‘devicegroups’) on $left.Computer == $right.SearchKey
| where Group !in (AllowedGroups)
| project TimeGenerated, ScriptBlockText, SystemUserId, DeviceName, Group, EventLevelName, Path, MessageNumber, MessageTotal, ScriptBlockId, Channel
| sort by TimeGenerated, ScriptBlockId, MessageNumber

Above, we can see what machines that were not from the expected departments have ran PowerShell code.

After polishing our watchlist and query, we are ready to create a detection under Analytics:

Please remember to match the Host entity under “Entity mapping” when you create your analytic rule. This is critical for correlation across data sources and alerts.

This concludes Part 2 of our 3 Part blog series on how to collect events using DCRs for advanced use cases. For Part 1, please check The power of Data Collection Rules: Collecting events for advanced use cases in Microsoft USOP – Microsoft Community Hub, and stay tuned for Part 3, in which we show how to monitor for indications that Defender for Endpoint (MDE) was shut down.

We welcome your feedback and questions on this or any of the other parts of this blog article series and look forward to hearing from you.

Miriam Wiesner (@miriamxyra) – Senior Security Research PM for Microsoft Defender XDR | Maria de Sousa-Valadas Castaño – Senior Product Manager Unified SocOps Platform | Shirley Kochavi – Unified SocOps Platform

​Microsoft Tech Community – Latest Blogs –Read More 

Hi, I have a question about Manager’s team calendar and how it’s supposed to work. 

The manager would like to see all the details of the calendar events of who report to him, but currently by default the manager can only see “free/busy”.

Idea is to make the org hierarchy work by equal level people not see each other’s details, only free/busy, which works right now, but the higher level manager should see everyone’s details under him. Hierarchy is already set-up correctly.

Is that possible? If yes, how?

​Hi, I have a question about Manager’s team calendar and how it’s supposed to work.  The manager would like to see all the details of the calendar events of who report to him, but currently by default the manager can only see “free/busy”. Idea is to make the org hierarchy work by equal level people not see each other’s details, only free/busy, which works right now, but the higher level manager should see everyone’s details under him. Hierarchy is already set-up correctly. Is that possible? If yes, how?  Read More

​

Hello,

In the below, i’m trying to make a new column in the table to the right. This column needs to find the max value of Remaining Demand corresponding to the range in colum 2 (capacity %).

E.g, it looks at all the values in storage percent between 0% – 25% and tells me the maximum remaining demand value and plots it in the corresponding row.

I tried a few ways and can’t get it to work…

If I can do this, I can adjust supply rate to suit to get it to 0 demand as it would be telling me the max number I need to adjust between 0% – 25%. 

​Hello, In the below, i’m trying to make a new column in the table to the right. This column needs to find the max value of Remaining Demand corresponding to the range in colum 2 (capacity %). E.g, it looks at all the values in storage percent between 0% – 25% and tells me the maximum remaining demand value and plots it in the corresponding row. I tried a few ways and can’t get it to work… If I can do this, I can adjust supply rate to suit to get it to 0 demand as it would be telling me the max number I need to adjust between 0% – 25%.    Read More

​

Hi i will like to get some help connecting to SQL. I try to connect and get the message: A connection was sucssesfully established but then an error occurred during the login process.

thanks

​Hi i will like to get some help connecting to SQL. I try to connect and get the message: A connection was sucssesfully established but then an error occurred during the login process. thanks  Read More

​

Hi,

I’m querying the Vulnerability endpoint of the MDE API family.

I’m making asynchronous request at a high rate, up to 20 API calls per second. Naturally, I’m hitting the API Rate Limit by receiving a 429 status code. However, I can’t seem to be able to confirm exactly what the API Rate Limit is for this endpoint.

The only page that mentions Rate Limiting is the one here and it pertains to Incident API or Advanced Hunting API, but not to the Entity API Vulnerability.

Can someone please tell me what the API Rate Limit is for Entities under which Vulnerability falls?

Thank you.

​Hi, I’m querying the Vulnerability endpoint of the MDE API family.I’m making asynchronous request at a high rate, up to 20 API calls per second. Naturally, I’m hitting the API Rate Limit by receiving a 429 status code. However, I can’t seem to be able to confirm exactly what the API Rate Limit is for this endpoint. The only page that mentions Rate Limiting is the one here and it pertains to Incident API or Advanced Hunting API, but not to the Entity API Vulnerability. Can someone please tell me what the API Rate Limit is for Entities under which Vulnerability falls? Thank you.    Read More

​