Hello everyone,
I am monitoring certificate expiration dates on HP printers and I want to create widgets that display ONLY the certificates that are expired and certificates that are expiring within 30 days. I created a UnDP to poll the certificate expiration date from the printers but I do not have a way to convert that information from a string into a date that solarwinds can understand so I can use the GETDATE < () function. Is there a way to convert the string to a date? Below is my query and an example output:
SELECT
n.NodeName,
n.Status,
n.DetailsURL,
n.IP_Address,
n.MachineType,
ncp.DeviceType,
cps.OID,
cps.UniqueName,
CASE
WHEN cpsn.Status LIKE '%Cert Expires: %' THEN
REPLACE(cpsn.Status, 'Cert Expires: ', ")
ELSE
cpsn.Status
END AS OID_VALUE
FROM Orion.Nodes n
JOIN Orion.NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
LEFT JOIN Orion.NPM.CustomPollerStatusOnNode cpsn ON n.NodeID = cpsn.NodeID --Join for specific poller status
LEFT JOIN Orion.NPM.CustomPollers cps on cpsn.CustomPollerID = cps.CustomPollerID -- Join with CustomPollerStatus based on CustomPoller ID
WHERE
cps.UniqueName LIKE '%CertExpDate802_1x%'
and ncp.DeviceType IN ('Printer')
An example of the output I get with this query is "Cert Expires: 2017-08-01 00:00 UTC". With the CASE statement I can remove the "Cert Expires:" leaving behind only the UTC Date/Time. I want to convert that into a date that I can query against and only show expired certs and ones that expire within 30 days using the GETDATE function.
The Solarwinds GitHub lists a function called DateTime which is supposed to convert a string into a date but when I go to run it, I get an error saying “Function DateTime not found”
Quote from the SWQL function page on GitHub
DateTime - Converts a string to a date. In most scenarios this conversion will happen automatically when needed by usage.
Hello everyone,
I am monitoring certificate expiration dates on HP printers and I want to create widgets that display ONLY the certificates that are expired and certificates that are expiring within 30 days. I created a UnDP to poll the certificate expiration date from the printers but I do not have a way to convert that information from a string into a date that solarwinds can understand so I can use the
GETDATE < ()function. Is there a way to convert the string to a date? Below is my query and an example output:An example of the output I get with this query is "Cert Expires: 2017-08-01 00:00 UTC". With the CASE statement I can remove the "Cert Expires:" leaving behind only the UTC Date/Time. I want to convert that into a date that I can query against and only show expired certs and ones that expire within 30 days using the GETDATE function.
The Solarwinds GitHub lists a function called DateTime which is supposed to convert a string into a date but when I go to run it, I get an error saying “Function DateTime not found”
Quote from the SWQL function page on GitHub