K1000 Reports - Machine Lists and Counts by Site, Role & Type
________________________________________________________

This is a group of SQL reports that will give a detailed overview of the machines managed by the K1000.  Most are focused on higher level items (number of models, machine types/roles per location, etc), although the first (Company Production Systems) gives specifics on each machine.  

Aside from the reports, I find the labels used in the reports useful for patching, managed installs, check-ins and other tasks.

Please see the example output for each report to determine if these would be useful and see the prerequisites for each.
________________________________________________________
________________________________________________________

*Pre-requisites*

These are how I currently have things configured on my K1000.  The LDAP labels could be done using smart labels, so that's just a personal preference.  The only absolute is that the label names themselves need to be in place.

If you are interested in using LDAP labels and would like to see some more in-depth examples and setup instructions, please see this post:

K1000 Patching - Setup, Tips & Things I Have Learned (LDAP, Smart Labels, SQL Reports)

http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
________________________________________________________
________________________________________________________

1) Location list

*The first task is to create a list of all of the company's locations, which will be used in building out the machine labels listed in the next section.  Our Active Directory OUs are primarily based on location, so this list was created using that as a guide:

East Canton
Greensboro
Hammond
Hillsborough
King Of Prussia
Marelan
Moulton
Oak Hill
Pittsburgh
Remote
Santa Fe Springs
Shenango
Tarentum
Testnet
UK
Warren
Warren Warehouse
Wellston

________________________________________________________

2) LDAP Labels for location (create for each location)

*This is an example "site computers" LDAP label (and the corresponding Search Filter) which is used in many of the reports.  The location name is the first item listed in the computer account's Description field, so the wildcard (*) is only used at the end.

Pittsburgh computers
(&(description=Pittsburgh*)(name=KBOX_COMPUTER_NAME))
________________________________________________________

3) LDAP label for server & control machines

*Same idea as above, but these target Description field entries in the middle so have wildcard characters at the beginning and end.

server
(&(&(description=*server*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))

control
(&(&(description=*control*)(objectClass=computer))(name=KBOX_COMPUTER_NAME))
________________________________________________________

4) Smart Labels for location types/roles (create for each location)

*These are examples of "machine role" smart labels (and their corresponding criteria) which are used in the reports.  Note how the LDAP labels from earlier are leveraged to create these.

Pittsburgh clients
Label Names = Pittsburgh computers *and*
Label Names does not match REGEX server|control

Pittsburgh desktop
Label Names = Pittsburgh computers *and*
Label Names does not match REGEX server|control *and*
Chassis Type = desktop

Pittsburgh laptops
Label Names = Pittsburgh computers *and*
Label Names does not match REGEX server|control *and*
Chassis Type = laptop

Pittsburgh servers
Label Names = Pittsburgh computers *and*
Label Names = server

Pittsburgh control
Label Names = Pittsburgh computers *and*
Label Names = control
________________________________________________________
________________________________________________________

*SQL Reports and Example Output*

Some of these reports are repetitive, but I've included them so the entire flow can be seen (particularly in the last couple of reports (...Type, ...Role) which use multiple SELECT statements based on the earlier reports.

Please note that I've adjusted the columns in the example output to fit a little better, truncated results as necessary and grabbed selected columns for some reports to show the variation in the output (i.e. the model count report).  The actual output is spaced appropriately (gets misrendered when posting) and typically has much more info.

Also please note that preceding spaces in the REGEX matches (i.e. L.NAME rlike ' desktop') are intentional and necessary in order for the reports to work as intended (due to other labels on my K1000 named "desktop", "control", etc).
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems

*Category*
Hardware (Custom)

*Description*
Lists all company machines by name, make, model, service tag, location and ship date.

*SQL Select Statement*
SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE,
M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG,
SUBSTRING(L.NAME, 1, length(L.NAME) - 10) AS LOCATION,
DATE_FORMAT(DA.SHIP_DATE, '%Y-%m-%d') as SHIP_DATE
FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'computers')
ORDER BY NAME
________________________________________________________

**Example Output**

Title: Company Production Systems
Description: Lists all company machines by name, make, model, service tag, location and ship date.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/07/31 11:55:30

#  Name    Make     Model           Service Tag  Location          Ship Date   
1  AALLEN  laptop   Latitude D520   14GKLC1      Pittsburgh        2007-03-04  
2  ABOYD   desktop  OptiPlex 160L   45ZRF41      Marelan           2004-02-18  
3  ADAVIS  laptop   Latitude D510   5543Q91      Remote            2006-03-26  
4  AFOX    desktop  Dimension 4700  23X8761      Tarentum          2005-01-18  
5  AJONES  desktop  OptiPlex 210L   1RMKPC1      Shenango          2006-10-27  
6  ALEWIS  laptop   Latitude D530   1Q2DJF1      Remote            2008-01-26  
7  AKID    desktop  OptiPlex 360    DK45WH1      Moulton           2008-12-22  
etc...
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Model Count

*Category*
Hardware (Custom)

*Description*
Condensed model list of all company machines (with count).

*SQL Select Statement*
SELECT CS_MANUFACTURER AS MANUFACTURER,CS_MODEL AS MODEL,
CHASSIS_TYPE AS FORM_FACTOR, COUNT(CS_MODEL) AS COUNT
FROM MACHINE
GROUP BY CS_MODEL
ORDER BY FORM_FACTOR, MODEL
________________________________________________________

**Example Output**

Title: Company Production Systems Model Count
Description: Condensed model list of all company machines (with count).
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/07/31 12:06:24

