December 27, 2017 at 5:18 pm
I'm making a query which can provide the last compliance status for updates for a collection per machines[SCCM Database]. The issue that I am having is that when i run the query on multiple machines, i am getting incorrect results. Currently, i am trying it with a CTE, but i don't know how to pass the computer value to the CTE function, below is the query i am using which is returning nothing when I run it, but if I move the computername inside the CTE function it works as intended.
WITH cteUpdateComplianceStatus (myResourceID, myUpdateStatus,myComp,myLastStatusCheckTime)
AS
(
SELECT TOP 1
UCS.ResourceID AS 'myResourceID',
UCS.Status AS 'myUpdateStatus',
RS.Name0 AS 'myComp',
UCS.LastStatusCheckTime AS 'myLastStatusCheckTime'
FROM dbo.v_Update_ComplianceStatus UCS
JOIN dbo.v_R_System RS ON UCS.ResourceID = RS.ResourceID
WHERE UCS.LastStatusCheckTime IS NOT NULL
ORDER BY UCS.LastStatusCheckTime DESC
)
SELECT
cte.myComp AS 'COMPUTER',
cte.myUpdateStatus AS 'UpdateStatus'
FROM dbo.v_R_System vRS
JOIN cteUpdateComplianceStatus cte ON cte.myResourceID = vRS.ResourceID
WHERE vRS.Name0 = 'COMPUTERNAME'
December 27, 2017 at 5:41 pm
SQLNu - Wednesday, December 27, 2017 5:18 PMI'm making a query which can provide the last compliance status for updates for a collection per machines[SCCM Database]. The issue that I am having is that when i run the query on multiple machines, i am getting incorrect results. Currently, i am trying it with a CTE, but i don't know how to pass the computervalue to the CTE function, below is the query i am using which is returning nothing when I run it, but if I move the computername inside the CTE function it works as intended.
WITH cteUpdateComplianceStatus (myResourceID, myUpdateStatus,myComp,myLastStatusCheckTime)
AS
(
SELECT TOP 1UCS.ResourceID AS 'myResourceID',
UCS.Status AS 'myUpdateStatus',
RS.Name0 AS 'myComp',
UCS.LastStatusCheckTime AS 'myLastStatusCheckTime'FROM dbo.v_Update_ComplianceStatus UCS
JOIN dbo.v_R_System RS ON UCS.ResourceID = RS.ResourceIDWHERE UCS.LastStatusCheckTime IS NOT NULL
ORDER BY UCS.LastStatusCheckTime DESC
)SELECT
cte.myComp AS 'COMPUTER',
cte.myUpdateStatus AS 'UpdateStatus'
FROM dbo.v_R_System vRS
JOIN cteUpdateComplianceStatus cte ON cte.myResourceID = vRS.ResourceID
WHERE vRS.Name0 = 'COMPUTERNAME'
You have the cte doing a select 1 so it's only selecting one row with the most recent laststatuschecktime.
Try removing the top 1 and filter by computer name outside of the cte
Sue
December 27, 2017 at 5:53 pm
Sue_H - Wednesday, December 27, 2017 5:41 PMYou have the cte doing a select 1 so it's only selecting one row with the most recent laststatuschecktime.
Try removing the top 1 and filter by computer name outside of the cteSue
Hi Sue,
I need to use the TOP 1 with ORDER BY so i can get only one value per machine for the status.Only way i was able to get this to work.
December 27, 2017 at 6:36 pm
SQLNu - Wednesday, December 27, 2017 5:53 PMSue_H - Wednesday, December 27, 2017 5:41 PMYou have the cte doing a select 1 so it's only selecting one row with the most recent laststatuschecktime.
Try removing the top 1 and filter by computer name outside of the cteSue
Hi Sue,
I need to use the TOP 1 with ORDER BY so i can get only one value per machine for the status.Only way i was able to get this to work.
Although you didn't provide any data or ddl, I am guessing it's because you have multiple status checks for each machine and you only want the most recent one. And in that case, no you don't need to use top 1. If that's what you need and need it by computer name, you don't even need a CTE. But the requirements aren't nearly clear enough. If you just want to query the table for the last status check for a computer and there are multiple computers and multiple status checks for each, it would just be something like MAX(UCS.LastStatusCheckTime) and group by the other columns. But again, no data or information on the other columns.
If you want a CTE (doesn't sound like it's needed) you could just include a row_number function and get a row number for every computer ordered by last status check. Inside the CTE you would add something like ROW_NUMBER() OVER(Partition by Name0 order by LastStatusCheckTimeDesc) as RowNum
Then when you query the CTE, it would just be where RowNum = 1 to get the most recent status check time.
Sue
December 28, 2017 at 8:21 am
Sue_H - Wednesday, December 27, 2017 6:36 PMAlthough you didn't provide any data or ddl, I am guessing it's because you have multiple status checks for each machine and you only want the most recent one. And in that case, no you don't need to use top 1. If that's what you need and need it by computer name, you don't even need a CTE. But the requirements aren't nearly clear enough. If you just want to query the table for the last status check for a computer and there are multiple computers and multiple status checks for each, it would just be something like MAX(UCS.LastStatusCheckTime) and group by the other columns. But again, no data or information on the other columns.
If you want a CTE (doesn't sound like it's needed) you could just include a row_number function and get a row number for every computer ordered by last status check. Inside the CTE you would add something like ROW_NUMBER() OVER(Partition by Name0 order by LastStatusCheckTimeDesc) as RowNum
Then when you query the CTE, it would just be where RowNum = 1 to get the most recent status check time.Sue
Thank you Sue for this.
I am not that experience with SQL, so i'm not sure how to implement what you state above. I did try to use the MAX function, but i don't think i did it properly.
Below is the original query i was able to find the information i need to manipulate. I am using a CTE, as it was advice by one of my co-workers since a lot more can by done with it.
SELECT
RV.Name0,
Case UCS.Status
when 0 then 'State unknown'
When 1 then 'Not required'
When 2 then 'Not Compliant'
When 3 then 'Compliant'
Else 'error'
End as 'UpdateStatus',
UCS.LastStatusChangeTime
FROM
v_Update_ComplianceStatus UCS
join v_R_System RV on UCS.ResourceID = RV.ResourceID
join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
join v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
join v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
JOIN v_Collection Col on Col.CollectionID = FCM.CollectionID and Col.Name = 'COLLECTIONNAME'
join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = RV.ResourceID
i have attached a screenshot of the data I get when i run the Query.
This collection has 8 Computers, and i want the results to only show the 8, but I am getting 5137.
December 28, 2017 at 8:43 am
Without table DDL and sample data, we're just guessing. However, this may be close to what you're looking for:
WITH NumberedbyMachine AS (
SELECT
RV.Name0,
UCS.Status,
UCS.LastStatusChangeTime,
ROW_NUMBER() OVER (PARTITION BY Name0 ORDER BY LastStatusChangeTime DESC) AS RowNo
FROM v_Update_ComplianceStatus UCS
join v_R_System RV on UCS.ResourceID = RV.ResourceID
join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
join v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
join v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
JOIN v_Collection Col on Col.CollectionID = FCM.CollectionID and Col.Name = 'COLLECTIONNAME'
join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = RV.ResourceID
)
SELECT
Name0,
Case Status
when 0 then 'State unknown'
When 1 then 'Not required'
When 2 then 'Not Compliant'
When 3 then 'Compliant'
Else 'error'
End as UpdateStatus,
UCS.LastStatusChangeTime
FROM NumberedbyMachine
WHERE RowNo = 1
John
December 28, 2017 at 8:56 am
Note to self: last status check per computer
Without data, it's harder to see what's going on, but you could try this pattern. I'm using AdventureWorks2014 for data.
-- for each customer, get his 3 most expensive invoices.
SELECT c.CustomerID
, t.Subtotal
FROM Sales.Customer c
CROSS APPLY (SELECT TOP 2 SubTotal
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = c.CustomerID
ORDER BY soh.TotalDue DESC) t;
To fit this to your example, your top query would be for the computer, and then the CROSS APPLY would be against the status checks. You could just just TOP 1.
December 28, 2017 at 9:24 am
The two main methods are the CROSS APPLY (suggested by Piet) and the CTE/ROW_NUMBER (suggested by Sue and John). Given the density of the child table with respect to the parent table (642.125 [5137/8]), it's likely that the CROSS APPLY will perform better than the CTE/ROW_NUMBER approach, but you should test both. Both also require an index on at least the ResourceID.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 28, 2017 at 10:04 am
John Mitchell-245523 - Thursday, December 28, 2017 8:43 AMWITH NumberedbyMachine AS (.....John
thnx john.
This actually worked.
WITH NumberedbyMachine AS (
SELECT
RV.Name0,
UCS.Status,
UCS.LastStatusChangeTime,
ROW_NUMBER() OVER (PARTITION BY RV.Name0 ORDER BY UCS.LastStatusChangeTime DESC) AS RowNo
FROM v_Update_ComplianceStatus UCS
join v_R_System RV on UCS.ResourceID = RV.ResourceID
join v_UpdateInfo UI on UCS.CI_ID = UI.CI_ID
join v_UpdateDeploymentSummary UDS on UCS.CI_ID = UDS.CI_ID
join v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
join v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
JOIN v_Collection Col on Col.CollectionID = FCM.CollectionID and Col.Name = 'MTN - H+H - Custom Compliance - HCLL Servers'
join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = RV.ResourceID
)
SELECT
Name0,
Case Status
when 0 then 'State unknown'
When 1 then 'Not required'
When 2 then 'Not Compliant'
When 3 then 'Compliant'
Else 'error'
End as UpdateStatus,
LastStatusChangeTime
FROM NumberedbyMachine
WHERE RowNo = 1
I've bolded the modifications I made in order to get it to work with my DDL.
I will try
Piet's
query next to see if I get better performance, and will continue to tinker around to make sure the information is along the way is correct.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply