BigFix Excel Connector

Reporting
1

(imported topic written by Lee Wei)

See more information at the developerWorks Wiki

IBM BigFix 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.

Installation

  • Download the latest version, which is currently 3.3.8
  • Uninstall any previous versions
  • The best way to install is to right-click on setup.exe and “Run as administrator”

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.8.0

  • (2017/05/04) Switched to .NET Framework 4.5.2 from 2.0
  • (2017/05/04) Supported TLS 1.2

Changes in version 3.3.7.0

  • (2016/08/26) Added additional properties to objects, Display Name of sites

Changes in version 3.3.6.0

  • (2016/07/22) Added additional properties to objects, Comments of Computers. Added (exists xxx | false) clause in the event of empty values

Changes in version 3.3.5.0

  • (2016/07/14) Added additional properties to objects (e.g. Names of BES Computer Groups, CVE ID List to results of BES Fixlets)

Changes in version 3.3.4.0

  • (2016/05/25) Changed name from IBM Endpoint Manager to IBM BigFix
  • (2016/05/25) Upgraded installer to support Excel 15 (MS Office 2016)
  • (2016/05/25) Upgraded Add-in Express from 7.1.4050.1 to 8.1.4350

Changes in version 3.3.3.0

  • (2013/03/15) Changed name from Tivoli Endpoint Manager to IBM Endpoint Manager
  • (2013/03/15) Upgraded installer to support Excel 15 (MS Office 2013)
  • (2013/03/15) Upgraded Add-in Express from 6.4.3056 to 7.1.4050.1

Changes in version 3.3.2.0

  • (9/7/2012) Fixed construct to query BES Property from id of it = (x, y, z) to (item 0 of it = x and item 1 of it = y and item 2 of it = z) of id of it

Changes in version 3.3.1.0

  • (10/17/2011) Changes max rows for Excel 2007 and Excel 2010 to 1,048,575

Changes in version 3.3.0.0

  • (3/30/2011) Changed name from BigFix to Tivoli Endpoint Manager
  • (3/30/2011) For computer properties, changed generated relevance from (if exists result) to (if exists values of results) because some results are 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 Add-in Express from 6.1.3044 to 6.4.3056

Changes in version 1.4.0.0

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

Changes in version 1.3.0.0

Changes in version 1.2.0.0

  • (7/24/2010) Supported 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

1 Like
2

(imported comment written by BenKus)

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

3

(imported comment written by jnmoore91)

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? :slight_smile:

–Jerroyd

4

(imported comment written by Lee Wei)

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

5

(imported comment written by Tima91)

Is there any way I can use this with bigfix version 7.0 :slight_smile: I can’t wait for upgrading to version 7.2?

6

(imported comment written by jnmoore91)

I doubt it, unless they provide the source code. Which would be wonderful, but probably unlikely :frowning:

–Jerroyd

7

(imported comment written by Lee Wei)

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

8

(imported comment written by jnmoore91)

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

9

(imported comment written by Lee Wei)

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

10

(imported comment written by jnmoore91)

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 :smiley:

11

(imported comment written by Lee Wei)

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

12

(imported comment written by kunrue91)

I’ve installed the tool, but I’m not sure how to invoke it. What am I missing?

Thanks.

13

(imported comment written by Jim23)

In excel:

View | Toolbars | BigFix Connecter.

14

(imported comment written by rdamours91)

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.

15

(imported comment written by MrFixit)

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

16

(imported comment written by Lee Wei)

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

17

(imported comment written by CCupit91)

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

18

(imported comment written by Lee Wei)

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

19

(imported comment written by SystemAdmin)

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 ?

20

(imported comment written by Lee Wei)

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