Dict-Physical

View VMD_IssueTrackingAsset

Card of view VMD_IssueTrackingAsset

Name	VMD_IssueTrackingAsset
Comment	Asset associated with issue-tracking system. See documentation on MD_IssueTrackingAsset in the StarsIssueTracking package.
Dimensional Type	Dimension

Options of the view VMD_IssueTrackingAsset

with schemabinding

SQL query of the view VMD_IssueTrackingAsset

select
MD_ITAsset_SurrogateKey,
MD_ITAsset_ID,
MD_ITAsset_IssueTrackingSystem,
MD_ITAsset_Number,
MD_ITAsset_Model,
MD_ITAsset_VendorSerialNumber,
MD_ITAsset_Type,
MD_ITAsset_SubType,
MD_ITAsset_Manufacturer,
MD_ITAsset_PurchaseYYYYMM,
MD_ITAsset_PurchaseFiscalYear,
MD_ITAsset_YearsUntilReplacement AS MD_ITAsset_ReplacementCycleYears,
MD_ITAsset_DisposalYYYYMM,
MD_ITAsset_DisposalFiscalYear,
-- We could precalculate the replacement YYYY-MM, but this is also pretty easy
CASE
WHEN MD_ITAsset_YearsUntilReplacement IS NULL THEN '9999-99'
WHEN MD_ITAsset_YearsUntilReplacement >= 99 THEN '9999-99'
WHEN MD_ITAsset_PurchaseYYYYMM = '9999-99' THEN '9999-99'
ELSE
CAST(YEAR(DATEADD(yy, MD_ITAsset_YearsUntilReplacement, MD_ITAsset_PurchaseYYYYMM + '-28')) AS VARCHAR)
+ '-' + CAST(MONTH(DATEADD(yy, MD_ITAsset_YearsUntilReplacement, MD_ITAsset_PurchaseYYYYMM + '-28')) AS VARCHAR)
END AS MD_ITAsset_ReplacementYYYYMM,
MD_ITAsset_ReplacementFiscalYear,
MD_ITAsset_Room,
MD_ITAsset_Status,
MD_ITAsset_Disposition,
MD_ITAsset_CarletonCost,
MD_ITAsset_CostCenter,
CASE
WHEN MD_ITAsset_CostCenter IS NULL THEN 'Unknown'
WHEN RTRIM(MD_ITAsset_CostCenter) = '' THEN 'Unknown'
WHEN MD_ITAsset_CostCenter = '?' THEN 'Unknown'
WHEN MD_ITAsset_CostCenter LIKE '%4480-%' THEN 'ITS for Admin'
WHEN MD_ITAsset_CostCenter LIKE '%4481-%' THEN 'Multi-Function Printing (MFP)'
WHEN MD_ITAsset_CostCenter LIKE '%6770-%' THEN 'ITS General'
WHEN MD_ITAsset_CostCenter LIKE '%6771-%' THEN 'ITS (ACNS)'
WHEN MD_ITAsset_CostCenter LIKE '%6773-%' THEN 'Classroom Technology'
WHEN MD_ITAsset_CostCenter LIKE '%6774-%' THEN 'ITS-Networking'
WHEN ISNUMERIC(MD_ITAsset_CostCenter) = 0
AND MD_ITAsset_CostCenter LIKE '%-%' THEN 'Other Account'
WHEN ISNUMERIC(MD_ITAsset_CostCenter) = 0 THEN 'Other (Text)'
WHEN MD_ITAsset_CostCenter = '4480' THEN 'ITS for Admin'
WHEN MD_ITAsset_CostCenter = '4481' THEN 'Multi-Function Printing (MFP)'
WHEN MD_ITAsset_CostCenter = '6770' THEN 'ITS General'
WHEN MD_ITAsset_CostCenter = '6771' THEN 'ITS (ACNS)'
WHEN MD_ITAsset_CostCenter = '6773' THEN 'Classroom Technology'
WHEN MD_ITAsset_CostCenter = '6774' THEN 'ITS-Networking'
WHEN LEN(MD_ITAsset_CostCenter) = 4 THEN 'Other Department'
ELSE 'Other Account'
END AS MD_ITAsset_CostCenterType,
MD_ITAsset_Purpose,
CASE
WHEN MD_ITAsset_Purpose = 'DEPT PURCHASE' THEN 'Yes'
WHEN MD_ITAsset_Purpose = 'SERC' THEN 'Yes'
WHEN MD_ITAsset_Purpose LIKE 'Grant%' THEN 'Yes'
ELSE 'No'
END AS MD_ITAsset_IsGrantFunded,
CASE
WHEN MD_ITAsset_Purpose = 'TEMP DEPLOY' THEN 'Yes'
WHEN MD_ITAsset_Purpose = 'TEMP FACULTY' THEN 'Yes'
ELSE 'No'
END AS MD_ITAsset_IsTempDeploy,
MD_ITAsset_Exceptions,
MD_ITAsset_IsActive,
MD_ITAsset_Warranty
from
MD_ISSUETRACKINGASSET

