BigFix Excel Connector

Reporting
81

(imported comment written by Lee Wei)

Is there any way to modify the relevance code generated by the query wizard and then regenerate the excel spreadsheet? I tried the session relevance editor, but it doesn’t appear to automatically split rows on the “$x$” delimiter like the query wizard does.

Hi Boyd,

No, there is not a break out to change the generated code right now before processing. I provided the code more as an FYI after fact. You are also correct that the delimiter is only used for the Excel Connector internally.

We have a few properties that use the same name (we have a lot of console operators, so conflicts sometimes happen). This creates unexpected results because the query wizard appears to use "bes property " and that will only return the first property of the same name. Is there any possibility that this could be reworked to go by property ID instead?

If you are using the latest version 3.2.0.0, I would have expected duplicate property names to be taken care of. When I detect duplicate names, I use the property ID construct as you have suggested. So why not just use IDs across the board? I tested using the ID versus "bes property " and the performance difference is startling. So I am left with deciding whether to resolve conflict, or to get performance. Isn’t the answer to everything almost always somewhere in the middle? So I use "bes property " when no duplicates, and IDs when there are duplicates.

Not to beat a dead horse, but this is another program that bug #38567 is affecting (http://forum.bigfix.com/viewtopic.php?pid=24212#p24212). Any news on when this bug will be fixed? It’s affecting a query that I wanted to generate for Installed Applications. I wanted to use Excel to make it easier to do sorting and grouping.

The bug is fixed in the next release, probably version 8.1 and code name “Euclid”. We expect this to be available in early February. Incidentally, the main purpose of this release is to rebrand everything as IBM. However, we do slip in a lot of fixes as appropriate.

Lee Wei

82

(imported comment written by MattBoyd)

Lee Wei

If you are using the latest version 3.2.0.0, I would have expected duplicate property names to be taken care of. When I detect duplicate names, I use the property ID construct as you have suggested.

Hmm… looks like I downloaded 3.1 from http://support.bigfix.com/labs/downloads/. I see the link for 3.2 is in this thread, so I’ll give that a try. Thanks!

So why not just use IDs across the board? I tested using the ID versus "bes property " and the performance difference is startling. So I am left with deciding whether to resolve conflict, or to get performance. Isn’t the answer to everything almost always somewhere in the middle? So I use "bes property " when no duplicates, and IDs when there are duplicates.

That’s a reasonable compromise given the circumstances.

The bug is fixed in the next release, probably version 8.1 and code name “Euclid”. We expect this to be available in early February. Incidentally, the main purpose of this release is to rebrand everything as IBM. However, we do slip in a lot of fixes as appropriate.

Thanks, I’m looking forward to the update. As always, thanks for sharing your awesome work Lee!

Matt

83

(imported comment written by SystemAdmin)

Hi,

I’ve installed the Excel Connector v3.2 on Windows 2007 32-bit notebook with Excel 2010. The install went perfectly but was unable to see the Connector add-in once I launched Excel. Looking at File | Options | Add-Ins, I see BitFixExcelConnector as a Com Add-in… but its not selected. The load behavior says “unloaded”. I’ve uninstalled, repaired and reinstalled both the connector and Excel… to no avail. Any suggestions? Also… installed by using “run as administrator”.

Thanks in advance,

Mark

84

(imported comment written by Lee Wei)

I have set up a private chat with Mark next week.

I am always interested in any challenges using the tool, including installation.

If we find anything useful, we will fix and post the info.

Lee Wei

85

(imported comment written by hbkrules6991)

Hi Lee:

FIrst of all this is a great add-in…I have one question though and this may have been answered, but are these reports a direct connection to the Database? i.e. if I delete a field will it subsequently remove it from the database? or is the data read-only?

Thanks!

Paul

86

(imported comment written by Lee Wei)

Paul,

The Excel Connector uses the SOAP API, so it connects to the BigFix Web Reports server which is servicing the data.

http://support.bigfix.com/bes/misc/soapapi.html

There is no direct connection to the SQL database.

The SOAP API is read-only, and the Excel Connector is read-only as well.

Once you execute the queries, there is no longer any connection to the backend. Any references to saving the query is saving the definition, so that the same query can be used again.

Lee Wei

87

(imported comment written by Miran_p91)

If I go query wizard > Bes propertys > OS > Execute query I get

Exception from HRESULT:0x800A0#EC

I have graph colum empty and no graph displayed like in demo video.

Also Concatenate multi-value Resoults usig %0A is not working for me. I get this inserted betwen IPs and not in new lines.

I’m using excell 2007 and win 7, any ideas what is cousing this?

88

(imported comment written by jonrobinson91)

I’m using the excel connector but it won’t let me select more than one property (see screen shot). I’m using connector 3.2 with excel 12. I also tried in excel 11. The machine is Windows XP. I see in the video and other screen shots that there are checkboxes. Am I doing something wrong?

https://img.skitch.com/20110416-gsx273hmw5qxgqacd8hr5kbc2f.preview.jpg

Click for large view

color=#A7A7A7

Uploaded with

Skitch

[/color]

89

(imported comment written by Lee Wei)

jonrobinson

I’m using the excel connector but it won’t let me select more than one property (see screen shot).

Hi Jon,

One the first page of the Wizard, you want to select BES Computers as the Object, which will then give you the option to select multiple properties for the computers.

The BES Properties object is intended to view just one specific property.

Lee Wei

90

(imported comment written by Lee Wei)

Miran_p

Also Concatenate multi-value Resoults usig %0A is not working for me. I get this inserted betwen IPs and not in new lines.

This is fixed in version 3.3, which is available for download from the first post.

Web Reports 8.1 fixes the issue of handling control characters by escaping it. This means that %0A is no longer recognized as linefeed, rather just a literal string.

I have fixed this by inserting a new line characters explicitly in the Excel cell when multiple rows of data are returned.

91

(imported comment written by murtasma91)

Hi Lee great tool thanks for providing the community with such a useful tool. I haven’t played around with it sometime but it looks like you made lots of improvements in the latest version.

I’m trying to create a querty that will show all open actions that have no end date set. Unfortantly when I try to filter on the end date the GUI requires that I select a date. I tried to modify the query using the session relevance editor but I’m not sure what to change the code so it will return the results I need.

BES Actions

whose (

(End Date of it = “Tue, 19 Apr 2011” as date) AND

(State of it as lowercase contains “open”))

I’ve tried several different modifications but I keep getting errors. I’m sure it’s just because I’m not that familar with relevance yet.

92

(imported comment written by Lee Wei)

Daniel,

The GUI does not allow for entering any dates other than a proper date format.

Where are you editing the query? There are no hooks to change the query from within the Wizard right now.

However, within the Excel Connector, you can use “Session Relevance Editor” button on toolbar. Here is an example statement for your query.

names of bes actions whose (
state of it as lowercase contains “open” and
not exist end date of it
)

93

(imported comment written by murtasma91)

Perfect that worked great, there was a report I downloaded from the forum some time ago that would show all open actions with no expiration date but it really just showed all open actions. I was able to modify the relevance in the report to filter out only actions with no end date.

Thanks for your help!

94

(imported comment written by SystemAdmin)

I was not able to get the 3.2 Add-in to show in Excel. I’ve uninstalled .net 4 and re-installed with the full version. I’ve tried the MSI as well as the setup running as administrator. I’m on Win7 64bit with Office 2007. I have a log file that shows success but its shorter than someone else previously posted. A colleague mentioned trying to install the 1.4 connector first then 3.2 and that actually worked. Note that when I installed 1.4 I just ran the MSI and when I installed 3.2 I ran setup.exe as administrator.

Add-in Express Registrator Log File: 04/20/2011 16:19:54

Installation directory: C:\Program Files (x86)\BigFix Enterprise\Excel Connector\

Registrator version: 6.1.3044.0

16:19:54 Starting the add-in registration process.

16:19:54 Loading mscoree.dll

16:19:54 Success.

16:19:54 .NET Framework installation directory:

16:19:54 The latest version of .NET Framework: ‘v4.0.30319’

16:19:54 Loading CLR: v4.0.30319.

16:19:54 Calling CLRCreateInstance method.

16:19:54 Success.

16:19:54 Calling GetRuntime method.

16:19:54 Success.

16:19:54 Calling GetInterface method.

16:19:54 Calling GetInterface method for the CorRuntimeHost interface.

16:19:54 Success.

16:19:54 Starting CLR…

16:19:54 Success.

16:19:54 The CLR v4.0.30319 has been initialized successfully.

16:19:54 Creating a new domain setup.

16:19:54 Success.

16:19:54 Getting the add-in directory.

16:19:54 Success. The directory is ‘C:\Program Files (x86)\BigFix Enterprise\Excel Connector’

16:19:54 The configuration file is ‘(null)’

16:19:54 The ‘shadow copy’ is disabled.

