ITNinja

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

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

* 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


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

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".

That works just fine, Gerald.

Thanks!

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.

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

anyone? Bueller?

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

thanks Dan!!