![]() ![]() VBScript to Read and Update an Excel Spreadsheet with Computer Description from Active Directory. VBScript to Read and Update an Excel Spreadsheet with Computer Description from Active Directory - Tech. Net Articles - United States (English)Purpose. This article explains how to use a VBScript application to read a single- column Excel spreadsheet containing a list of computers, check that list against Active Directory (AD), and then update the spreadsheet with the corresponding computer's. AD Description field, if present. In the event the computer does not exist in Active Directory, the Description field on the Excel spreadsheet will be updated with the text "NOT FOUND IN AD."In the event the computer exists in AD, but the description field in AD is empty, the Description field in the Excel spreadsheet will be updated with the word "BLANK" next to the computer on the list. Example Case (Input Excel File)Below is an example of what the initial spreadsheet would look like. WARNING: DO NOT HAVE EXCEL OPEN – Not even for other spreadsheets during the script run! In this scenario, the below primary constraints were tested: 1). Include 2 valid AD server names with valid descriptions in AD, one with name. Include 1 valid AD server name, with no description blank, one non- existent server. Include 1 invalid AD server name. The tested list includes the specific entries listed below: ·. Fileserver. A (this would have a description in AD)·. Fileserver. B (no description in AD)·. Test (this would have a description in AD)·. Br. Zm. N (this would be a non- existent server)Below is a screen- shot of the initial (pre- script) servers. Other Considerations and Further Steps. Preferably, delete any other worksheet tabs, so that the only tab remaining is “Sheet. Alternatively, you can forego the deletion of other tabs, since this script deals with “Sheet. Your initial “servers. Column 1 populated with your server names and, per the existing script design; your script should be located in your “c: \scripts” folder. Alternatively, you may already. Description fields already filled in. This will not be an issue, since the script automatically will bypass any Excel server record that already contains a non- blank description beside it in Column 2. Therefore, it is acceptable. Column 2. Note: You may, if desired, customize the script to have different behavior if Column 2 contains data; i. Column 2 (Description) field in Excel with the then- current data found in. AD (or with “BLANK” and/or “NOT FOUND IN AD" for each such occurrence). After you ensure that you have entered the server names into your spreadsheet Column 1 as desired and required; save the following script to your “c: \scripts” with the file name “checkservers. Note: The assumption here is that you know how to open Notepad and paste and save the below code). Code Snippet. NOTE: Below is only a snippet (portion) of the full code for general understanding. The below code section WILL NOT WORK, unless you click and download/save the code from the embedded links. References" section! The basic premise that the code uses is as follows: 1) Read through all rows of Col 1 on an Excel document (Main code section) 2) Read through each AD computer record (Subroutine section) 3) Update Excel with Description from AD (Main code section)[Start of Code snippet section]'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ' Start of MAIN code (checkservers. VBScript: checkservers. Author: Jeff Mason aka TNJMAN aka bitdoctor'Basic premise: 1) Read through all rows of Col 1 on an Excel document' 2) Read through each AD computer record' 3) Update Excel with Description from AD' 1) Create Excel document (c: \scripts\servers. ONE worksheet. ' containing only "server name" in Column 1‘Other assumptions in the FULL SCRIPT, download now' You must Set excel. Path = "C: \scripts\servers. You must have at least "read" permissions to AD/LDAPDimcounter. To count rows and/or columnsexcel. Path =. "C: \scripts\servers. Full code is listed in the FULL SCRIPT, download now. WScript. Echo. "Reading Data from Path/File: ". Path. Setobj. Excel = Create. Object("Excel. Application")obj. Excel. Display. Alerts = 0. Don't display any messages about conversion and so forth. WScript. Echo. "- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "WScript. Echo. "Reading data from worksheet ". Sheet. Count. WScript. Echo. "- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ". CRLFSetcurrent. Work. Sheet = obj. Excel. Active. Workbook. Worksheets(work. Sheet.Count)' What is the leftmost column in the spreadsheet that has data in itleft = current.Worksheet. Used. Range.Column. Set. Cells = current.Worksheet. Cells'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ' Row Loop - Loop through each row in the worksheet (but only for Column 1)' Only deal with Cols 1 & 2 of Sheet.SERVER=Col. 1 and DESCRIPTION=Col. more. Column 2 is built by "checksvr" subroutine, based on Column 1) Forrow = 0 to (used. Rows. Count- 1) ' only look at rows/cols in the "used" range If. Is. Empty(str. Description) Then' If Col 2 already populated, skip to next row in sheet If. Not. (Is. Empty(server)) Then‘Full code is listed in the FULL SCRIPT, download now'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ' Done with the current worksheet, release the memory. Setcurrent. Work. Sheet = Nothing‘Save and close the workbook - Full code is listed in the FULL SCRIPT, download now. Setcurrent. Work. Sheet = Nothing' Finished with Excel object, release it from memory & get out !!!'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- '- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- '- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ' Subroutine (checksvr) to check for the sever name in Active Directory'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ' Point to the domain/ldap root' Query all Active Directory (normally, leave this commented, query specific OU(s)' str. Root = obj. Root. DSE. Get("Default. Naming. Context") 'Uncomment to search ENTIRE AD TREE' Query a specific Organizational Unitstr. Root =. "OU=Servers,DC=YOUR- DOMAIN,DC=com". Comment this out, if searching ALL OF AD obj. Cn. Provider =. "ADs. DSOObject"obj. Cn. Open. "Active Directory Provider"' Filter the query for only s. AMAccount. Name,description of any computers in ADsvrcmp = UCase(svr) &. Upper- case the Server entry from the spreadsheet for consistent comparesvrflag =. Clear out the "found- server" flag' If description is blank/null, set the value to the word "BLANK" If. Not. (Is. NUll(obj. Res. Fields("description"). Value)). Then‘Full code is listed in the FULL SCRIPT, download now ' We want to check ALL descriptions, including null descriptions ' But only for the server passed into this script as an argument Ifsvrcmp = obj. Res. Fields("s. AMAccount. Name"). Value. Then 'If Excel server name found in AD, set svrflag = "TRUE" & end the subroutine 'Write this to the Excel spreadsheet / exit the subroutine 'Move to / read the next AD resource record 'If flag never set to "TRUE" then fall out through here - server not found in AD str. Description =. "NOT FOUND IN AD"'- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- '- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- [End of Code snippet section]Customize Code to Your Enviornment (Domain, OU, etc.)You must edit the full code and customize the “str. Root” variable in the script to match your own AD environment. Caution: Take care to modify only the 2nd “str. Root” line, since the 1st str. Root line is commented out. In the script, a generic line is included (str. Root = "OU=Servers,DC=YOUR- DOMAIN,DC=com"); this is the only line that should need to be customized, before saving your script. As an example, if your domain is “contoso. Servers” Organizational Unit (OU), then the requisite modified “str. Root” line would look like the following: str. Root = "OU=Servers,DC=contoso,DC=com"After modifying the “str. Cognos Webinars, Demos, White Papers, Presentations, Hints. IBM Cognos Business Intelligence version 1. This webinar highlights the latest and greatest offerings, including the Report Studio preview modes making authoring for Active Report and other mobile consumers much less cumbersome. Our special guests Stefan Constantinides, Solution Advisor North America for IBM, and Don Davis, Client Technical Professional in Business Analytics for IBM, also demonstrate and discuss: Simplified end- user functionality for greater user self- service. Simplified deployment for faster time to value. Numerous improvements in general data access and administration. Additionally, we touch on Cognos Disclosure Management (CDM), which can be a huge time saver when generating reports, along with Watson Analytics. Get up to speed on what the latest version of Cognos BI offers. PRESENTERStefan Constantinides. Solution Advisor North America. IBMStefan Constantinides has Expert level certification within IBM as a Solution Advisor and technical pre- sales solution architect. Over the course of his career, Stefan has had successful sales engagements with companies like Amazon, Disney, Wells Fargo, Sprint, Charles Schwab, AT& T and many other large, high profile accounts. With over 6 years at IBM and almost 3 decades of enterprise level experience, Stefan is now working with large companies to ensure their success with IBM products and helping to guide their business into the future. Don Davis. Client Technical Professional - Business Analytics. IBMDon Davis is a Client Technical Professional in IBM's Business Analytics software group, residing in the Seattle area. Don has been helping clients with Cognos BI since 2. OUTLINEWhat’s New in Cognos BI Version 1. Added Features and Functionalities Overview. Enabling Foundational Self- Service with Cognos Business Intelligence V1. Speed deployment to enable foundational self- service. Targeted profiles will engage even more users and make them more self- sufficient. Customize profiles in Report Studio and Workspace Advanced. Views based on skill and required functionality. Enables more users to be self- sufficient. Use ‘quick set- up’ to easily install and configure all Cognos BI components for testing and prototyping. Increase confidence by managing security at the individual user level in multi- tenant deployments. Leverage high performing Dynamic Cubes by quickly migrating existing Framework Manager models. Proactively plan your upgrade using the Cube Designer hardware sizing guide. Support users with a broad range of disabilities with broadened accessibility. US Section 5. 08 Compliant, from modeling through to authoring. Supports assistive technologies. Produce fully accessible, compliant reports. Streamline the report creation process to save time and make authors more efficient. Build a report once and create a consistent corporate identity with reusable style templates. Define styles for standardized reporting. Create templates for groups to provide a starting point. Simplify report maintenance by updating styles once. Leverage the new visualization capabilities and customize them directly in Report Studio. Extensible visualization properties are exposed in Report Studio and Workspace Advanced. Extensible visualization data slots are now flexible, use only those that you need. See changes to your reports in real- time and speed authoring with Active Report Live Preview. View Active Report Content in Report Studio Directly. Change Layout to Match Device Output or Custom. Make Formatting Changes Directly in Preview Mode. Empower more users with increased personalization and targeted capabilities. Quickly upload and easily model personal data sources for end- to- end analysis with My Data Sets. Upload CSV, XLS, XLSX, and create reports and dashboards. Manage uploaded data files without ITAdministrators can define who has privileges. Quickly focus your analysis by using Dynamic Filters in Cognos Workspace. Create interactive dashboards for easy analysis. Define custom buttons that specify a filter action. Filter related widgets by selecting values. Learn more about these exciting innovations on Analytics. Zone IBM Watson Analytics and Cognos Business Intelligence. Cloud- based agile analytics for business users. Guided flexible exploration with cognitive computing and automated visualization selection. Automated discovery of combinations and permutations of data providing new answers. A standardized gateway to maximize business intelligence value across the Enterprise. Foundational business intelligence for the enterprise. Support for multiple data sources and operating environments with customizable visualizations. A known and trusted analytics foundation. Provides a baseline of information to feed Watson Analytics across one integrated and supported environment. Demonstrations Cognos 1. Cognos Disclosure Management (CDM) Introduction. CDM Deployed at Amazon: HUGE time saver. Now with using CDM, it takes five minutes to generate the monthly executive level report that used to take a minimum of 3 hours to cobble together. CDM is rapidly becoming irreplaceable at Amazon. One of the Amazon team described it as something as essential as turning the lights on. How? Specific and underlying reporting problems. The “Do it Again” Problem. Every month means all reports need to be updated to reflect the current period. Title pages. Date references in text. Narrative comments. Dates that drive the data reported. Data reported. How IBM Cognos Disclosure Management (CDM) addresses the problems. Leverages existing tools in use; Word, Excel, etc. Reports directly integrated with data sources so they can be rolled forward; Master documents can be cascaded. Changes dynamically updated in reports, e. Provides a powerful multi- user, collaborative environment. Workflow and version controls deliver process transparency. Evidence of compliance, report validation, access control and audit trails reduce operational risk. Reports driven from a single, secure database so stakeholders and consumers get a consistent view. Multiple output formats. Integrated XBRL taxonomy, XBRL validation and pre- tagged template reports. Organizations are relying on outdated tools and processes to meet mandated reporting requirements. Abundance of reports and changing requirements. Manual time- consuming processes. High Risk of errors. What we deliver. Employ a repeatable process. Instead of navigating gaps in the production of recurring reports over and over again. Streamline report assembly. Instead of manually piecing together Microsoft Word, Excel and Power. Point files. Make better decisions. Instead of interpreting “just read” data, get to understanding right away. Recurring, multi- author reports create the same problems. Time is spent on low- value work (data/text, copy/paste, editing, validation)Limited time for specialists to make insightful decisions. Slow, manual, recurring process. Facts within reports can be out of date before the report even published. No compliance or controls during the process. Cognos Disclosure Management (CDM) solution features. Replace tools and manual processes with a proven reporting solution used by 3. Filing packages provide all the templates and XBRL connectivity that you need. Familiar MS Office interface for ease of use. Reduce the risk of errors with strong data integration. Built in workflow plus full audit trail provides visibility and transparency. Reports cascading for easier reporting of multiple subsidiaries. Rollover capabilities reduce reporting for future cycles and provide an easy transition between periods and mandate versions. Questions: What’s New in Cognos BI Version 1. The following questions were originally collected during the live webinar session and answered by the Senturus team. You can download the complete Questions Log from the right hand side bar at the top of this page. Questions. Q: Just looking at the 1. Framework Manager and Report Studio capabilities. However, I only see TM1 and Business Insight as actual 1. IBM site under Software Catalog. Is there actually a Framework Manager 1. Report Studio 1. 0. Can you go over the actual products that were updated, and if not, what their packages may be (i. Framework Manager 1. TM1 1. 0. 2. 2) or other actual updated mix & matches to install to get the updates claimed in the videos? Q: Will the IBM Cognos quick setup be available for SQL server in the future? Q: Will the tenant capabilities be called out in the licenses? Q: Do I have to get 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |