MySQL code to find all managed machines that have checked-in in the last 30 days

[benducklow]

Looking to expand on some manual MySQL queries to make gathering metric data for upper management easier. As I experienced before, the whole epoch thing is throwing me off. I am hoping @iJake @powellbc or other MySQL experts can asset me. I am looking to find all managed machines that have checked-in in the last 30 days. Here is what Ive come up with but its not quite right:

SELECT c.computer_name, FROM_UNIXTIME(cd.last_contact_time_epoch/1000,"%m/%d/%Y  %h:%i") AS 'last check-in',FROM_UNIXTIME(cd.last_report_date_epoch/1000,"%m/%d/%Y  %h:%i") AS 'last inventory update'
FROM computers c
    JOIN computers_denormalized cd ON c.udid = cd.udid
WHERE cd.Is_Managed = 1 AND cd.last_contact_time_epoch <'2592004'

The 2592004 is what I've come up with for how many seconds there are in 30 days.

[iJake]

I'm not sure why you are doing the join. Everything you need is in the computers_denormalized table. I believe this query will get what you want. You just will have to keep changing the unixtime stamp you compare against as it is July 18, 2014. Anything that has checked in since then will show in the result set.

SELECT * FROM `computers_denormalized` WHERE Is_Managed = 1 AND last_contact_time_epoch/1000 >'1405641600'

[powellbc]

I am no expert. I know just enough about SQL querying to be dangerous.

This one can also work as well I believe (using the base syntax I used in the other thread):

Select computer_name,
from_unixtime(substring(last_contact_time_epoch,1,10))
from computers_denormalized
WHERE from_unixtime(substring(last_contact_time_epoch,1,10)) > '2014-07-18'
ORDER BY last_report_date_epoch desc;

In this case you would need to change the date as desired. I verified it was giving me the correct results but I urge you to test first.

[benducklow]

Thanks fellas. @iJake - I am doing the join because I am picking up a few machines that have the is_managed = 0 in my results. I am looking to clone this simple search (see below). external image link

I will be building upon it to look for specific computers names based on our standard naming conventions.

I am still playing around with this as I am getting a different counts doing an Advanced Computer Search vs. a direct query from the database. When I use the Advanced Computer Search on the JSS, I get 514 results... when I query the db directly, I get 517. I'm not seeing why they are off by 3...

SELECT DISTINCT cd.computer_name, FROM_UNIXTIME(cd.last_contact_time_epoch/1000,"%m/%d/%Y  %h:%i") AS 'last check-in', FROM_UNIXTIME(cd.last_report_date_epoch/1000,"%m/%d/%Y  %h:%i") AS 'last inventory update', cd.is_managed
FROM computers c
    JOIN computers_denormalized cd ON c.udid = cd.udid
WHERE cd.Is_Managed = 1 AND FROM_UNIXTIME(substring(last_contact_time_epoch,1,10)) > '2014-07-18'
ORDER BY cd.computer_name