List of incoming view references of the view VMD_IssueTrackingAsset

ViewReference_105

VMF_IssueTrackingTicket

ViewReference_120

VMF_IssueTrackingAssetValue

ViewReference_224

VMF_KeyedComputerEvent

ViewReference_239

VMF_KeyedComputerInUse

ViewReference_247

VMF_KeyedComputerInUseWithRoomUtilization

List of referenced objects of the view VMD_IssueTrackingAsset

MD_IssueTrackingAsset

List of shortcuts of the view VMD_IssueTrackingAsset

VMD_IssueTrackingAsset

VMD_ISSUETRACKINGASSET

View

ViewsIssueTracking

List of diagrams containing the view VMD_IssueTrackingAsset

ViewsIssueTrackingDiagram

List of permissions of the view VMD_IssueTrackingAsset

SELECT,VIEW DEFINITION

IssueTrackingReader

List of view columns of the view VMD_IssueTrackingAsset

MD_ITAsset_SurrogateKey

numeric

MD_ITAsset_ID

varchar(32)

System-specific identifier for a particular asset identified in a trouble ticket/helpdesk submission. The system in which the identifier occurs is supplied in the MD_ITAsset_IssueTrackingSystem field.

This variable should not normally be exposed to users, who may confuse it with the CCID (MD_ITAsset_Number). The value of this variable is something that the ticket/asset tracking system does not expose, although it keeps it internally as a unique identifier.

MD_ITAsset_IssueTrackingSystem

nvarchar(64)

As of 2011, this variable is always set to 'Web Help Desk'.

If we bring in multiple asset or ticket-tracking systems, we will acquire alternate values here.

MD_ITAsset_Number

varchar(32)

This is the CCID, i.e., the Carleton College asset ID.

MD_ITAsset_Model

varchar(64)

Vendor-designated model name for a given asset. Hierarchizes below manufacturer. E.g., the manufacturer for a given asset might be 'Dell' and the model might be 'Optiplex 445-blah-blah'.

MD_ITAsset_VendorSerialNumber

varchar(64)

Serial number provided by vendor for a piece of equipment. Defaults to 'Unknown'.

Not the same as our Carleton asset number (CCID).

MD_ITAsset_Type

varchar(32)

General type of asset: Server, System, Monitor, etc.

MD_ITAsset_SubType

varchar(32)

Custom attribute created to allow us to flesh out the MD_ITAsset_Type attribute in Web Help Desk (WHD). For example, we have an asset type of 'Server' in WHD. But there are many kinds of servers, and not all are replaced on the same cycle. We therefore divide servers up into sub-types. The sub-types are recorded in this attribute.

MD_ITAsset_Manufacturer

nvarchar(64)

MD_ITAsset_PurchaseYYYYMM

varchar(32)

