Lists all machine fields for all computers that have created a new inventory record in the last 10 days. This can be used as a filter or email notification as well.

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
where (DATE(NOW() - INTERVAL 10 DAY) < DATE(MACHINE.CREATED))
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
We use this for a daily Email Alert sent in the evening for all computer records added that day:


select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from MACHINE
WHERE DATEDIFF(NOW(),CREATED) < 1
order by MACHINE.NAME
Answered 01/14/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
Does anyone know of a way to modify these reports to only show some minimal information (like machine name, IP, Username, etc.)? The current report lists ALL information and is way too big to be useful for a quick notification of what was recently added.

Thanks
Answered 03/03/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
* means, all columns from all tables involved.

If you refer to the download archive section you will see a schema for each database. The tables you are interested in are likely in the org schema (not the kbsys schema)

From your example request:
MACHINE.NAME - Host name of pc
MACHINE.IP - IP used to do the last inventory
MACHINE.USER - username (more accurate then USER_NAME)

Then you would rewrite the query. Here's a rewrite of the first line:
select MACHINE.NAME,
MACHINE.IP,
MACHINE.USER,
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
Answered 03/03/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Gerald,

That works great! Next question for you. How do I interpret the Last Sync Time and Last Sync Seconds data?

For instance, if I run it for Machines Added Today, Last Sync Time = 10907 and Last Sync Seconds = 1267636985.

Thanks again
Answered 03/03/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) is how many seconds ago it synced. You could just use MACHINE.LAST_SYNC if you want the timestamp instead.

UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS is the time in seconds since '1970-01-01 00:00:00' UTC -- so probably not very useful to you.

The only reason I included those is they were in the first post.

The only reason the first poster included those is because they are the way our reports, forms, email alerts, filters, etc query the data and turn that into "10s" or "1d".
Answered 03/03/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
That works just fine, Gerald.

Thanks!
Answered 03/03/2010 by: errinf
Senior Yellow Belt

Please log in to comment
0
We use this

select MACHINE.*, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS

FROM MACHINE
WHERE DATE(CREATED) > DATE('2010-03-30 12:00:00')




As a filter to assign a label to a computer, in our case after today (the 30th of March). We use this right now for a few software items that don't upgrade well, so we can start putting it on new computers.
Answered 03/30/2010 by: dtuttle
Purple Belt

Please log in to comment
0
Cool report. Could someone show me how to get the machine model added to the report?
Answered 04/23/2010 by: mlathrop
Fifth Degree Brown Belt

Please log in to comment
0
anyone? Bueller?
Answered 05/25/2010 by: mlathrop
Fifth Degree Brown Belt

Please log in to comment
0
Could someone show me how to get the machine model added to the report? ORIGINAL: mlathrop

Cool report. Could someone show me how to get the machine model added to the report?


MACHINE.CS_MODEL is the field for model. MACHINE.CS_MANUFACTURER is the field for Manufacturer. You can add these two fields in the select statement 'select MACHINE.NAME, MACHINE.CS_MODEL, MACHINE.CS_MANUFACTURER from MACHINE.....'

Dan
Answered 06/04/2010 by: DContreras
Orange Belt

Please log in to comment
0
thanks Dan!!
Answered 07/16/2010 by: mlathrop
Fifth Degree Brown Belt

Please log in to comment
Answer this question or Comment on this question for clarity