+2We’re going to be attempting to upgrade several hundred machines to Windows 11. I’m using a combination of SCCM Reports and PowerShell to filter and extract the workstation names that need to be upgraded.
I’m wondering what functions in IIQ would make sense to hold this data.
If this is going to be scalable, it would need to be automatable through a CSV upload of some sort too.
-Marcus
Best answer by jclark
This may not completely answer you question but could be useful…
We have the SCCM integration set up with iiQ, which uses an executable that runs a SQL query on your SCCM server and sends the results back to iiQ.
To target devices that won’t update to Windows 11, we modified their default SQL query to include more info, and are mapping that data back to a custom field in iiQ that we can include in a custom view.
If you’re using this integration you can include these lines...
---(SELECT UpgExProp0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS UpgExProp0,
---(SELECT UpgExU0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS UpgExU0,
---(SELECT Version0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS Version0,
(SELECT CASE WHEN EXISTS (
Select UpgExProp0, UpgExU0, Version0
FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS
WHERE v_GS_UPGRADE_EXPERIENCE_INDICATORS.ResourceID = sys.ResourceID AND UpgExProp0 = 'Red' AND UpgExU0 = 'Red' AND Version0 LIKE '%24H2'
)
THEN CAST('False' AS varchar)
END
) AS WinUpgradablebetween these lines in the original…
....
(SELECT SUM(Capacity0) from v_GS_PHYSICAL_MEMORY PM WHERE PM.ResourceID = SYS.ResourceID) AS PhysicalMemory,
{{{new code mentioned above goes here}}}
FROM v_R_System SYS
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID
....
This gives us a value of ‘false’ for anything in SCCM that won’t update to Windows 11. The only caveat is that you may occasionally have to update the LIKE '%24H2' to a newer target version of Windows 11.
The end result for us looks like this:
+2We’re trying not to muddy the ticket statistics of the sites, so one idea was to create an ad-hoc “site” called “Windows 11”, and then use tags for the location of the device for this project.
+5
Have you tried to create a view using the filter Microsoft Intune Data: OS Version?
Create a view with these columns and filter. You can filter even further by location to provide a list to techs.
Add the column for Device Name to easily look up in Intune.
Not sure if this would accomplish pulling the information you are looking for. If not please clarify and we’ll try again 😊
Thank you!
+2Hi Leann,
It’s a nice idea - unfortunately, not all the devices running windows 10 will support 11, so we’re trying to filter from the larger population of Windows 11 that can be upgraded, vs which can’t.
That’s where the data from SCCM comes into play.
+5
This may not completely answer you question but could be useful…
We have the SCCM integration set up with iiQ, which uses an executable that runs a SQL query on your SCCM server and sends the results back to iiQ.
To target devices that won’t update to Windows 11, we modified their default SQL query to include more info, and are mapping that data back to a custom field in iiQ that we can include in a custom view.
If you’re using this integration you can include these lines...
---(SELECT UpgExProp0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS UpgExProp0,
---(SELECT UpgExU0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS UpgExU0,
---(SELECT Version0 FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS AS[link] WITH(NOLOCK) WHERE[link].ResourceID = sys.ResourceID AND Version0 LIKE '%24H2') AS Version0,
(SELECT CASE WHEN EXISTS (
Select UpgExProp0, UpgExU0, Version0
FROM v_GS_UPGRADE_EXPERIENCE_INDICATORS
WHERE v_GS_UPGRADE_EXPERIENCE_INDICATORS.ResourceID = sys.ResourceID AND UpgExProp0 = 'Red' AND UpgExU0 = 'Red' AND Version0 LIKE '%24H2'
)
THEN CAST('False' AS varchar)
END
) AS WinUpgradablebetween these lines in the original…
....
(SELECT SUM(Capacity0) from v_GS_PHYSICAL_MEMORY PM WHERE PM.ResourceID = SYS.ResourceID) AS PhysicalMemory,
{{{new code mentioned above goes here}}}
FROM v_R_System SYS
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = SYS.ResourceID
....
This gives us a value of ‘false’ for anything in SCCM that won’t update to Windows 11. The only caveat is that you may occasionally have to update the LIKE '%24H2' to a newer target version of Windows 11.
The end result for us looks like this:
+2That’s fantastic, J! We currently have the Intune integration turned on (hybrid AD/AAD environment). I’m not sure if we can/should connect both Azure and on-premises AD Applications? Is that something that many customers do I wonder.
-Marcus
We are using both integrations. Originally we only used the Intune integration in iiQ, since all of our devices were hybrid-joined as well, but there were a few decisions driving the use of the SCCM integration.
Essentially, anything that’s in SCCM currently will stay there for now unless it either won’t upgrade to 11 (it gets decommissioned or converted to a ChromeOS Flex device) or it needs a re-image (Intune joined when wiped).
Once we get to a manageable number of devices remaining in SCCM (in the future), we’ll likely try to canvas the district to get them moved so we can wash our hands of SCCM.
Also, since the integrations basically match on serial number, it’ll display metadata from both integrations on the same device, so you shouldn’t end up with duplicate devices in iiQ or anything. It takes a little thought to make sure there aren’t any collisions with mapping fields, but it’s not horrible.
+2That’s what I was concerned about - if duplicate devices would start to show up. If it uses the primary key of the serial number, it sounds like there shouldn’t be much of an issue.
Can you expand on what you mean with collisions of mapped fields?
Hypothetical example: Say you have both apps set to map something to the Asset tag field. If both integrations don’t contain the same value, or you don’t have writeback options enabled (if applicable) then you may end up overwriting the field on every sync of each app, back and forth. Unlikely scenarios in general, just something to watch out for if you start mapping to their pre-defined list of fields.
We’ve had scenarios where we had a typo on an asset tag for an iPad in Jamf that matched that of a Chromebook (like someone typed 123465 when it should have been 123456) - so in the series of matching on asset tag, it kept updating the single device on every sync. Our Chromebook would become an iPad temporarily until the Google sync ran again a couple hours later 😂
+5
You were thinking the same as where I was going with my question of what field in SCCM are you pulling this data from and then map that in iiQ. I am not as familiar with executables running SQL queries, so that insight is super helpful!
MDashoff 140a3a1 redlandsusd I hope this helps you complete your project successfully! I used to work in a district and upgraded all devices to Windows 11 last year since 10 was going EOL this year and realize it can be a daunting project so good luck!
Thank you!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
