SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE Query to find Unique Status for Update Per Machine


CTE Query to find Unique Status for Update Per Machine

Author
Message
SQLNu
SQLNu
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 36
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[s] 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'


Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41687 Visits: 11549
SQLNu - Wednesday, December 27, 2017 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[s] 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'



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



SQLNu
SQLNu
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 36
Sue_H - Wednesday, December 27, 2017 5:41 PM
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

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.

Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41687 Visits: 11549
SQLNu - Wednesday, December 27, 2017 5:53 PM
Sue_H - Wednesday, December 27, 2017 5:41 PM
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

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



SQLNu
SQLNu
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 36
Sue_H - Wednesday, December 27, 2017 6:36 PM


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

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.

Attachments
12-28-2017 10-10-40 AM.png (20 views, 44.00 KB)
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97803 Visits: 18442
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

pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39071 Visits: 15917
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.
drew.allen
drew.allen
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45187 Visits: 14686
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
SQLNu
SQLNu
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 36
John Mitchell-245523 - Thursday, December 28, 2017 8:43 AM
WITH 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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search