It took about six weeks before we saw our first true positive that the default detections missed. Like you, it was a service account pattern, but specifically a sequence of low-severity events - a scheduled task creation followed by a rare network connection - that only looked suspicious when joined across a specific device group.
The real lesson for us wasn't the timeline, but the validation loop. That first custom detection didn't stop an incident, it just flagged something we were able to investigate and confirm as benign. That sounds like a failure, but it built critical trust in the raw data schema. The "win" came a month later when a nearly identical pattern popped up and we could confidently escalate it in minutes.
Are you tracking how often your custom logic validates a hunch versus actually prompting a new response? That ratio tells you more about maturity than the timeline does.
Writing a custom Python script was definitely the way to go for us too, but we used the Azure SDK for Python instead of msal directly. It handled a lot of the auth and pagination boilerplate.
About the warehouse structure, starting with just DeviceEvents is good advice. We made the mistake of trying to ingest everything at once. Pick one table, get that pipeline solid, then add another.
Can I ask, how are you handling authentication? We used a service principal, but I'm still not sure if that's the best way for a long running ETL job.
Still learning
Good questions. I'd write the Python extractor. Airbyte can struggle with MDE's pagination and rate limiting in my experience, you need more control.
For warehouse structure, start with a raw landing zone that mirrors the Advanced Hunting schema exactly. Don't try to flatten or model it on ingestion. Just get the nested JSON in. Then, build your dbt models on top of that raw layer. Start with just DeviceEvents and DeviceInfo.
Cleaner vs actionable is the real debate. Sophos logs are cleaner, but MDE's are more correct. Actionable depends entirely on your team's bandwidth to build those detection models. Without that, MDE just feels noisier.
Spreadsheets > marketing slides.
You're right about the schema shock, it's real. On your question about structuring the raw data, yes, just dump the full JSON blobs into a raw landing table, exactly as the API returns them. Trying to normalize on ingestion will break constantly.
One specific tip: Use the service principal auth, store the creds in your pipeline's secrets manager, and make sure you're handling the 429 (throttling) responses. That's where a custom Python script really shines over Airbyte. You can back off and retry gracefully.
Cleaner vs actionable? Sophos gave you a curated art gallery. MDE gives you the raw marble quarry. You get to be the sculptor, but only if you have the time and tools. The data's more trustworthy, but only after you've built that trust yourself. Good luck.