Scripting

For a K1000 MySQL primer, see this article:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

Honestly this is way too much to try and cram into one report and what I've included is jammed, but see if this gets most of your requirements.  If you need to be more specific on some items, it would probably be worthwhile to either:

1) Break things down into more discrete reports.

2) Spend some time learning how to write reports so you can create very specific filters (and subqueries where necessary).

3) Contract someone willing to spend hours meeting your exact requirements, with access to your systems.

To just target your servers, you'll want to limit the report to run against them by label (there was just a post on how to do this).

SELECT M.NAME as MACHINE_NAME,
DA.SERVICE_TAG,
DATE_FORMAT(DA.SHIP_DATE,'%Y/%m/%d') as PURCHASE_DATE,
M.IP as IP_ADDRESS,
M.OS_NAME as OS_NAME,
M.OS_VERSION as OS_VERSION,
M.SERVICE_PACK as OS_SERVICE_PACK,
M.OS_BUILD as OS_BUILD,
M.BIOS_VERSION,
M.BIOS_SERIAL_NUMBER as SERIAL_NUMBER,
MD.NAME as DISKS,
M.RAM_TOTAL as MEMORY,
M.PROCESSORS as PROCESSORS,
GROUP_CONCAT(DISTINCT DW.START_DATE ORDER BY 1 SEPARATOR '***') as WARRANTY_START,
GROUP_CONCAT(DISTINCT DW.END_DATE ORDER BY 1 SEPARATOR '***') as WARRANTY_END,
GROUP_CONCAT(DISTINCT DW.SERVICE_LEVEL_DESCRIPTION ORDER BY 1 SEPARATOR '***') as SERVICE_LEVEL,
GROUP_CONCAT(DISTINCT DW.SERVICE_PROVIDER ORDER BY 1 SEPARATOR '***') as SERVICE_PROVIDER,
GROUP_CONCAT(DISTINCT S.DISPLAY_NAME ORDER BY 1 SEPARATOR '***') as MICROSOFT_APPLICATIONS,
M.MOTHERBOARD_PRIMARY_BUS as CHIPSET,
GROUP_CONCAT(DISTINCT MN.NIC ORDER BY 1 SEPARATOR '***') as NETWORK_INTERFACE_CARD,
M.VIDEO_CONTROLLERS as VIDEO_INTERFACE
FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA on (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
LEFT JOIN MACHINE_DISKS MD on (MD.ID = M.ID)
LEFT JOIN KBSYS.DELL_WARRANTY DW on (DW.SERVICE_TAG = DA.SERVICE_TAG)
LEFT JOIN MACHINE_SOFTWARE_JT MS on (MS.MACHINE_ID = M.ID)
LEFT JOIN SOFTWARE S on (S.ID = MS.SOFTWARE_ID)
LEFT JOIN MACHINE_NICS MN on (MN.ID = M.ID)
WHERE NOT S.IS_PATCH
AND DISPLAY_NAME rlike 'microsoft|sql'
GROUP BY MACHINE_NAME
ORDER BY M.NAME

I'm not sure about BIOS date - there's only BIOS_NAME, BIOS_VERSION, BIOS_MANUFACTURER, BIOS_DESCRIPTION, BIOS_IDENTIFICATION_CODE and BIOS_SERIAL_NUMBER in the MACHINE TABLE, so I subbed in the BIOS version.

Also not sure about the Disk Configuration - there's only ID, NAME, DISK_USED, DISK_FREE, DISK_SIZE and PERCENT_USED in the MACHINE_DISKS table, so I subbed in MACHINE_DISKS which gives a good bit of info.  Maybe look into using a custom inventory rule to capture this.

I added the SERVICE_LEVEL_DESCRIPTION and SERVICE_PROVIDER columns from the DELL_WARRANTY table, as these are just useful to have (particularly when you have multiple warranties listed for machines).

I left the some other items off (MS Service pack, Database Patch, DB Patch Release Level, Drivers) off, as these would ideally be targeted with a more specific report.  Honestly, all of this is a bit much for one (legible) report.

For the GROUP_CONCAT statements, you'll probably want to change the separator to '\n' once the bug has been fixed in the Reports section (presently doesn't process it correctly, unless you have the Classic Reports which does).

Again, this is probably too much for a legible report, but here you go and hope that helps!

John