(imported comment written by SystemAdmin)
Latest build of PropertyIDMapper (2.2.2.11)
http://software.bigfix.com/download/bes/util/BESPropertyIDMapper.exe
Bugfixes:
(imported comment written by daveb91)
Tyler,
The PropertyIDMapper utility works great for me, and is exactly what I have been looking for. Thanks!
I’ve spent a number of days trying to do this myself, after unsuccessfully searching the forums for this information / utility. Coming straight to 7.0 (never used 6.0) I didn’t think to check this thread, and hadn’t come across it in my searches. It was sheer luck that I finally happened to read it out of curiosity.
I don’t know how many others are out there that want to be able to fully utilize the Database API, but this is just the kind of info they need to find. I would suggest either a sticky under this forum named "How to query
all
properties via SQL", or a separate/subordinate forum for “Using the Database API”. I really appreciate this and hope others find it too.
On a vaguely related note, I’ve been able to use the MS-SQL PIVOT function to create a tabular view of selected (at view creation time) properties by ComputerID, and have found the functionality and execution speed to be quite satisfactory. I only have about 2k systems under management (so far), so the latter could change. If anyone wants an example I’m happy to post.
Thanks again,
David
(imported comment written by riel91)
(edit) Granted the account EXECUTE permission on fn_ExtractField and fn_IsActionExpired, and it works now.
Does anyone have a list of permissions needed for the Property Mapper exe to work? We’re trying to keep permissions to the database as limited as possible.
Specifically, we ran the utility once as a database admin to create the PROPERTYIDMAP table. But when trying to run it (from another account) to update the table, I’m getting these errors:
The EXECUTE permission was denied on the object ‘fn_ExtractField’ … at BESPropertyIDMapper.pl line 167.
The EXECUTE permission was denied on the object ‘fn_ExtractField’ … at BESPropertyIDMapper.pl line 177.
The EXECUTE permission was denied on the object ‘fn_ExtractField’ … at BESPropertyIDMapper.pl line 197.
(imported comment written by SystemAdmin)
Hi Riel,
This might be a little tricky as the tool has different behavior each run.
Initial Run - It needs to create the PROPERTYIDMAP table in the BFEnterprise database. I think you need DBO rights to do this. So, the first time the tool is run, you need to give it DBO permissions.
Update Runs - After you run it the first time, it notices that the table already exists and just needs to update it. To do this, you would need these permissions:
Select on TABLES: DBINFO, SITENAMEMAP, VERSIONS, LOCAL_OBJECT_DEFS, EXTERNAL_OBJECT_DEFS, PROPERTYIDMAP
Insert on TABLES: PROPERTYIDMAP
Delete on TABLES: PROPERTYIDMAP
Execute on Scalar Function: fn_ExtractField
(imported comment written by kevin_tang91)
Is there a upgrade for PropertyIDMapper tool? Seems like version 2.2.2.12 's for 1.96/1.97/1.98, but my db version is 1.99 (7.2.1.357).
(imported comment written by kevin_tang91)
Found it.
(imported comment written by SystemAdmin)
Yes, just download the latest build (3.0.3.65)
http://software.bigfix.com/download/bes/util/BESPropertyIDMapper.exe
(imported comment written by SystemAdmin)
New build is available (3.0.3.135)
http://software.bigfix.com/download/bes/util/BESPropertyIDMapper.exe
(imported comment written by riel91)
Getting errors with the property mapper after having installed the update for 7.2.4.60.
Sample errors:
Looking up Custom Properties…DBD::ODBC::st fetchrow_array failed:
Microsoft
SQL Native Client
String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) err=-1) at /<D:\jobs\BigFix Export\BESPropertyIDMapper.exe>BESTools/Databases.pm line 273, line 165.
DBD::ODBC::st fetchrow_array failed:
Microsoft
SQL Native Client
String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) err=-1) at /<D:\jobs\BigFix Export\BESPropertyIDMapper.exe>BESTools/Databases.pm line 273, line 165.
(imported comment written by SystemAdmin)
Hey Riel,
This means you have a row in the database we are trying to get that is over 16MB large which is the default max row length we use for this tool. I’m not sure why you would have a row that large.
What Fixlet sites are you subscribed to?
Have you created an custom analyses or properties that are suspiciously large?
(imported comment written by riel91)
Not that I know of but I will check… which table(s) should I be looking at?
(imported comment written by SystemAdmin)
I mostly just wanted you to poke around in the BES Console and see if any analyses looked really big. You can get your site subscription list from tools->manage sites.
The database tables we would look at are LOCAL_OBJECT_DEFS and EXTERNAL_OBJECT_DEFS but we don’t necessarily look at every row for this tool (though I’m not sure why any row would be over 16MB).
If it is possible for you to upload your database to us, I can do the analysis on our side. Just send me an email and I’ll give you instructions on how to do it.
(imported comment written by SystemAdmin)
New build is available (3.0.3.166)
http://software.bigfix.com/download/bes/util/BESPropertyIDMapper.exe
(imported comment written by paclark91)
This is great information. I do have a question; Does anyone know where the information is stored (in 7.0) for the Manual Computer Groups. It used to be in ‘fieldcontents’ column in the textfields table. I am trying to write a query to pull all the computers in a certain manual group but I can not find this information in the new 7.0 schema. P.S. I am writting my query in SQL from the database directly.
(imported comment written by SystemAdmin)
Does the BES_COMPUTERGROUPS view do what you are looking for?
(imported comment written by SystemAdmin)
A new build is available (3.0.3.173) with updates for TEM 8.2. Same download link:
http://software.bigfix.com/download/bes/util/BESPropertyIDMapper.exe