Topic
  • 153 replies
  • Latest Post - ‏2015-03-17T09:08:08Z by KarenKue
Lee Wei
Lee Wei
599 Posts

Pinned topic BigFix Excel Connector

‏2009-07-18T02:28:52Z |

See more information at the developerWorks Wiki

IBM Endpoint Manager (IEM) operators often find that it is useful to extract the wealth of data on the IEM Server for analysis and reporting. They either process the data themselves, or will often send the information to other consumers in an Excel format.

Excel Connector

 

Installation


Screen Casts
A simple walk through of selecting some Computer Properties (47 secs)
http://leewei.com/bigfix/prod/excelconnector/BESComputers/BESComputers.html

The new feature to extract single Retrieve Property summary (34 secs)
http://leewei.com/bigfix/prod/excelconnector/BESProperties/BESProperties.html (34 secs)

The new features to Import, Open and Save reports
http://leewei.com/bigfix/prod/excelconnector/ImportOpenSave/ImportOpenSave.html (1 min 28 secs)

Show the generated relevance in nicely formatted output with indentations, without going through a hidden worksheet
http://leewei.com/bigfix/prod/excelconnector/ShowRelevance/ShowRelevance.html (1 min 21 secs)

Installation Problems and What to try

  • Right-click on setup.exe and "Run as Administrator"
  • If you have .NET Framework 4.0, you need the Full version, not the Client/light version

Read post: http://forum.bigfix.com/viewtopic.php?pid=24824#p24824

 

  • Some people reported problem installing on Vista with UAC turned on. Symptom is that the add-in does not show up in Excel
  • The installer log file is located at \user_home\Add-in Express. Any errors will be visible there


Change History

Changes in version 3.3.3.0

  • (2013/04/16) Changed name to IBM Endpoint Manager (IEM) Excel Connector .
  • (2013/04/16) Added support for Excel 2013.
  • (2013/04/16) Fixed error with Regional and Language Formats that are non-English
  • (2013/04/16) Fixed error with searching for action status using the "Results of BES Action" object


Changes in version 3.3.1.0

  • (10/17/2011) Increased maximum rows for Excel 2007 and Excel 2010 to 1,048,575.


Changes in version 3.3.0.0

  • (3/30/2011) Change name from BigFix to Tivoli Endpoint Manager
  • (3/30/2011) For computer properties, change generated relevance from (if exists result) to (if exists values of results) because some results might be NULL.
  • (3/30/2011) Fixed concatenation character %0A not working in Web Reports version 8.1. This is due to Web Reports escaping control characters.
  • (3/30/2011) Fixed Analysis properties for BES Computers not being restored.
  • (3/30/2011) Upgraded installer using Add-in Express from 6.1.3044 to 6.4.3056


Changes in version 3.2.0.0

  • (11/27/2010)
  • Use explicitly IDs to identify Retrieved Properties. This eliminates problems with duplicate names
  • Added processing to "Free Space on System Drive", "RAM", and "Total Size of System Drive" to make them integers rather than strings


Changes in version 3.1.0.0

  • (11/12/2010) Too many changes to list individual items. The bigger enhancements are:
  • Open and save reports
  • Import and export reports
  • Refresh the report without going through the Wizard
  • Show the generated relevance with indentations
  • A single BES Property report is generated with a pie chart


Changes in version 2.0.0.0

  • (8/16/2010) Support Excel 2010.


Changes in version 1.4.0.0

  • (7/26/2010) Clear worksheet formatting when returning results from Session Relevance Editor.
  • (7/26/2010) Refresh content cache when switching Web Reports.


Changes in version 1.3.0.0

  • (7/26/2010) Fix bug found by Nick Holmes on Forum http://forum.bigfix.com/viewtopic.php?id=5394. The filter does not work against properties with multiple results.


Changes in version 1.2.0.0

  • (7/24/2010) Supports HTTPS connection with private certificates.
  • (7/24/2010) Fixed problem with Web Reports URL with trailing forward slash (e.g. http://server:80/).


Bug fixes in version 1.1.0.0

  • (7/18/2009) comboBoxProperty for filter name not concatenating property if it is a property of an object.
  • (7/18/2009) When Checking a parent node of a list of property names, and if there are children already checked, they get duplicated in the selected list.
  • (7/21/2009) Tab order for fields (3 of them) used to create a filter corrected.
  • (7/24/2009) Missing Analysis properties if multiple activations. Helped fixed by Stephane Minisini of eSpeed.


Enhancements in version 1.1.0.0

  • (7/20/2009) Added auto-complete functionality to the comboBoxProperty - to select Properties for filtering.
  • (7/21/2009) Combined 2 BES Site queries into one to improve caching speed.
  • (7/21/2009) Added Remediated attribute to BES Fixlets. Also possible to search on Remediated.
  • (7/21/2009) Made the "Value" column in the filter data grid editable. This makes it easier to change a query criteria.


Lee Wei

 

  • BenKus
    BenKus
    597 Posts

    Re: BigFix Excel Connector

    ‏2009-07-20T07:08:28Z  
    Hey everybody,

    This is a very cool tool that you can try to make customized reports based in Excel but powered with BigFix data... I definitely recommend that you try it out...

    Ben
  • jnmoore91
    jnmoore91
    45 Posts

    Re: BigFix Excel Connector

    ‏2009-07-20T16:23:28Z  
    This will be extremely useful for my customers! I can't wait until we migrate to BigFix 7.2!

    Question: Why can't it work on previous versions? I've taken a look at the EvaluateRelevance() JS function (web reports v7.1.x), and it simply opens a request to the webreports page, and I bet the plug in is doing the same thing, because it requires the web reports URL, username, and password. Am I wrong? :)

    --Jerroyd
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2009-07-20T16:48:20Z  
    Jerroyd,

    You are correct that the Connector is using the SOAP API to access Web Reports.
    We modified the SOAP XML namespace in version 7.2. Some language bindings are very forgiving (Perl for example), but the .NET Framework used for the Excel Connector insist on strong type checking.

    Lee Wei
  • Tima91
    Tima91
    1 Post

    Re: BigFix Excel Connector

    ‏2009-07-21T04:21:01Z  
    Is there any way I can use this with bigfix version 7.0 :-) I can't wait for upgrading to version 7.2?
  • jnmoore91
    jnmoore91
    45 Posts

    Re: BigFix Excel Connector

    ‏2009-07-21T16:21:03Z  
    I doubt it, unless they provide the source code. Which would be wonderful, but probably unlikely :(

    --Jerroyd
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2009-07-21T18:30:57Z  
    Tima,

    Yeah, sorry that this won't work for you right now.
    As Jerroyd had guess correctly, source code is difficult because of a few reasons. One being some externally licensed components.

    Lee Wei
  • jnmoore91
    jnmoore91
    45 Posts

    Re: BigFix Excel Connector

    ‏2009-07-30T18:16:29Z  
    Finally migrated to 7.2.

    I noticed a bug after you query results from the query wizard (I queried from "Results of BES Fixlets"). If you go back to the query wizard and edit the list of attributes, you cannot remove the first query's selected attributes, additionally, any attributes you select from a folder (say the fixlet folder), as an additional "of fixlet" (ex: analysis flag of Fixlet of Fixlet). I'm using MS Excel 12.0 - 2007
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2009-07-31T22:17:35Z  
    Jerroyd,

    Thanks for reporting this. I believe this is fixed in the latest version. I sneaked in a v1.1 release 7/20/09.
    If you don't have a Wizard saying this is v1.1, then you have the older version.

    The most useful enhancement is the addition of the "Remediated" attribute for the BES Fixlet object. This is not a normal attribute, but a computed field.

    Enhancements in version 1.1.0.0
    • (7/20/2009) Added auto-complete functionality to the comboBoxProperty - to select Properties for filtering.
    • (7/21/2009) Combined 2 BES Site queries into one to improve caching speed.
    • (7/21/2009) Added Remediated attribute to BES Fixlets. Also possible to search on Remediated.
    • (7/21/2009) Made the "Value" column in the filter data grid editable. This makes it easier to change a query criteria.

    Lee Wei
    http://www.leewei.com/bigfix/prod/forum_images/excel_connectorv11.png
  • jnmoore91
    jnmoore91
    45 Posts

    Re: BigFix Excel Connector

    ‏2009-08-06T14:47:22Z  
    Hah! Thanks,

    Since it looks like you're actively developing this tool, I would love to request a feature: Under results of BES Fixlets / Actions, There is a tier for computers, but you can only select their names. My customers would find it EXTREMELY useful to be able to select any number of BES Properties for computers of these two inspector objects :D
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2009-08-06T19:41:30Z  
    jnmoore,

    I will work on including a few common computer properties in the next version. These will be OS, IP address and last report time.

    Lee Wei
  • kunrue91
    kunrue91
    1 Post

    Re: BigFix Excel Connector

    ‏2009-08-27T16:28:30Z  
    I've installed the tool, but I'm not sure how to invoke it. What am I missing?
    Thanks.
  • Jim23
    Jim23
    4 Posts

    Re: BigFix Excel Connector

    ‏2009-08-27T17:44:48Z  
    In excel:
    View | Toolbars | BigFix Connecter.
  • rdamours91
    rdamours91
    20 Posts

    Re: BigFix Excel Connector

    ‏2009-09-06T07:32:47Z  
    Very nice work guys. I've been buried alive with other projects and haven't been keeping up with the latest cool stuff.

    Keep up the good work.
  • MrFixit
    MrFixit
    20 Posts

    Re: BigFix Excel Connector

    ‏2010-01-11T22:18:33Z  
    This is an awesome tool.

    Can you add BES Custom Sites to the Inspector objects. We are starting to use custom sites in a big way and it really would be nice to have it available.

    Also do you have plans to include come of the other inspector types that are listed in the Session Inspector guide?

    I would also like to be able to save queries and open saved queries directly from the Query Wizard.

    thanks again for a cool tool,
    -Gary
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2010-01-12T04:05:08Z  
    Hello Gary,

    Some very good ideas there and reasonable requests. Especially the saving of queries and others have asked for similar functionality.

    • What properties do you need from BES Custom Sites? It is possible to issue Relevance statements to retrieve the results using the Session Relevance Editor.
    • What other objects (inspector types) have you come across that you believe might be useful?
    • If you save the spreadsheet, the Wizard will remember all your options. There is currently a hidden worksheet created name "BigFixExcelConnector" that contains various info about the query.

    Lee Wei
  • CCupit91
    CCupit91
    23 Posts

    Re: BigFix Excel Connector

    ‏2010-02-19T13:28:39Z  
    Hi,

    Do you think any of the following are/could be possible?

    1).
    Run a relevance as a excel macro?

    Include data from excel in a session relevance execution.. Ie.

    Have worksheet with a list of fixlet ID's and run a macro that picks up each of those ID's and then runs a session relevance command against each one of them publishing the data into seperate tabs and then performing further work on them within the macro.

    2). Save session relevance command within the spreadsheet (so we can have complex statements within a sheet we can just open and hit a button to refresh the data). I guess we could run it in the session relevance tester but that appears (not tested yet as it's just on our dev platform) to be limited to 1000 rows? (some of our queries will return 30,000 to 100,000 rows).

    Thanks,
    Chris
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2010-02-20T00:58:14Z  
    Chris,

    1. With programming, it is possible, but nothing trivial or available out-of-the-box. With Excel macros, we have access to Visual Basic. All the work that I do with the BigFix Connector uses the SOAP API.

    2. With the Session Relevance Tester, the 1,000 limit is just a random number I picked as not to overwhelm the client. You can change that right up on the button tool bar where it says "Max Rows". The version out there has a hardcoded limit where the SOAP call will timeout after 100 seconds. So if your statement takes a while, it will abort after 100 seconds. I can recompile a version without the limit if needed.

    Lee Wei
  • SystemAdmin
    SystemAdmin
    890 Posts

    Re: BigFix Excel Connector

    ‏2010-02-24T21:32:17Z  
    The excel plug in is a great tool.

    When we run the field on a specific field that has a large amount of data we get a message back that states cells over 900 chars have been truncated.

    Any suggestions ?
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2010-02-24T22:11:47Z  
    Kryan762,

    Yeah, unfortunately, this is an Excel bug:

    911 characters in Excel 2003 - http://support.microsoft.com/kb/818808
    8203 characters in Excel 2007 - http://support.microsoft.com/kb/832136

    If you are using Excel 2007, and you get the 900 character error, I must have gotten lazy and just limited it to 900 across the board, rather than checking your Excel version...

    Lee Wei
  • SystemAdmin
    SystemAdmin
    890 Posts

    Re: BigFix Excel Connector

    ‏2010-02-24T22:17:07Z  
    Thank you !
  • spritekk
    spritekk
    1 Post

    Re: BigFix Excel Connector

    ‏2010-04-02T00:04:00Z  
    My machine was recently reimaged and I am running Win7 and Office 2010 Beta.
    Same configuration, no changes as I had before. I had previously used the Excel connector extensively, albeit with minor annoyances at launch of excel.

    I have just reinstalled the Excel connector, and its a no-show in Excel. When I look at COM Add-ins, it is listed as available, but Load Behavior: Not loaded. A runtime error occurred during the loading of the COM Add-in.

    Any suggestions? or assistance?
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2010-04-02T00:28:31Z  
    I don't have Office 2010. Maybe someone else can help us determine if this is a general Office 2010 issue.
    Does anyone else have Win7 and Office 2010?

    Lee Wei
  • Lee Wei
    Lee Wei
    599 Posts

    Re: BigFix Excel Connector

    ‏2010-05-06T14:10:49Z  
    mjbennett9,

    I will ask someone to look at the download page. Sorry about that.
    Meanwhile, you can find the MSI here:
    http://www.leewei.com/bigfix/prod/excelconnector/BigFix_Excel_Connector_Setup_v11.msi

    Lee Wei
  • mjbennett991
    mjbennett991
    5 Posts

    Re: BigFix Excel Connector

    ‏2010-05-06T14:12:48Z  
    The excel connector seems to be missing?

    Clicked here: http://support.bigfix.com/labs/excelconnect.html Then I accepted the license agreement here: http://support.bigfix.com/labs/license.html which took me here: http://support.bigfix.com/labs/downloads/ and no connector?

    :-(