EOL Asset DB – Checklists

The IT Assets Database was replaced by the IT Admins CMDB and is EOL / End of Life, no further development will be done on this project.

Most IT departments use checklists for common tasks like deploying new workstations, on-boarding employees or new servers. It does not matter weather you on-board / deploy or you terminate / decommission a user or system. Checklist can be created for various tasks for assets like workstations, servers and equipment as well as employees.

Implemented and used right, those checklists can become incredible useful and important. They can help you through whole system migrations, besides the usual on-boarding and termination processes or deployment and retirement. Checklists change with time – but a once created checklist does not, cause checklists are copied from the template as it is at a certain point in time – so you can easily look at a older checklist and have a completely different checklist compared to what the current one looks like. I personally can’t encourage you enough to use them and implement proper script to automate standard processes where you can. It is not just because of the scripts, it is about standardization and streamlining everything. This avoids even to tell the team constantly about process changes, you simply change the checklist. As long they really go through them step by step, you will have an incredible tool and process on how to change certain steps. And if you ever worry – the checklist search actually indicates on how long someone was in a checklist as well as the fact that each click on a checkbox or even selecting a value from a drop down field or entering text in a field will result in a history information. Hover over a field in a real checklist and you will see a tip-text that will show you who changed it last with date and time.

You can create the similar lists – e.g. a list new accounting user / new manufacturing user – or – PC build (for preparing available systems with the default image) / PC deployment (for deploying and customizing the system when deployed to a user). In the end it is up to you how you use and engage those checklists, but they can be very powerful.

A checklist of course might not just be a simple list with instructions, it might collect data like version numbers, dates, etc. as well as having links to your intranet documentation about specific steps for a task to even executing scripts. All of this can be accomplished with the checklists. You can add a single title per item, additional instructions (item memo), define if the item is mandatory or not, define if it has a textbox to expect information from the employee that is executing the list, add additional links to e.g. intranet places like Exchange Web-Admin portal that can be clicked while working with the list, and even add scripts as cmd-scripts or PowerShell scripts that can be executed with a single button click and even retrieve information from the parent table/asset from the database as well as for any checklist text field or even drop down field select value – any column when you use drop-down fields. In order to accomplish this and making sure that the script executes right, a mini-validation will happen before the script even fires. If this mini-validation fails, the user will be informed of what is missing as well.

Drop-Down fields have been added in version 3.0 of the database. Those can relate to either existing views and tables, direct transact SQL queries or value lists. Eventually they act as any other Microsoft Access DropDown field. If you use SQL queries, always make sure you show all values – do not restrict the values to only “Active” entries, this would cause issues if you load an older checklist – if the values would been since retired/in-active – you would not see what was select. It is to recommend to simply put ORDER statements to accomplish this, if at all – or show enough columns.

Useful examples for lists are below – you can use them and even process the select information in scripts in order to automate settings.

• Departments
  • type: Table/Query
  • columns: 3
  • width: 0,0 (this avoids that the first and second column is shown)
  • qryDepartments
• Manager / Supervisor
  • type: Table/Query
  • columns: 4
  • width: 0 (only first column is invisible)
  • SELECT E.UserName, E.EmployeeName, E.department, E.RemovedDate
    FROM tblEmployees AS E INNER JOIN tblEmployeesLDAPSyncLastSeen AS L ON E.ID = L.EmployeeID
    WHERE (((E.UserName) Not Like ‘ilsd%’ And (E.UserName) Not Like ‘%_admin’) AND ((E.EmployeeName) Not Like ‘, %’ And (E.EmployeeName) Not Like ‘Admin, %’) AND ((E.LDAPSync)=True))
    ORDER BY Format(L.LDAPSyncLastSeen,”yyyy/mm/dd”) DESC , E.EmployeeName, E.department;
• Yes/No fields
  • type: Value List
  • columns: 1
  • width: <leave empty>
  • “Yes”,”No”
• Workstation
  • type: Table/Query
  • columns: 2
  • width: 0 (only the first column is invisible)
  • SELECT ID, UserName FROM tblEmployees ORDER BY RemovedDate, EmployeeName

Got curious on those SELECT statements? You need a full version of Microsoft Access – click on the SHOW TABLES button in the Main Menu and create your own query in Microsoft Access – show the SQL code for the query once it looks like you want it and gives you the data you want to show and insert it to the checklist. This is rather easy and does not need a lot of SQL code knowledge nor database development knowledge. If you get stuck, use the contact form and reach out – I am always happy to help.

To accomplish this, you first create a template and define to which asset type it belongs. Then you add checklist items – in a simple tabular form. Please pay attention to the green instructions text above this table that gives you hints on how to most effectively enter the items.

Use the preview checklist button to see how your checklist will look like.

Limitation – please be aware that there is a limitation due to the way the checklist system works and Microsoft Access that only allows a certain maximum form height and width. A checklist can go over two columns, left and right – but once you filled it up – you might need to split it in two separate templates that both would need to be executed. You can limit the wasted space by transferring instructions in the Memo field that might be rather long to a local intranet and just use the links to point to them.

When you look at a checklist, you see that there are two columns of checkboxes at the left of each checklist item. The first column defines if it is applicable, this is checked by default, the second checkbox is to be checked once the item / task is finished – by the executing employee. By design – either both checkboxes need to be checked or unchecked – they can’t be different. If a entry becomes mandatory, the applicable checkbox can not be unchecked. Similar rules apply to the text-fields that can be added.

A checklist remains in progress until it is finished with the appropriate buttons in the top. It only can be finished if all necessary fields are checked / entered as defined in the template. Altering the template after a list was started / created will not alter the target list – in other words – if you e.g. create in workstations a checklist based on any template – this template will be copied completely as is – if you a few minutes or months later alter the template – the created list still will not change or be affected. There is a version control on those lists.

Only application users with the right to create templates can unlock a finished checklist, making it edible again. This avoids that your regular database users lock and unlock checklists as they like and give you some kind of certainty that the data wasn’t altered afterwards.

To further help with this task and determining when something was checked, unchecked, text entered or e.g. a button for a script clicked, the information will be saved as well in the database. If you hover of an item that was clicked somehow before – it will show you tool-tip-text with information who and when the field / button was clicked or data entered.

The biggest challenge might be the scripts – I do have some scripts active for e.g. removing Active Directory accounts or password database entries, as well as sending welcome emails. But all of this is so specific to each and every environment, that you really need to develop them yourself. Once they are working, they will become very powerful.

Checklists have additional SysConfig parameter for some control over the necessary rights, e.g. execute as a certain user or relative user (default user: jdoe / executing user: jdoe_admin – depending on logged on user, etc…). Please refer to the section in the manual for details.

Data field and reference overview

This list shows and partly explains the configuration values for the templates, since the entries are not directly asset related rather then configuring a checklist template. Adjusting checklists will not alter existing checklists – cause those are always copied from the current template.

Hovering with the mouse cursor over most of those fields will show you a tooltip text that helps understanding their purpose and usage.

• Active / Disabled
• Target Table – this determines where the checklist will be available
• checklist name
• new deployment status – leave empty or define a new deployment status that will be set to the asset when the checklist is finished
• Notes
• filled out lists overview
• checklist items / template configuration – sub-table
  • active / disabled
  • sort-order – where in the checklist the entry will appear
    • suggestion – jump big – like in 1000 steps to be able to sort easily later on or add sub-entries in between
  • sub-level
    • chose one of four levels to move the entry more to the right and show it as a sub-entry
  • item name
  • has checkbox – if not – the entry does not need to be completed and might be just a title for sub-entries
  • mandatory – setting this box causes the APPLICABLE checkbox to be disabled and making it mandatory that this item is processed – it also write the title RED in the checklist
  • Item Memo – long text for the item – explanation on what needs to be done etc..
    • TIP: press SHIFT + F2 to open an edit window in Access and hit ENTER to break the lines and make it better readable
  • Has TextBox
    • input field size – how many rows the textbox field should be
    • textbox target field – this must be a field on the target-table – the value in the textbox would be pushed to this field when click on finished – leave empty if no write back to the source table is needed
      • useful for description and status notes fields
    • input field reference
      • this reference can be used as a script parameter
        • set it to: ABC 
        • use it as a script parameter: “scriptpath\script” “-parameter1:[!checklist_ABC!]”
  • link
    • shows a clickable link on the checklist – good for external Wiki’s, direct access to web-portals etc. – simply to make live easier
  • scripts
    • script-type (PowerShell/ps1 or CMD/Batch-File)
    • script
      • TIP: press SHIFT + F2 to edit this field
      • put text quotes around the script path and name as well as each single parameter
        • “\\server\path\folder\script.ps1” “[!checklist_ABC!]” “[!checklist_ABC.2!]” “[!sourcecolumn/alternativesourcecolumn!]”
      • if you want to access columns from a LIST (defined in the checklist, see further below) you need to keep in mind to set a reference tag and that columns are 0 based
        • assume you have 3 columns (ID, DepartmentName, DepartmentNumber) and the reference is CHKDEPT you use the following parameters to inject the selected values in the script parameters
          • [!checklist_CHKDEPT.0!] – this gives you the ID column value
          • [!checklist_CHKDEPT.1!] – this gives you the DepartmentName column value
          • [!checklist_CHKDEPT.2!] – this gives you the DepartmentNr column value
    • alternative script source
      • per default script parameter can be any field/column of the target-table see example above – you can set another source e.g. a query/view instead – the parameters still work the same but are based on this alternate table
      • note: there will be a SQL query that says: SELECT * FROM ALTERNATIVESCRIPTSOURCE WHERE ID = X 
        • this means you need to have the ID field as reference in this alternative script source / query/view available
      • run elevated – this will use the SysConfig defined username abbreviation and ask for the user password while executing a RUN-AS command
  • Lists
    • List Type – value list or Table/Query
      • refer to Microsoft Access Drop Drown fields in general – this field configuration works the same way
      • value lists: you enter the values comma separated in to the list source, e.g.:
        • single column example
          • “Yes”,”No”
          • column count = 1
        • multi column example
          • 1,”okay”,2,”failed”,3,”cancelled”
          • column count = 2
          • column width = 0 (assuming you only want to see column 2)
      • Table/Query (SQL)
        • List source would be a SQL query or the name of a query or table
        • example select workstation
          • select id, hostname, assettag, type, departmentname, departmentnr, deploymentstatus, employeename from qryWorkstations order by deploymentstatus,departmentname,assettag
          • count = 8
          • widths = 0
        • example departments
          • qryDepartments
          • count = 3
          • widths = 0,0
          • List target field = DepartmentID
        • example LDAP manager for Employees
          • SELECT E.UserName, E.EmployeeName, E.department, E.RemovedDate
            FROM tblEmployees AS E INNER JOIN tblEmployeesLDAPSyncLastSeen AS L ON E.ID = L.EmployeeID
            WHERE (((E.UserName) Not Like ‘ilsd%’ And (E.UserName) Not Like ‘%_admin’) AND ((E.EmployeeName) Not Like ‘, %’ And (E.EmployeeName) Not Like ‘Admin, %’) AND ((E.LDAPSync)=True))
            ORDER BY Format(L.LDAPSyncLastSeen,”yyyy/mm/dd”) DESC , E.EmployeeName, E.department;
          • count = 4
          • width = 0
    • list column count
      • amount of columns in this list in total to be visible – starting from the left – you can hide columns from the left / additional columns are not available as data source for script parameters
    • list column widths
      • leave empty to show all columns, set 0 to hide the first column, input comma separated values for multiple columns – you can define the size of each column
    • ListTargetFiled – this must be a field on the target-table – the value in the textbox would be pushed to this field when click on finished – leave empty if no write back to the source table is needed
    • ListShowHeaders – show column names on top of the list when dropped down
    • LimitToList – only list entries and user can enter independent value
    • List Reference – this reference can be used as a script parameter
      • set it to: ABC 
      • use it as a script parameter: “scriptpath\script” “-parameter1:[!checklist_ABC!].0”

Examples for scripts

Since there seems to be a lot of questions about script, I thought I post some example scripts here to keep you going. Please see below for more information.

DHCP reservations

Create this SQL server view in the database

And create the following script in your defined scripts folder

In this script, adjust this line to your preferred DHCP server – this could be a parameter as well, but in this example it is hard coded in the script.

$DHCPServer = “yourDHCPserver.domain.local”

Now add a checklist entry – the parameters should look similar to this – keep in mind – this is specifically for SERVERS and nothing else – otherwise adjust the SQL view to the table PRINTERS or WORKSTATIONS as needed.

• Script
  • “C:\IT Assets\ChecklistScripts\CreateDHCPReservation.ps1″ [!hostname!] [!domain!] [!MACAddress!] [!IPAddress!] [!IPNotation!]
• Script-Type
  • ps1
• AlternativeScriptSource
  • qryCheckListScriptServerMACIP

Remove DHCP reservation (retirement checklist)

SQL – we will use the same view we used for the DHCP reservation script – see above – if you have it already, you are all set. We now will create a second script

In this script, adjust this line to your preferred DHCP server – this could be a parameter as well, but in this example it is hard coded in the script.

$DHCPServer = “yourDHCPserver.domain.local”

Now add a checklist entry – the parameters should look similar to this – keep in mind – this is specifically for SERVERS and nothing else – otherwise adjust the SQL view to the table PRINTERS or WORKSTATIONS as needed.

• Script
  • “C:\IT Assets\ChecklistScripts\RemoveDHCPReservation.ps1” [!IPAddress!] [!IPNotation!]
• Script-Type
  • ps1
• AlternativeScriptSource
  • qryCheckListScriptServerMACIP

Remove a server/workstation from Active Directory

• Script
  • “C:\IT Assets\ChecklistScripts\RemoveWorkstationFromAD.ps1” [!hostname!]
• Script-Type
  • ps1

This is a way simpler script as the DHCP reservation – but it works as well. Similar stuff can be done with adding systems to groups etc…

Assign Office 365 licenses

• Script
  • “C:\IT Assets\ChecklistScripts\O365.ps1” [!username!]
• Script-Type
  • ps1

Update or set Generic user information

• Script
  • “C:\IT Assets\ChecklistScripts\Employee_UpdateAll.ps1” [!username!] [!checklist_empNumber!] [!checklist_dept.1!] “[!checklist_dept.2!]” “[!checklist_title!]” [!checklist_phone!] [!checklist_mgr.0!]
  • Be aware – the above is a rather complex example that relates to various fields in the checklist, especially drop down lists!
• Script-Type
  • ps1