Note that this field (unlike MD_ITAsset_PurchaseDate) refers to the built-in purchase date field in the asset-tracking system. MD_ITAsset_PurchaseDate refers to the custom attribute defined by ITS. Normally this field should be hidden.

MD_ITAsset_PurchaseFiscalYear

int

Fiscal year in which an asset was originally purchased. Note that the fiscal year runs from July 1 to June 30, and bears the name of the second year. E.g., the fiscal year running from 1 July 2011 until 30 Jun 2012 is 2012.

MD_ITAsset_YearsUntilReplacement

int

Number of years until an asset is due for replacement (or retirement) counting from the original purchase date. A simple integer.

This attribute is used, typically, to categorize assets by the replacement cycle we've put them on, theoretically. It is not normally updated after initial entry. So, e.g., if we purchase an asset in January of 2011, and we assign it an MD_ITAsset_YearsUntilReplacement of 3, we are saying that we intend to replace (or retire) an asset by January of 2014. This anticipated replacement/retirement date does not change routinely, although we may do batch updates periodically, if we change our overall replacement cycle policy as applied to a particular asset group or class.

MD_ITAsset_DisposalYYYYMM

varchar(32)

YYYY-MM in which an asset was taken out of service and recycled or otherwise disposed of.

MD_ITAsset_DisposalFiscalYear

int

Fiscal year in which an asset was disposed of (recycled, etc.).

MD_ITASSET_REPLACEMENTYYYYMM

MD_ITAsset_ReplacementFiscalYear

int

Fiscal year in which an asset was originally planned to be decomissioned/replaced, when first purchased. Note that the fiscal year runs from July 1 to June 30, and bears the name of the second year. E.g., the fiscal year running from 1 July 2011 until 30 Jun 2012 is 2012.

Normally this value is not changed once an asset is entered into the system. But occasionally, when we change replacement cycles, it will change.

MD_ITAsset_Room

varchar(32)

Somewhat well maintained as of summer 2011. Cleaned up in August of 2011.

Most problematic are assets that are initially deployed in one location, but are moved.

Also problematic are equipment deployed to computer labs and to network closets. Rooms for such equipment have not traditionally been well maintained.

The room name here contains both a building identifier and a room number, e.g.: CMC*202.

MD_ITAsset_Status

varchar(64)

This attribute is typically used in filters, and will in fact typically be set to a single value: Deployed. Other possible values include: GONE, Repair, In Stock.

MD_ITAsset_Disposition

varchar(32)

Subcategory of MD_ITAsset_Status. Used, generally, to tell us what happened to assets whose status is GONE.

MD_ITAsset_CarletonCost

varchar(32)

Discretized ('banded') version of original Carleton cost (purchase price, often with things factored in like initial maintenance/warranty).

MD_ITAsset_CostCenter

varchar(64)

ITS custom designation for where the money is coming from. Often this is a budget number (4 digits out of the 14 total digits that make up an account number).

At least as of 2012, a lot of logic is needed in order to make this field useful analytically, because of the inconsistent way in which data has been entered. Machinery should exist for making it more consistent - in the corresponding user-exposed view, VMD_TicketTrackingAsset.

MD_ITASSET_COSTCENTERTYPE

MD_ITAsset_Purpose

varchar(64)

This is where ITS labels things as being grant funded, startup-fund funded, etc.

MD_ITASSET_ISGRANTFUNDED

MD_ITASSET_ISTEMPDEPLOY

MD_ITAsset_Exceptions

varchar(64)

Applies mainly (exclusively?) to systems. Holds information like whether the system is a refreshing one (i.e., it resets to a base state after each use) or not. Use to eliminate/filter classes of machines from reports when they're not wanted or needed, like lab machines.

This is based on a custom field in Web Help Desk.

MD_ITAsset_IsActive

varchar(7)

'Yes' if an asset's status is not GONE and the asset is not marked, internally to the asset/ticket tracking system, as deleted. 'No' otherwise.

MD_ITAsset_Warranty

varchar(32)

Typically not maintained as of 2011.

Home