16:19:54 Creating a new application domain.

16:19:54 Success.

16:19:54 Getting the base directory for the domain.

16:19:54 Success. The directory is ‘C:\Program Files (x86)\BigFix Enterprise\Excel Connector’.

16:19:54 Searching for the Add-in Express core library.

16:19:54 Success. The ‘AddinExpress.MSO.2005.dll’ file is found.

16:19:54 Creating an instance of the ‘AddinExpress.Deployment.ADXRegistrator’ class.

16:19:54 Assembly identity is ‘AddinExpress.MSO.2005’.

16:19:54 Success.

16:19:54 Unwrapping the instance of the ‘AddinExpress.Deployment.ADXRegistrator’ class.

16:19:54 Success.

16:19:54 Calling the managed registration procedure.

16:19:57 Success.

16:19:57 The add-in registration process is completed with HRESULT = 0.

95

(imported comment written by Lee Wei)

wsnider,

Is it working for you now?

Thanks for letting us know.

By the way, the latest version is 3.3, where the installer has been upgraded.

Lee Wei

96

(imported comment written by khanand91)

Feature request :slight_smile:

On the next release, would it be possible to add save report attributes feature, so you can save report settings for use again later.

Thanks Lee

97

(imported comment written by Lee Wei)

khanand

Feature request :slight_smile:

On the next release, would it be possible to add save report attributes feature, so you can save report settings for use again later.

khanand was using the older version before I implemented the save report feature.

98

(imported comment written by Miran_p91)

I am having problems with excel 2010.

What happend:

AddinExpress.MSO.ADXRibbonButton error: the add-in has fired an exception.

An error occured in the code of the BigFixExcelConnector add-in. See ‘Details’ section to get more detailed information about the error.

Detailed technical information follows: MY ACCUNT IS HIDEN with *

(Inner Exception)

Date and Time: 7.6.2011 9:15:25

Machine Name: *

IP Address: *

Current User: *

Application Domain: C:\Users*\AppData\Roaming\BigFix Enterprise\Tivoli Endpoint Manager Excel Connector 3.3\

Assembly Codebase: file:///C:/Users/*/AppData/Roaming/BigFix Enterprise/Tivoli Endpoint Manager Excel Connector 3.3/AddinExpress.MSO.2005.DLL

Assembly Full Name: AddinExpress.MSO.2005, Version=6.4.3056.2005, Culture=neutral, PublicKeyToken=4416dd98f0861965

Assembly Version: 6.4.3056.2005

Exception Source: BigFixExcelConnector

Exception Type: System.NullReferenceException

Exception Message: Object reference not set to an instance of an object.

Exception Target Site: showQueryWizard

  • Stack Trace ----

BigFixExcelConnector.AddinModule.showQueryWizard()

AddinExpress.MSO.2005.DLL: N 0001 (0x1) IL

BigFixExcelConnector.AddinModule.adxRibbonButton2_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean)

AddinExpress.MSO.2005.DLL: N 0001 (0x1) IL

AddinExpress.MSO.ADXRibbonButton.DoInternalAction(e As ADXRibbonOnActionEventArgs)

AddinExpress.MSO.2005.DLL: N 0035 (0x23) IL

(Outer Exception)

Date and Time: 7.6.2011 9:15:25

Machine Name: *

IP Address: *

Current User: *

Application Domain: C:\Users*\AppData\Roaming\BigFix Enterprise\Tivoli Endpoint Manager Excel Connector 3.3\

Assembly Codebase: file:///C:/Users/*/AppData/Roaming/BigFix Enterprise/Tivoli Endpoint Manager Excel Connector 3.3/AddinExpress.MSO.2005.DLL

Assembly Full Name: AddinExpress.MSO.2005, Version=6.4.3056.2005, Culture=neutral, PublicKeyToken=4416dd98f0861965

Assembly Version: 6.4.3056.2005

Exception Source:

Exception Type: AddinExpress.MSO.ADXExternalException

Exception Message: An error has occured in the code of the add-in.

Exception Target Site: Object reference not set to an instance of an object.

  • Stack Trace ----
99

(imported comment written by Lee Wei)

Hi Miran_p,

I will help you debug this offline.

Can you please send me a private message?

Lee Wei

100

(imported comment written by lunchboxfl91)

I am all of the sudden having an issue where my excel connector constantly timesout. Only thing that I know that has changed is that the relays were recently updated. Any information to help resolve this would be greatly appreciated. Thanks.