Hi All,

Trying to develop a report for all computers that haven't synced back in the last 40 days or greater. Have the report working fine - however it displays ALL computers that haven't reported back, not just the ones that haven't reported in 40 days or more.

Below is the syntax i'm using at the moment, can someone please assist?

select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME from MACHINE where (CURDATE() - INTERVAL 40 DAY) > 40 order by LAST_SYNC_TIME

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Try using this.

select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC,

REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME

from MACHINE

where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY)

order by LAST_SYNC_TIME
Answered 06/03/2013 by: dugullett
Red Belt

  • Hi Dugullett - thanks for your help, however it didn't work... still listing all computers syncing, not just 40 days or older. I've attached a screenshot if it helps. Thanks http://postimg.org/image/r4yhyo8pd/
    • It's working for me. I changed the INTERVAL to 1 MINUTE just to test. It only returned 25 rows out of 18,000 machines. I also cannot see your screenshot. It's blocked here at work.

      where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 1 MINUTE)
      • Hi Dugullett, still having no luck here on my end. Returning all computers syncing - not just 40 days or older.
      • Can you paste exactly what you are using? What version are you on?
  • Hi Dugullett, i'm using K1000 v5.4.76847,
    The code i'm using is below:

    select DATE_FORMAT(LAST_SYNC,'%W %m-%d-%Y') as LAST_SYNC, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC,

    REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, LAST_SYNC as LAST_SYNC_TIME

    from MACHINE

    where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY)

    order by LAST_SYNC_TIME

    Appreciate the assistance.
    • Sorry I guess I misread your original question. To get the ones that have not synced in the past 40 days change this line.

      where LAST_SYNC > DATE_SUB(NOW(), INTERVAL 40 DAY)

      to

      where LAST_SYNC < DATE_SUB(NOW(), INTERVAL 40 DAY)
      • Many thanks Dugullett! that worked great!
Please log in to comment
Answer this question or Comment on this question for clarity