Home General Discussion
Options

Time Worked Query in ServiceManagement DB

Daniel_Polivka1Daniel_Polivka1 Customer Adept IT Monkey ✭✭

Hi everyone, I was just curious if anyone has a query for Time Worked from the ServiceManagment DB. I am having a tough time getting mine to show the data I am expecting. Using SMLets, I can see exactly what I want, but it's painfully slow in relation to a SQL query. Here's the SMlets query, in case anyone is interested:

$irClass = Get-SCSMClass -name "System.WorkItem.Incident$"

$AllIRs = Get-SCSMObject -Class $irClass

$WIHasBillableTimeRelClass = Get-SCSMRelationshipClass System.WorkItemHasBillableTime$

$BillableTimeHasUser = Get-SCSMRelationshipClass System.WorkItem.BillableTimeHasWorkingUser$


foreach ($WorkItem in $AllIRs)

{

  foreach ($TimeEntry in (Get-SCSMRelatedObject -Relationship $WIHasBillableTimeRelClass -SMObject $WorkItem))

  {     

    [pscustomobject] @{

      WorkItem = $WorkItem.Name

      Analyst = (Get-SCSMRelatedObject -Relationship $BillableTimeHasUser -SMObject $TimeEntry).displayname

      TimeWorked = $TimeEntry.TimeInMinutes

      TimeEntered = $TimeEntry.LastModified

    }

  }

}


And here is the SQL query I'm currently running against the ServiceManagement DB:

select wi.workitemid as 'ID',

 billableTime.System_WorkItem_BillableTimeHasWorkingUser_DisplayName as 'Working User',

 billableTime.ModifiedDate,

 billableTime.TimeInMinutes as 'Time Worked'

 SUM(billableTime.TimeInMinutes) as 'Time Worked'

from WorkItem as wi

 inner join DisplayString as STATUS on WI.StatusId = STATUS.ElementID

 inner join dbo.cpex_SmRelationship as rel on wi.Id = rel.SourceEntityId

 inner join cachert.csnCached_MT_System_WorkItem_BillableTime as billableTime on rel.TargetEntityId = billableTime.Guid

 inner join DisplayString as SUPPORTGROUP on WI.TIERID = SUPPORTGROUP.ElementID

where relationshiptypeid = '33835caa-05f9-f881-7bbe-e407dfdb6c55' -- Billable Time relationship class id

 and billableTime.LanguageCode = 'ENU'

group by wi.WorkItemId, BillableTime.System_WorkItem_BillableTimeHasWorkingUser_DisplayName

order by BillableTime.System_WorkItem_BillableTimeHasWorkingUser_DisplayName


My results in the SQL query are just really off. I only get 8 results, and I get way more than that using the SMlets command.

Sample with the SMlets command:

My SQL output:


Answers

Sign In or Register to comment.