In the Cisco Live Melbourne SOC, we use a combination of Endace full packet capture (PCAP), Splunk Enterprise Security, and Splunk SOAR to provide automated detections of cleartext credential leakage. This approach uses an Event Based Detection (EBD) in Enterprise Security, searching over the metadata stored in Splunk by the Endace PCAP system. Part of the approach is to look for usernames and passwords in several cleartext protocols, including FTP, SMTP, HTTP, and POP3.
One problem with this is that across those protocols, the username and/or password can be stored in multiple schema fields depending on which protocol is in use. We resolve this using the coalesce function in Splunk’s eval command. The coalece function will return the first non-null value from a list of possible values, just like the COALESCE function in SQL. In our case, we use it like so:
However, during Cisco Live we discovered a problem with this logic. Splunk will treat a field that exists, but is empty, as not-null. So as an example, we have this event from our dataset:
In this event, the username is an empty string and the mailfrom field contains the username. The design of our coalesce was failing us here by grabbing the empty username because it was technically “” not NULL.
So, what to do? We want a quick and easy way to clean up the empty strings. One answer is a Splunk macro. Macros let you define snippets of SPL code that can be called by name anywhere you’d like.
We defined several macros, all named clean_empty_strings. Splunk allows you to overload the name of a macro so long as the number of arguments is different. So we defined several versions of this macro, like clean_empty_strings(), clean_empty_strings(2), clean_empty_strings(3) and so on all the way up to clean_empty_strings(8). They are mostly identical except for the number of arguments. We made up to 8 because that was how many fields we needed to clean at once in our detection, just as a convenience.
The macro definition looks like this:
In the GUI, it looks like:
Now in the Detection SPL, we can use it like so:
From here, we know that none of the fields we are about to coalesce could possibly contain an empty string value. The detection is much more reliable, without a lot of added complexity.
Check out the other blogs by my colleagues in the Cisco Live Melbourne 2026 SOC.
We’d love to hear what you think! Ask a question and stay connected with Cisco Security on social media.
Cisco Security Social Media
CONNECT WITH US