Reports with criteria Based on Future Date

[Randomecho]

I have managed to get a report put together that Reports on all systems in my Inventory that have a Certain SSL Certificate installed.  Using the GetDate() Function, I can report on systems that have an expired certificate that expired prior to the current date.  I was hoping to be able to use some sort of operator that would give me a date say 15-30 days in the future and give me a list of Certs Expiring in that period of time.

Have not been able to find a way to do this as of yet.

Here is what I have so far, maybe this is not possible.

Select Top 1000000 tblAssets.AssetID,
Coalesce(tsysOS.IMAGE, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username As UserName,
tblAssets.Userdomain As UserDomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
tblCertificates.SubjectName,
tblCertificates.IssuerName,
Case
When tblCertificates.HasPrivateKey = 0 Then 'no'
Else 'yes'
End As HasPrivateKey,
Case
When tblCertificates.IsArchived = 0 Then 'no'
Else 'yes'
End As IsArchived,
tblCertificates.StartDate,
tblCertificates.ExpirationDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblCertificateLocations On tblCertificateLocations.AssetID =
tblAssets.AssetID
Inner Join tblCertificates On tblCertificates.CertificateID =
tblCertificateLocations.CertificateID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblCertificates.SubjectName = '*.certdomain.com' And
tblCertificates.ExpirationDate < GetDate() And tblAssetCustom.State = 1
And tsysAssetTypes.AssetType = -1
Order By tblAssets.AssetName

[rader]

Try this line from an Esben report on certificates expiring in 30 days.

Where tblCertificates.ExpirationDate < GetDate() + 30 And
tblCertificates.ExpirationDate > GetDate() And tblAssetCustom.State = 1 And
tsysAssetTypes.AssetType = -1

Report:

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username As UserName,
  tblAssets.Userdomain As UserDomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblCertificates.SubjectName,
  tblCertificates.IssuerName,
  Case
    When tblCertificates.HasPrivateKey = 0 Then 'no'
    Else 'yes'
  End As HasPrivateKey,
  Case
    When tblCertificates.IsArchived = 0 Then 'no'
    Else 'yes'
  End As IsArchived,
  tblCertificates.StartDate,
  tblCertificates.ExpirationDate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblCertificateLocations On tblCertificateLocations.AssetID =
      tblAssets.AssetID
  Inner Join tblCertificates On tblCertificates.CertificateID =
      tblCertificateLocations.CertificateID
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblCertificates.ExpirationDate < GetDate() + 30 And
  tblCertificates.ExpirationDate > GetDate() And tblAssetCustom.State = 1 And
  tsysAssetTypes.AssetType = -1
Order By tblCertificates.ExpirationDate Desc