Dict-Physical

View VMF_KeyedComputerInUse

Card of view VMF_KeyedComputerInUse

Name	VMF_KeyedComputerInUse
Comment	See documentation on MF_KeyedComputerInUse in the StarsLabUsage package. Although based on MF_KeyedComputerInUse, this view differs in important respects. This view's primary use is for telling us how well utilized lab and shared machines are. To that end, it includes only information on lab and other shared machines (insofar as this can be determined from the IT asset-tracking system, WebHelpDesk, and from K2). This is unlike MF_KeyedComputerInUse, which incorporates logon, logoff, up, and down information on all machines in K2 (and possibly eventually other systems). Just to make the point really clear: THIS VIEW DOES NOT PROVIDE GOOD MODELING OF NON-LAB/SHARED MACHINE USAGE. Among other things, it throws out long-running sessions, because if we kept those, we'd end up with very skewed stats for machines where people log in, then stay logged in, often for months at a time. This produces very long-running sessions and gives the impression of a heavily used machine (because someone is always logged in). This view is not intended for such machines, but rather for lab and other shared machines intended for short-term use (less than a day at a time). In departments with lax security policies (one person logs in, then leaves the computer unlocked and the next person simply coopts the previous user's session), these stats will be skewed. They will also be skewed in cases where a "shared" machine is really not shared, and people camp out, claiming the shared machine as their own for days or weeks on end. This view is also (unlike MF_KeyedComputerInUse) based on a highly inefficient query that incorporates 'coverage' information. That is, it has a row for every computer and every date and hour from the first time the computer was seen by K2 (base audit) to whenever it was last logged into in K2 (or when it was disposed of in WHD, whichever is later). That's a lot of rows, and because they're generated through a join (not stored in the database), the efficiency is low. This table should only be used to load a multidimensional cube.
Dimensional Type	Fact

SQL query of the view VMF_KeyedComputerInUse

/*
* Usage stats on all keyed computers correctly linked to assets in WHD
*
* Incorporates both usage information and coverage. The coverage table is the first big select below, and it gets
* us information on every possible date/time when a given computer could have been used. The second big select gets
* us usage information.
*/
select
compDateTime.MD_KeyedComp_SurrogateKey,
compDateTime.MD_Date_DateInteger,
compDateTime.MD_Time_TimeInteger,
(compDateTime.MD_Date_DateInteger / 10000) AS VMF_KCInUse_Year,
compDateTime.MD_ITAsset_SurrogateKey,
compDateTime.MD_ITUser_SurrogateKey,
compDateTime.MD_Room_SurrogateKey,
compDateTime.MD_Building_SurrogateKey,
compDateTime.MD_CarlDept_SurrogateKey,
ISNULL(VMF_KCInUse_Used, 0) AS VMF_KCInUse_Used,
6 AS VMF_KCInUse_Available
from
-- This first select gives us coverage - every date/time for every keyed computer (+ asset key)
(SELECT
mdk.MD_KeyedComp_SurrogateKey,
mdk.MD_ITAsset_SurrogateKey,
mdk.MD_ITUser_SurrogateKey,
mdk.MD_Room_SurrogateKey,
mdk.MD_Building_SurrogateKey,
mdk.MD_CarlDept_SurrogateKey,
mdd.MD_Date_DateInteger,
vmt.MD_Time_TimeInteger
FROM
-- Assemble every unique computer along with the asset key and when it was last seen
(SELECT DISTINCT
kdc.MD_KeyedComp_SurrogateKey,
kdc.MD_ITAsset_SurrogateKey,
kdc.MD_ITUser_SurrogateKey,
kdc.MD_Room_SurrogateKey,
kdc.MD_Building_SurrogateKey,
kdc.MD_CarlDept_SurrogateKey,
kyc.MD_KeyedComp_LastSeen,
kyc.MD_KeyedComp_FirstSeen
FROM
         MF_KeyedComputerInUse kdc
INNER JOIN MD_KeyedComputer kyc
ON kdc.MD_KeyedComp_SurrogateKey = kyc.MD_KeyedComp_SurrogateKey
      ) mdk,
MD_Date mdd,
VMD_TimeToHour vmt
WHERE
mdd.MD_Date_DateInteger >= (SELECT MIN(MD_Date_DateInteger) FROM MF_KeyedComputerInUse)
AND mdd.MD_Date_DateInteger <= (SELECT MAX(MD_Date_DateInteger) FROM MF_KeyedComputerInUse)
AND mdd.MD_Date_SQLDate <= mdk.MD_KeyedComp_LastSeen
AND mdd.MD_Date_SQLDate >= mdk.MD_KeyedComp_FirstSeen
) compDateTime
LEFT OUTER JOIN
-- This next select gives us the actual usage percentage (by day and hour) for each computer (cf. coverage select above)
(SELECT
MD_KeyedComp_SurrogateKey,
MD_Date_DateInteger,
-- Take only the hour part of MD_Time_TimeInteger; set minutes, seconds to zero
((MD_Time_TimeInteger / 65536) * 65536) AS MD_Time_TimeInteger,
MAX(MD_ITAsset_SurrogateKey) AS MD_ITAsset_SurrogateKey,
COUNT(MD_Time_TimeInteger) AS VMF_KCInUse_Used
FROM
      MF_KeyedComputerInUse
   GROUP BY
MD_KeyedComp_SurrogateKey,
MD_Date_DateInteger,
-- Zero out minutes and seconds component of time integer
((MD_Time_TimeInteger / 65536) * 65536)
) mfk
ON compDateTime.MD_KeyedComp_SurrogateKey = mfk.MD_KeyedComp_SurrogateKey
AND compDateTime.MD_Date_DateInteger = mfk.MD_Date_DateInteger
    -- Zero out minutes and seconds parts of MD_Time_TimeInteger (lowest 16 bits) and join to VMD_TimeToHour
AND compDateTime.MD_Time_TimeInteger = mfk.MD_Time_TimeInteger

List of outgoing view references of the view VMF_KeyedComputerInUse

ViewReference_234

VMD_TimeToHour (Shortcut)

ViewReference_236

VMD_KeyedComputer

ViewReference_237

VMD_Date (Shortcut)

ViewReference_238

VMD_Building (Shortcut)

ViewReference_239

VMD_IssueTrackingAsset (Shortcut)

ViewReference_240

VMD_CarletonDepartment (Shortcut)

ViewReference_248

VMD_IssueTrackingUser (Shortcut)

ViewReference_249

VMD_Room (Shortcut)

List of referencing views of the view VMF_KeyedComputerInUse

VMF_KeyedComputerInUseWithRoomUtilization

List of diagrams containing the view VMF_KeyedComputerInUse

ViewsLabUsageDiagram

List of permissions of the view VMF_KeyedComputerInUse

SELECT,VIEW DEFINITION

IssueTrackingReader

List of view columns of the view VMF_KeyedComputerInUse

MD_KeyedComp_SurrogateKey

MD_Date_DateInteger

MD_Time_TimeInteger

Time, to the granularity of one hour (minutes and seconds fields are always zero).

VMF_KCInUse_Year

Used solely for partitioning purposes; can be calculated from MD_Date_DateInteger.

MD_ITAsset_SurrogateKey

MD_ITUser_SurrogateKey

MD_Room_SurrogateKey

MD_Building_SurrogateKey

MD_CarlDept_SurrogateKey

VMF_KCInUse_Used

Count of how many, of the six available ten-minute chunks available during each hour, a given computer had an active login (i.e., a logon without, yet, a corresponding logout).

VMF_KCInUse_Available

Will always be 6 - unless, of course, we aggregate the total.

Used as a divisor for VMF_KCInUse_Used, which gives the count of how many, of the six available ten-minute chunks available during each hour, a given computer had an active login (i.e., a logon without, yet, a corresponding logout).

Together these figures give us a good % utilization figure.

Home