Time Worked Query in ServiceManagement DB
Daniel_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
Actually, probably a better and more concise question here would be, has anyone been able to join these two ServiceManagement tables?
@Daniel_Polivka1 when I was met with this same task I found an article from @Liz_Ross that helped. This may help you as well - I queried the ServiceManager database instead though.
Here's the post:
thanks @Liz_Ross !