KACE Product Support

Wow. This will start off as an easy query but will get complicated once you start joining the necessary tables. 

A basic query of the machines table will get the first 5 fields:

 SELECT NAME, CS_MODEL, MAC, OS_NAME, SERVICE_PACK, BIOS_NAME, BIOS_VERSION 
FROM MACHINE

Now things will get more complicated. For each of the additional things you want to report you'll have at least one table join. We'll start with the software. If you look at the tables on the KBOX there are several tables related to software:

• SOFTWARE
• SOFTWARE_LABEL_JT
• SOFTWARE_OS_JT

Unfortunately none of these actually relate what software is on a particular computer so looking through the tables there's actually a MACHINE_SOFTWARE_JT. KACE is nice that all of the tables that contain relationships tend to end with JT so it does make them easier to find. The first join we need to make is to the MACHINE_SOFTWARE_JT so now our query looks like this:

 SELECT MACHINE.NAME, CS_MODEL, MAC, OS_NAME, SERVICE_PACK, BIOS_NAME, BIOS_VERSION
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID

Since we didn't add anything to the SELECT portion of the statement nothing gets added to the output at this point. The only thing we could add would be a list of the software ID numbers. What we're really interesting in getting at is in the SOFTWARE table, so we also need to join to that:

 SELECT MACHINE.NAME, CS_MODEL, MAC, OS_NAME, SERVICE_PACK, BIOS_NAME, BIOS_VERSION
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
LEFT JOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID

If we just added the SOFTWARE.DISPLAY_NAME to the SELECT statement we wouldn't end up with what we're after, because it will only choose one thing from that table through the join. I normally use a group_concat in this situation to group the software into one result:

 SELECT MACHINE.NAME, CS_MODEL, MAC, OS_NAME, SERVICE_PACK, BIOS_NAME, BIOS_VERSION, 
group_concat(SOFTWARE.DISPLAY_NAME ORDER BY DISPLAY_NAME) as "Installed Software"
FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
LEFT JOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID 
GROUP BY MACHINE.NAME

Adding the Running Processes and Startup Programs is a similar process since they both have MACHINE_PROCESS_JT and MACHINE_STARTUPPROGRAM_JT tables with corresponding tables to hold the actual data. Adding the installed patches data tends to become more difficult because that data is stored in other less obvious places.

Note that when I started adding the processes and startupprogram tables to my join statements my queries started to take a very long time to run. There may be a more efficient way to handle those parts of the query and hopefully someone else will chime in if there is.

Kace has a built in report already for patches by machine "For each Machine, what patches are installed". The query looks like this.

Select CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME  WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
where 
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
S.STATUS = 'PATCHED'
order by MACHINE_NAME, P.TITLE

I've gotten in the habit of adding "Limit 10" to everything I do so that the Kbox will not get bogged down until I get what I want correct. I would do the same with the processes. I've ran a report on processes before and got a very slow response. 

Maybe looking for a specific process could limit that. For that use the WHERE statement. 

SELECT M.NAME, IP, MAC, OS_NAME

FROM MACHINE M

LEFT JOIN MACHINE_PROCESS_JT ON MACHINE_PROCESS_JT.MACHINE_ID = M.ID

LEFT JOIN PROCESS P ON P.ID = MACHINE_PROCESS_JT.PROCESS_ID = P.ID

WHERE P.NAME LIKE 'WINLOGON%' OR P.NAME LIKE 'SERVICES%'

LIMIT 10

Again like Chuck said this will usually run extremely slow. Either way I would divide these all up and not try get everything with one query.

Two things that helped me quite a bit are here.

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

http://www.itninja.com/link/w3schools