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

 


Answer Summary:
http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

4

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

Answered 06/21/2012 by: jverbosk
Red Belt

  • 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.
  • Where is your post that shows tweaking the HTML? I think I'm going to end up doing that.
  • 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
  • I really wish Comments would show up in the Activity feed so I know when people reply to answers...

    John
Please log in to comment

Answers

3

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

 

 

Answered 06/21/2012 by: dugullett
Red Belt

Please log in to comment
0

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"

Answered 06/22/2012 by: jverbosk
Red Belt

  • 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
  • Very cool, I just figured mine out by trial and error one day, but it's good to have official documentation.

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