Source
r/SCCM•
jpier

Need help with a simple task.....

So i haven't done SCCM in over 2 years, they let the SCCM guy go and now I have some SQL from Quest that we need to run in SCCM. Oh yeah and my knowledge in SQL is shit.

I created a query and pasted the language in but when i run it it says invalid view. When i created the query I left the object type as System Resource and then pasted in the query language.

Any help is greatly appreciated.

SELECT DISTINCT
    v_R_System.Netbios_Name0,
    v_R_System.Operating_System_Name_and0,
    v_GS_WORKSTATION_STATUS.LastHWScan,
    v_GS_LastSoftwareScan.LastScanDate
FROM v_R_System
    LEFT JOIN v_GS_LastSoftwareScan on v_R_System.ResourceID = v_GS_LastSoftwareScan.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS on v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
    ORDER BY v_R_System.Netbios_Name0

Posted Aug 22, 2019, 10:51 AM UTC

612
limeybrit•

You're trying to run that in a SCCM query? You need to translate it from SQL views to WQL classes instead.

v_R_System is SMS_R_SYSTEM, v_GS_Workstation_Status is SMS_G_SYSTEM_WORKSTATION_STATUS, etc.

8
merlinfrombelgium•

It's not clear from your statement whether you're running this query in the Queries node of the SCCM console or against the SQL DB directly. Because if it's in the SCCM console, these views won't work.

I've attempted to recreate your query in SCCM and this should be close to what you're expecting:

select distinct SMS_R_System.NetbiosName, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, SMS_G_System_LastSoftwareScan.LastScanDate from  SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId order by SMS_R_System.NetbiosName
5
jpierOP•

Yeah i'm in the SCCM console under monitoring and then creating a new query under there. I suppose thats why i'm getting invalid view. I did say i was shit with SQL!

Thanks for recreating that, you rule!

2
Reset_Assured•

Creating a query in the SCCM console isnt SQL it’s WQL. Might be why you had issues finding queries for it.

5
jpierOP•

yeah i was just pasting what quest sent

1
merlinfrombelgium•

Glad I could help. As u/Reset_Assured said, queries in the SCCM console use WQL, which is very similar to SQL queries. But WQL targets WMI, not a SQL DB.

Welcome back to the SCCM crew!

1
jpierOP•

Yeah I'll have to learn wql now.

1
MasterSlax•

You can just run this against the SQL DB if you want. It’s a very simple inventory query.

2
MagmaManOne•

Man, there sure are a lot of "they let the SCCM guy go" posts. What are these guys doing?

1
jpierOP•

right!

2
Export_User•

Looking for a new job, I assume.

2
merlinfrombelgium•

They must be working with Intune now.

I hear it's replacing SCCM... 🤔

2