Datalust's Seq Queries

Introduction

The following is an example of Seq Queries for generating reports from SecureDNS Structured Logs.

Resolved A Records:

select Coalesce(Answer.Answers[0].Domain, Answer.Questions[0].Domain) as Domain, Coalesce(Answer.Answers[0].Type, Answer.Questions[0].Type) as Type, Answer.Answers[0].Record.Address as IP, RemoteEndPoint from stream where StartsWith(@Message, 'Resolved') and Type = 'A' limit 1024

Resolved CNAME Records:

select Coalesce(Answer.Answers[0].Domain, Answer.Questions[0].Domain) as Domain, Coalesce(Answer.Answers[0].Type, Answer.Questions[0].Type) as Type, Answer.Answers[0].Record.Domain as CNAME, RemoteEndPoint from stream where StartsWith(@Message, 'Resolved') and Type = 'CNAME' limit 1024

Resolved Domain Names:

select distinct(Answer.Questions[0].Domain) from stream

Filtered Domain Names:

select Answer.ID as ID, Answer.Questions[0].Domain as Domain from stream where StartsWith(@Message, 'Filtered')