It’s been a while since I made a post on this website. This isn’t because I’ve not been keeping active with my learning in cybersecurity, but because I’ve been trying to build up my knowledge further over the past two months by learning specifically about Microsoft Security Operations, Azure, and Microsoft’s security environment.
I recently started a course (SC-200) on Udemy which can be found here and which takes you through the process of setting up a Microsoft security environment, and includes activities such as setting an Azure virtual machine, setting up Microsoft Admin roles and permissions, onboarding devices and managing them using Defender for Endpoint, verifying that devices have been onboarded successfully, creating user groups and device groups, and setting up and configuring Sentinel to ingest Azure and Entra ID data.
I’ve just got to the part of the course where I’ve been playing around with workbooks and I initially had some issues in getting these to work properly. For examples, one of the issues I was encountering can be seen in the screenshot below:
As I didn’t know what was happening, I asked ChatGPT for assistance and it explained that this was just a workbook parameter issue, not a data problem.
Essentially, the work workbook has a parameter called ‘UserName’, but this is currently set to ‘All’ (or blank behind the scenes), the query behind the chart requires an actual value, not ‘All’, and so the query was failing.
To fix this, ChatGPT said to edit the workbook and change the query to something like the following:
| where isempty(“{UserName}”) or UserPrincipalName == “{UserName}”
After getting into the edit section for the query, I was presented with the following screen:
This screenshot shows that the query was using strict parameter filters, and the one breaking things was:
| where UserDisplayName in ({Users});
The reason this was causing things to fail is because {Users} is a parameter, and when it’s set to ‘All’ it doesn’t pass a real value. So, this line becomes invalid, and the query fails with ‘Please set: UserName”.
So to fix this, we need to replace:
| where UserDisplayName in ({Users});
with:
| where isempty(‘{Users}’) or UserDisplayName in ({Users})
While in the edit query section though, ChatGPT said to update and fix all filters properly with the following, which I did:
let data =
union SigninLogs, AADNonInteractiveUserSignInLogs
| where isempty(‘{Category}’) or Category in ({Category})
| where isempty(‘{Apps}’) or AppDisplayName in ({Apps})
| where isempty(‘{Users}’) or UserDisplayName in ({Users});
data
| summarize count() by UserPrincipalName, bin(TimeGenerated, 5m)
This ultimately fixed things. And the overall takeaway from this is that workbooks use parameters which are injected into KQL. And if not handled correctly, dashboards can break. And I understand that real analysts regularly fix parameter handling, make dashboards resilient to ‘All’ / null, and validate data versus query logic.
I next encountered a further problem with another of the workbooks for Microsoft Defender for Office 365 Detection and Insights I have set up as follows:
In this case, the subscription and workspace options were not set. But this was a relatively straightforward and quick fix.
By clicking on Open in Azure at the top of the page, all I had to do was set these options in the following places:
At the moment, I don’t have any data to calculate anything from and so the fields populate in the dashboard as NaN (not a number). This workbook is email security specific, and as I’m just learning at the moment, I don’t have this data and the dashboard will stay empty as follows:
Moving on, ChatGPT suggested creating a dashboard myself and guided me through the steps to do so. The workbook I created is named AgarSec – Entra Sign-in Detection Dashboard, and appears in my list of workbooks as follows:
This workbook consists of failed sign-ins, top failed login IPs, and failed logins over time.
The steps I took to create this were as follows. In Sentinel:
- Go to Workbooks
- Select Add workbook
- Click Edit
- Add a TimeRange parameter first
This then brought up the following window in which I called the Parameter name: TimeRange, the Display name: Time Range, I set the Parameter type: Time range picker, checked the box: Required, and checked the box: Last 24 hours, as follows:
I then clicked Save at the bottom of this window, and then clicked the option below, and set this to Last 24 hours.
I now currently have the following dashboards:
but I don’t need the following box, so I’ll remove it:
And so now, I just have the following:
Now to build the first dashboard (Failed sign-ins), I did the following:
Add → Add data source + visualization
I then pasted in the following query:
SigninLogs
| where TimeGenerated {TimeRange}
| where ResultType != 0
| summarize FailedLogins = count()
Set the visualisation to Stat, added the title Failed Sign-ins and then clicked ‘Done Editing’ at the bottom (not the top) of the window.
In this case, I already have a few failed sign-ins because after making the dashboard the first time, I then logged into the virtual machine I’d previously created in Azure, navigated to the login page for the 365 admin center, and entered the wrong password several times to simulate a brute force attack.
Next, I created some visualisations for Top Failed Login IPs, Failed Logins Over Time, and Possible Impossible Travel. The process was similar to before, so I won’t include screenshots here showing exactly what I did, but I’ll include some details of the steps I took.
For Top Failed Login IPs, I clicked:
Add → Add data source + visualization
I then pasted the following query:
SigninLogs
| where TimeGenerated {TimeRange}
| where ResultType != 0
| where isnotempty(IPAddress)
| summarize Failures = count() by IPAddress
| top 10 by Failures desc
I set the visualisation to Bar Chart, and titled it Top Failed Login IPs.
For Failed Logins Over Time, I clicked:
Add → Add data source + visualization
I then pasted the following query:
SigninLogs
| where TimeGenerated {TimeRange}
| where ResultType != 0
| summarize count() by bin(TimeGenerated, 1h)
I set the visualisation to Time Chart, and titled it Failed Login Over Time.
Finally, I added Impossible travel detection, using a similar process to before:
Add → Add data source + visualization
I then pasted the following query:
SigninLogs
| where TimeGenerated {TimeRange}
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| where isnotempty(UserPrincipalName) and isnotempty(Country)
| order by UserPrincipalName asc, TimeGenerated asc
| serialize
| extend PrevUser = prev(UserPrincipalName),
PrevCountry = prev(Country),
PrevTime = prev(TimeGenerated),
PrevIP = prev(IPAddress)
| where UserPrincipalName == PrevUser
| where Country != PrevCountry
| extend MinutesBetween = datetime_diff(‘minute’, TimeGenerated, PrevTime)
| where MinutesBetween between (0 .. 240)
| project TimeGenerated, UserPrincipalName, PrevCountry, Country, MinutesBetween, PrevIP, IPAddress, AppDisplayName
| order by TimeGenerated desc
I set the visualisation to Grid, and titled it Possible Impossible Travel.
To test out my new dashboard, as explained above, I generated some failed login attempts to see if the dashboard would actually show this activity, which it did as follows:
So, it appears the dashboard is working as intended, and everything seems to have been set up correctly. And in this case, there are no results for impossible travel because all of my logins were from GB, with no anomaly to detect.
But what I might say as an analysts here if I saw this activity would be “We have multiple failed authentication attempts from a single IP within a short time window — potential brute force attempt.”
Conclusion
This was an interesting first dive into creating a dashboard and visualising things. I’ve kept this simple for now, but I’m aware I can build upon this further, by adding things such as threshold detection to detect suspicious IPs and not just noise.
I’m also aware I can create Sentinel detection rules so that instead of manually checking things, I can be alerted automatically.
Additionally, I know I can clean things up further and make this look like a professional and real SOC dashboard with side by side panels, and I can simulate more realistic attacks.
Moving forwards, this is what I’ll be looking to do next. And I hope to be able to present something that’s more in-depth and reflective of real world scenarios in my upcoming posts.