PENDING sql query to create a report

[dj3094]

Hello experts,

I am trying to create a sql query to get below information
systemname, last seen, domain, OS, missing patches, last reboot time, patch compliance, updated scan error, last scan update, maintenance window. Is that possible?

Regards

 

[Garth]

What do you have right now and what isn't working for you?

 

[dj3094]

I have not started yet, checking if that is possible. I will try to put some views together

 

[dj3094]

I came up with this but getting error

SELECT        dbo.v_R_System.Name0, dbo.v_R_System.Full_Domain_Name0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_UpdateScanStatus.LastScanState, dbo.v_UpdateScanStatus.LastErrorCode,
                         dbo.v_R_System.ResourceID
FROM            dbo.v_R_System INNER JOIN
                         dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN
                         dbo.v_UpdateScanStatus ON dbo.v_R_System.ResourceID = dbo.v_UpdateScanStatus.ResourceID
WHERE        (dbo.v_FullCollectionMembership.CollectionID = N'******')

 

[Garth]

You problem is the you are linking collection id to resource id., it will need to link to resource id only.
dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID

 

[Garth]

I have not started yet, checking if that is possible. I will try to put some views together

All the SQL views are listed here. https://docs.microsoft.com/en-us/me...lviews/sql-server-views-configuration-manager

 

[dj3094]

we have this huge report which give all the info if we run on small collection, but running it on all machines errors out. Report processing has been canceled by the user. (rsProcessingAborted)

 

[Garth]

we have this huge report which give all the info if we run on small collection, but running it on all machines errors out. Report processing has been canceled by the user. (rsProcessingAborted)

First off the download doesn't work. Secondly the bigger the report / sql the this is to be expected. you need to review and edit your report/query.

 

[dj3094]

Thanks
Sorry for that, I have attached again

 

[Garth]

Thanks
Sorry for that, I have attached again

I'm not sure what you are expecting but you are querying some unsupported views, 10+ temp table within this query set and the query is huge.

 

[dj3094]

thats as written by someone, is there simple way to get the same results?

Thanks

 

[Garth]

You should go back to the author and ask them to fix it for you.

 

[dj3094]

Thanks Garath