Report on Custom Inventory
I've got a custom inventory rule that gets the following bitlocker info:
BitLocker Version: None
Conversion Status: Fully Decrypted
Percentage Encrypted: 0%
Encryption Method: None
Protection Status: Protection Off
Lock Status: Unlocked
Identification Field: None
Key Protectors: None Found
[string]
I've created a report that will give name, mac, IP, and that bitlocker info. My problem is the way the output appears.
BitLocker Version: None<br/> Conversion Status: Fully Decrypted<br/> Percentage Encrypted: 0%<br/> Encryption Method: None<br/> Protection Status: Protection Off<br/> Lock Status: Unlocked<br/> Identification Field: None<br/> Key Protectors: None Found<br/
I would rather the output be sorted more like the custom inventory. Any help would be appreciated.
Here is my SQL code:
SELECT
MACHINE.NAME AS SYSTEM_NAME,
IP,
MAC,
(SELECT
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
FROM
MACHINE_CUSTOM_INVENTORY
WHERE
MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID
AND
MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=2370
) AS MACHINE_CUSTOM_INVENTORY_0_2370
FROM MACHINE
WHERE (
(1 IN (
SELECT 1
FROM
MACHINE_CUSTOM_INVENTORY
WHERE
MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
AND
MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 2370
AND
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE like '%%')
)
) ORDER BY SYSTEM_NAME
Answers (3)
http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields
Here's a blog I did covering a workaround I use for this, I'm sure you could tweak it to your liking:
http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields
John
Comments:
-
Thanks! That works.... I would have drove myself crazy looking for that. I never think of bugs. Hopefully that will get fixed this next rev. - dugullett 12 years ago
-
Where is your post that shows tweaking the HTML? I think I'm going to end up doing that. - dugullett 12 years ago
-
http://www.itninja.com/question/ldap-patching-sql-reports-using-all-three-for-efficient-managed-patching-and-other-cool-tric
The formatting got messed up during a past site upgrade, but hopefully it's still useable:
I created all of these reports using the old reporting tool (no longer available in K1200+, from what I understand) and continue to do so as I like the PDF output. However, I did test these using the new reporting tool and they still work fine. The reason I mention this is because if anyone else out there wants to use the old reporting tool for PDF output of these reports, the column widths will be... less than satisfactory. That being said, I found a workaround for fixing the column widths by editing the generated HTML code. To do this, create the report by populating all of the fields (Title.... SQL Select Statement, etc), make sure the Auto-generate Layout checkbox is checked, then hit Save. This should take you back to the Classic Reports screen - click on the report again and you should see the XML Report Layout (if not, uncheck the Auto-generate Layout checkbox). Scroll about halfway down and do a search on "ColumnHeaderFooter", and you should hit a line similar to this: Starting from here, change the values as in the table below for each of the three instances (there three columns in the reports above - Patch Name, Computer Name, Windows Version). Then do the same for "Detail", which will have the same type of layout. Click Save, run the report and you should be in good shape. Just keep in mind that if you do need to edit the SQL code, you'll need to re-check the Auto-generate Layout checkbox afterward and save the report, which will undo your XML code changes (i.e. you'll need to adjust them again). Max = 782 (style="ColumnHeaderFooter") (style="Detail") x= width= 0 30 30 625 655 127
John - jverbosk 12 years ago -
I really wish Comments would show up in the Activity feed so I know when people reply to answers...
John - jverbosk 12 years ago
After checking http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields my code has change to include REPLACE. Built with classic reports.
SELECT MACHINE.NAME AS SYSTEM_NAME, IP, MAC, REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=2370), '<br/>', '\r\n') AS MACHINE_CUSTOM_INVENTORY_0_2370 FROM MACHINE WHERE (1 in (select 1 from MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 2370 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE LIKE '%b%')) ORDER BY MACHINE.NAME asc
BitLocker Version: None
Conversion Status: Fully Decrypted
Percentage Encrypted: 0%
Encryption Method: None
Protection Status: Protection Off
Lock Status: Unlocked
Identification Field: None
Key Protectors: None Found
OK, that's a little hard to follow... try this example (with three columns) for editing column widths in the XML. If it's still hard to follow, just let me know.
Hope that helps!
John
_________________________________________
In the XML Report Layout code (which should be auto-generated to create the code)
*Max total column width is 782*
1) Adjust the width variable under style="ColumnHeaderFooter" & style="Detail" (3/4 down - 1st x = 0) so that total = 782
to adjust column width in SQL reports
2) Adjust the x variable under style="ColumnHeaderFooter" &
style="Detail" (3/4 down - 1st x = 0) so that x = total width of previous columns
_________________________________________
Ex:
1) Original
Column width 1 = 51
Column width 2 = 150
Column width 3 = 567
1st x value = 0
2nd x value = 51
3rd x value = 212
2) Changed - Test
Column width 1 = 150
Column width 2 = 567
Column width 3 = 51
1st x value = 0
2nd x value = 150
3rd x value = 717
_____________________________
printOrder="Vertical"
orientation="Landscape"
pageWidth="828"
pageHeight="595"
columnWidth="768"
columnSpacing="0"
leftMargin="30"
rightMargin="30"
topMargin="20"
bottomMargin="20"
_____________________________
*TEST*
Original:
PC column width 51
IP Address column width 150
Printers list column width 567
Changed - Test:
PC column width 150
IP Address column width 567
Printers list column width 51
_____________________________
* ColumnHeaderFooter (Original)
style="ColumnHeaderFooter"
x="0"
y="1"
width="51"
height="15"
style="ColumnHeaderFooter"
x="51"
y="1"
width="150"
height="15"
style="ColumnHeaderFooter"
x="201"
y="1"
width="567"
height="15"
_____________________________
* ColumnHeaderFooter (Changed - Test)
style="ColumnHeaderFooter"
x="0"
y="1"
width="150"
height="15"
style="ColumnHeaderFooter"
x="150"
y="1"
width="567"
height="15"
style="ColumnHeaderFooter"
x="717"
y="1"
width="51"
height="15"
_____________________________
* Detail (Original)
style="Detail"
x="0"
y="1"
width="51"
height="13"
style="Detail"
x="51"
y="1"
width="150"
height="13"
style="Detail"
x="201"
y="1"
width="567"
height="13"
_____________________________
* Detail (Changed - Test)
style="Detail"
x="0"
y="1"
width="150"
height="13"
style="Detail"
x="150"
y="1"
width="567"
height="13"
style="Detail"
x="717"
y="1"
width="51"
height="13"
Comments:
-
Thanks.... I found this link by chance. I've been using it, and it's nice what all you can change.
http://www.kace.com/support/resources/kb/article/Customizing-a-Report-Layout-With-Jasper-iReport - dugullett 12 years ago -
Very cool, I just figured mine out by trial and error one day, but it's good to have official documentation.
John - jverbosk 12 years ago