/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Help creating report

02/25/2019 421 views

So I built a report that tells me what laptops have not connected since a certain date.  What I would like to do is get it to show me what laptops have not connected within a certain time period such as 7, 14, 21, 30 days.  Can anyone help me figure out what I need to put in within my WHERE statement to allow for this to work.


SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME, LAST_SYNC  FROM MACHINE  WHERE ((LAST_SYNC < '2019-02-11 00:00:00') AND (CHASSIS_TYPE = 'laptop'))  ORDER BY SYSTEM_NAME, LAST_SYNC
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

Ziggi using your query as something to work with, I was able to figure it out.  Here is the way I have it looking and running for the 30 day query.


SELECT
LAST_SYNC,
MACHINE.NAME AS SYSTEM_NAME,
MACHINE.USER_NAME
FROM
MACHINE
WHERE
(
(CHASSIS_TYPE = 'Laptop')
AND (
(
TIMESTAMP(LAST_SYNC) > NOW()
OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(), INTERVAL 30 DAY)
)
)
)
ORDER BY
LAST_SYNC
Answered 02/26/2019 by: tbingeman
Senior White Belt

All Answers

1

Hope this helps.


SELECT LAST_SYNC, MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME  FROM MACHINE  WHERE ((CHASSIS_TYPE = 'Laptop') AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 30 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 21 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 14 DAY))) AND ((TIMESTAMP(LAST_SYNC) > NOW() OR TIMESTAMP(LAST_SYNC) <= DATE_SUB(NOW(),INTERVAL 7 DAY))))  ORDER BY LAST_SYNC

Answered 02/26/2019 by: Ziggi
Blue Belt

  • I am trying to turn this into 4 reports not just 1. I need to figure out how to get it to show just 1 of the 7 time frames

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