Navigating the Journey from SPL to SQL
As the field of security data engineering continues to evolve, workloads that once lived in the siem are being migrated to or augmented with a security data lake. Often time this means shifting a fair amount of queries from a siem specific DSL, like SPL, to SQL. The question I’m often asked is “how can we translate our SPL queries to SQL queries?” I used to struggle answering that, because on one hand I love solving technical problems, on the other hand I also love ranting about philosophy. This post is both.
The fundamental issue of translation is stems from a innate difference in paradigms. SPL is a streaming language, while SQL is not. Although there’s some overlap, allowing non-streaming commands in SPL and streaming-type syntax in SQL, fundamentally, they are built differently. In theory, streaming languages are associated with real-time data processing and event-driven architectures while languages like SQL are associated with historical data analysis and complex queries involving joins, aggregations, subqueries, and window functions. In practice, analysts will use the tools they have to accomplish what they need regardless of which language is theoretically more appropriate.
It’s similar to using a translator app for spoken languages: you can convey your message, but nuances and tones crucial for effective communication are lost.
Generally, I’ve come across two main motivators for converting queries in the first place. The first are teams migrating a workload to an SQL-based system that wish to transfer their dashboards and queries as effectively as possible. In this case, it’s a one-time effort and these queries will likely be in production for an extended period and run repeatedly. This means that performance and optimization can have a large impact and so time spent validating and optimizing queries is arguably justified. Not that you have to do it totally by hand of course. I’ve heard good feedback from customers using an LLM to do the initial conversion, then having an SME review and tweak.
The second motivator is to soften the transition for analysts and threat hunters accustomed to SPL who may find it challenging to switch paradigms abruptly. Here, I suggest a ‘crawl, walk, run’ strategy, where analysts gradually become more familiar with SQL. This approach minimizes disruption and enables experts to adapt to the new paradigm while mitigating the risks of change. To revisit the earlier simile, if you’re moving to a foreign country, starting with a phrasebook or app is helpful, but over time, learning the language itself is more beneficial.
Strategies for gradual adoption might include using custom macros to emulate functionalities like wildcard searches or writing less efficient but simpler queries. Similarly, analysts can leverage LLMs to assist in query writing or, if using an SPL-supported UI, funnel SQL results into SPL functions. Eventually, they will grow comfortable with SQL and can natively craft queries in it.
To summarize, translating SPL queries into SQL is not just a technical exercise; it involves understanding and adapting to fundamentally different paradigms. While automated tools like LLMs offer a starting point, they cannot fully capture the nuances and efficiencies of a new language. For organizations and individuals facing this transition, a balanced approach is key. This involves using technology to facilitate initial conversions and gradually immersing analysts in SQL through a structured learning path. Just as mastering a new language takes time and practice, so does the transition from SPL to SQL. Embracing this journey with the right tools and mindset can turn an initially scary looking task into a rewarding learning experience that ultimately leads to a more robust and effective security team.