#  Manufacturer               Model             Form Factor  Count  
1  INTELR                     AWRDACPI          desktop          1  
2  To Be Filled By O.E.M.     ConRoe945PL-GLAN  desktop          3  
3  Dell Computer Corporation  Dimension 3000    desktop          2
4  Dell Inc.                  Latitude E5500    laptop           7    
5  Acer                       TravelMate 4050   laptop           1
6  Dell Computer Corporation  PowerEdge 1650    server           1
7  Dell Inc.                  PowerEdge T300    server           3
etc...
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (All)

*Category*
Hardware (Custom)

*Description*
Total machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 10) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'computers')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Title: Company Production Systems Count and Concatenated List by Site (All)
Description: Total machine count and list for all company sites.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/07/31 12:14:18

#  Location         Total  Machines                                                                                                 

1  East Canton         23  ajones DDIGGER EKING ...etc
2  Greensboro          42  BBARKER BSMITH CDAVIS ...etc
3  Hammond             19  BPARKER CNELSON dlee ...etc
4  Hillsborough         3  HMARTIN RJOHNSON twright
5  King Of Prussia      3  CWHITE DJACKSON GHALL
6  Marelan             29  ACOOK CPRICE DJENKINS ...etc
7  Moulton              5  AMURPHY FHOWARD mwood ...etc
etc...
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (Clients)

*Category*
Hardware (Custom)

*Description*
Client machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'clients')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Same format as Company Production Systems Count and Concatenated List by Site (All) report, but systems and numbers will vary due to report focus on client machines.
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (Client Desktops)

*Category*
Hardware (Custom)

*Description*
Client desktop machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike ' desktop')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Same format as Company Production Systems Count and Concatenated List by Site (All) report, but systems and numbers will vary due to report focus on client desktop machines.
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (Client Laptops)

*Category*
Hardware (Custom)

*Description*
Client laptop machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike ' laptops')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Same format as Company Production Systems Count and Concatenated List by Site (All) report, but systems and numbers will vary due to report focus on client laptop machines.
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (Servers)

*Category*
Hardware (Custom)

*Description*
Server machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'servers')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Same format as Company Production Systems Count and Concatenated List by Site (All) report, but systems and numbers will vary due to report focus on servers.
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count and Concatenated List by Site (Control PCs)

*Category*
Hardware (Custom)

*Description*
Control PC machine count and concatenated list for all company sites.

*SQL Select Statement*
SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY 1 SEPARATOR '\n') as MACHINES
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike ' control')
GROUP BY LOCATION
ORDER BY LOCATION
________________________________________________________

**Example Output**

Same format as Company Production Systems Count and Concatenated List by Site (All) report, but systems and numbers will vary due to report focus on control machines.
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count by Site and Role (All)

*Category*
Hardware (Custom)

*Description*
Client, control & server machine count and list for all company sites.

*SQL Select Statement*
SELECT CLIENT.LOCATION as 'Location', COALESCE(CLIENT.TOTAL, 0) as 'Clients',
COALESCE(CONTROL.TOTAL, 0) as 'Control PCs', COALESCE(SERVER.TOTAL, 0) as 'Servers'
FROM (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'clients')
GROUP BY LOCATION) CLIENT
LEFT JOIN (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'control')
GROUP BY LOCATION) CONTROL ON (CLIENT.LOCATION = CONTROL.LOCATION)
LEFT JOIN (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'servers')
GROUP BY LOCATION) SERVER ON (CLIENT.LOCATION = SERVER.LOCATION)
________________________________________________________

**Example Output**

Title: Company Production Systems Count by Site and Role (All)
Description: Client, control & server machine count and list for all company sites.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/07/31 12:51:19

#  Location         Clients  Control PCs  Servers
1  East Canton           19            2        2
2  Greensboro            36            4        2
3  Hammond               17            1        1
4  Hillsborough           3            0        0
5  King Of Prussia        3            0        0
6  Marelan               24            4        1
7  Moulton                5            0        0
etc...
________________________________________________________
________________________________________________________

**SQL Report Setup**

*Title*
Company Production Systems Count by Site and Type (All)

*Category*
Hardware (Custom)

*Description*
Client, control & server machine count and list for all company sites.

*SQL Select Statement*
SELECT DESKTOP.LOCATION as 'Location', COALESCE(DESKTOP.TOTAL, 0) as 'Desktops',
COALESCE(LAPTOP.TOTAL, 0) as 'Laptops', COALESCE(CONTROL.TOTAL, 0) as 'Control PCs',
COALESCE(SERVER.TOTAL, 0) as 'Servers'
FROM (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike ' desktop')
GROUP BY LOCATION) DESKTOP
LEFT JOIN (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike ' laptops')
GROUP BY LOCATION) LAPTOP ON (DESKTOP.LOCATION = LAPTOP.LOCATION)
LEFT JOIN (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'control')
GROUP BY LOCATION) CONTROL ON (DESKTOP.LOCATION = CONTROL.LOCATION)
LEFT JOIN (SELECT SUBSTRING(L.NAME, 1, length(L.NAME) - 8) as LOCATION, COUNT(M.NAME) as TOTAL
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'servers')
GROUP BY LOCATION) SERVER ON (DESKTOP.LOCATION = SERVER.LOCATION)
ORDER BY LOCATION
________________________________________________________

**Example Output**

Title: Company Production Systems Count by Site and Type (All)
Description: Client, control & server machine count and list for all company sites.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/07/31 12:53:57

#  Location         Desktops  Laptops  Control Pcs  Servers
1  East Canton            17        2            2        2
2  Greensboro             34        2            4        2
3  Hammond                15        2            1        1
4  Hillsborough            3        0            0        0
5  King Of Prussia         1        2            0        0
6  Marelan                22        2            4        1
7  Moulton                 5        0            0        0
etc...
________________________________________________________
________________________________________________________

Hope that helps someone!

John