I am trying to compare the differences between a group of computers in our environment to track down an issue we're seeing. I'd like to create a custom report or query that will give me the following information for these computers:

1- Machine Name
2- Model
3- MAC Address
4- OS Name & Service Pack
5- BIOS Name and Version
6- Software Installed
7- Installed Patches
8- Running Processes
9- Startup Programs
10- Services

I have not been able to find a way to create a report that contains all these items in one report. I have MySQL Workbench installed so I'm assuming it would be best to run an SQL query from this instead. I understand there is a way to join the info from the different tables this info would come from into one query but as I am horrible with anything SQL related, I'm not sure how to do this. I also understand that this would output quite a bit of info, so it doesn't necessarily have to be in a single query, but I'd prefer as few as possible. Any help would be very much appreciated.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

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.

 

 

Answered 09/03/2012 by: chucksteel
Red Belt

Please log in to comment
3

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

 

Answered 09/04/2012 by: dugullett
Red Belt

  • Glad my little primer helped! ^_^

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