New fields in Certificates

There are mutliple new fields available in certificates, and you can use them in queries and reports. The renewed and partially_renewed fields allow you to make a custom report that lists all certificates that are about to expire. The result shows if they have been renewed in time or not:

index=certificates not_after<+3w last_seen>-1m | table cn,
 ip, grade, renewed, partially_renewed, issuer

The cn_without_sni field is handy to get more information on shared hosters:

index=certificates  cn != cn_without_sni | table cn,
 cn_without_sni, ip, issuer

The subject and issuersubject fields give the exact string that is in the certificate, complete with CN, O , OU and other values used. As an extra, these are also parsed out and made available seperately. Please note that older certificates (before April 2022) do not have these fields populated. You can use these fields to group by subject countries:

index=certificates by C

Or issuer countries:

index=certificates by issuer_C

Please see Certificate Index in the docs for more details.

New query keyword: IN

Query based reports are here, and query based alerts are underway. To fully use this, queries should be easy. That is why you can now use IN () and NOT IN () as keyword.

Say you want a report of all websites that do not return a 201, 403 or 404 HTTP code. The old way (which still works) would be:

index=websites https_status!=201 AND https_status!=403 
  AND https_status!=404

The more values you want to select or exclude, the longer the query becomes. With the new keyword this query can be rewritten as:

index=websites https_status NOT IN(201, 403, 404)

Much better right?

Select report columns with | table

Last week custom reports went live. One of the first things you of course want is the ability to select the columns or fields you want in the search results and exports. For that, we now have the keyword table.

Just like in Splunk, you can "pipe" queries in to other commands. For now only the table command is supported. With table you can explicitly select the columns you want in the search results. It works on all queries, including queries with the by keyword. All queries where results are grouped with by will always contain the count per group in the last column (named "count").

Here is an example query to try:

index=websites latest=-7d | table url ip https_status http_status

You can also use | table to select columns in combination with the by keyword in grouped results:

index=hosts by asn | table asn country