Forum Discussion
Query All Logs/sources for Credit Card Numbers
We thought this might be something that Microsoft Sentinel could have some built in functionality for but seems we cannot find it. We are looking to be able to query all of our log sources for any credit card numbers but I cannot seem to think of a great way to do this and I don't believe union is possible in an analytics rule. Has anyone else created logic in KQL to potentially solve this gap in the solution?
Happy to post our regex here as well:
(.*)((?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13}|6(?:011|5[0-9]{2})[0-9]{12}))(.*)
Common Goal:
1. Query the log source(s) for the specific regex
2. Parse the field identified as matching the regex so we can capture where it matches and go from there, not just that a "match exists"
This seems rather easy but also....struggling to think of a good way to make this happen especially across all log sources rather than querying one table at a time.
- Hello. You can use union just not with the wildcard "*".
Option 1:
https://github.com/Azure/Azure-Sentinel/blob/004ceca9de9e9686ce69aed17e7345872179dfac/Detections/MultipleDataSources/HostAADCorrelation.yaml#L69
Option2:
https://github.com/Azure/Azure-Sentinel/blob/004ceca9de9e9686ce69aed17e7345872179dfac/Detections/SecurityEvent/SolorigateNamedPipe.yaml#L31
None of the above are ideal as you need to name the tables in the KQL - there is a partial workaround whereby you auto create the Table list in a watchlist (maybe daily) and use that in the query.
Option 3: Personally, I'd run a Playbook and use the union * in there (which doesnt have the limitation when you run it as a rule). Its not particularly efficient, so make sure these dont run too frequently and have time to process.
I use this to find txt in a column (for named Tables, I've not tried it with union)
let srch = "User"; // search for
search in (SigninLogs) srch // Table to search in
| evaluate narrow()
| where Value contains srch // also try "has" for better efficiency rather than "contains"
| summarize count() by Column, txtFound = srch, Value- Thank you! Your "| evaluate narrow()" officially made the query. I think it might be beneficial to even publish this to the Azure Sentinel Github instance as it could be utilized by anyone who want's to search their log sources for PAN!
Thanks again for your assistance here!Could you share the full code you used for this search? analyst1900
