Search the Community
Showing results for tags 'Query'.
-
I am trying to build a collection based on the Last Online time (within an arbitrary number of days). I know typically this is done using the Heartbeat, ClientActivity, or Hardware scan property. but I would like to see all machines, to identify which machines may not have the client installed. I cant for the life of me find where the column in the collections view populates from. I have the queries to filter out machines based on Last Hardware Scan shown below. I am looking for something very similar, but as I said, i cant find the property for "Last Online Time"
-
I'm using the following report and checking against all systems for %iTunes%. My report comes back with around 35 systems. However, I've created a collection for iTunes, and it is only showing 9 systems. The query I am using for the collection is the following. select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "iTunes" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "iTunes" Any suggestions? Thanks!
-
I have a requirement to create 2 collections from AD groups and return only the users/machines that are in both. I have tried something like the following:- SELECT ... WHERE ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupA>) AND ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupB>) But if a user is in both groups the result is 0 not the expected result e.g Collection from ADGroup1 = 12345 Collection from ADGroup2 = 34567 Collection items in ADGroup1 and ADGroup2 = 345 -
__GENUS : 2 __CLASS : Win32Reg_AddRemovePrograms __SUPERCLASS : __DYNASTY : Win32Reg_AddRemovePrograms __RELPATH : Win32Reg_AddRemovePrograms.ProdID="{93F653D3-ED8B-4A04-BA58-3DF60B37D58B}" __PROPERTY_COUNT : 5 __DERIVATION : {} __SERVER : NAME_HIDDEN __NAMESPACE : root\cimv2 __PATH : \\NAME_HIDDEN\root\cimv2:Win32Reg_AddRemovePrograms.ProdID="{93F653D3-ED8B-4A04-BA58-3DF60B37D58B}" DisplayName : IBM Spectrum Protect Client InstallDate : 20170621 ProdID : {93F653D3-ED8B-4A04-BA58-3DF60B37D58B} Publisher : IBM Version : 08.01.0000 PSComputerName : NAME_HIDDEN OK. Here is my issue. I have created a collection that I am attempting to populate with all computers that contain the specific application listed above (IBM Spectrum Protect Client). I created the collection and a query using the criteria button, added the installed application, and chose the display name to match what is contained above. I do membership update, yet no computers appear in the collection. I can run a report (Companies & Products > Computers with specific software registered in Add Remove Programs) and match the same Display name, and return results. The reason I need the collection for the specific software, is I need to install an update that is only applicable to the specific version listed. Thanks
-
I need some help with a query to create a collection for Visio Pro and Project Pro 2016 - en-us. When i just query for i am getting results, but when i add en-us i am getting nothing, but i know i have them out there
-
Running 1610, I stumbled across something yesterday, and I'm wondering if anyone else has seen this: changing just the name of a query used to populate a collection breaks the collection. Steps to replicate: Change the name of the query being used as a membership rule for a collection. Collection's icon gets an hourglass, even though nothing about its membership actually changed. Hourglass doesn't go away, and collection membership no longer updates. The first time I saw this was with a query-based collection looking for %server% in operatingSystemNameAndVersion. Even after adding new devices to the environment and waiting overnight, those devices didn't get pulled into the collection this morning, even though delta was enabled so they should've been picked up within minutes. "Fixed" it by deleting and re-creating the collection. I have another, separate 1610 environment I work with regularly, so I jumped over to that one and created a new query-based collection to test with, looking for %string% in the system's name. Changed the name of the query, and sure enough, the collection's hourglass refused to go away (only left this one for 15 minutes, not overnight). Changing the actual query to %string2% seems to have gotten the collection working again, though obviously isn't really a fix, since string and string2 are different; just need to change back to the original string. Back to the initial environment, with a collection looking for system name ending with a number and a specific service existing and being set to auto-start: change the name of the query, and the hourglass has been stuck for 30 minutes. Changing the query in some non-meaningful way (adding a second space character after an "and") doesn't help. Changing the query in a meaningful way (look for a different service name) gets the collection working again. So it doesn't seem to matter what the query is looking for - three queries looking for three different things (though second and third are similar) and all of them break the collection if just the query name is change without changing the query itself. -
Hi, Our current Task sequence for the Surface pro devices has been working great for sometime. Now with our entity moving towards Windows 10, I'd like to just add on the driver package step in my task sequence to get the needed driver package at it's desired OS. Currently, I have one driver package for Surface devices with all drivers and firmware as shown in the tutorials provided from here. My current WMI query for identifying the device is the basic " Select * From Win32_ComputerSystem WHERE Model LIKE "%Surface Pro 3% " Which works fine for when you have two different generations of Surface Pro devices. With our WIn10 migration coming up, I've gone ahead and imported the Win 10 driver package into SCCM, but I need to find a find a better way to query the two different driver packs. Otherwise the TS will install both packages to the system, defeating the whole point... So, my question is- What is the best way to query the driver packages for the same device but each being guided at a different OS? (we need to keep both packages present for the time being) By OS? - with the builds of windows 10 changing every 8 months, how will this(if at all) affect my query? Any suggestions or wake up calls is always appreciated!
-
hi guys, I need to come up with a report that will show how many machines received any application deployment and that it was successful. any ideas queries out there that can help me accomplish this task? we do a lot of deployments on a daily basis but i need to show this report to tally all deployments we have done and how many machines received them. thanks in advance
-
Hi, all. SCCM 2012 SP1 with CU5. I'm seeing weirdness when setting up device collections that attempt to include systems based on both 32 bit and 64 bit applications being installed. I'm trying to combine "Installed Applications.Display Name" (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) with "Installed Applications (64).Display Name" (SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName), but the result isn't what I'm expecting - the collection actually shrinks, instead of growing, which I would think is impossible, since it's an OR. Example using Java: Criteria "Installed Applications.Display Name" like '%java 8' I get 34 members in the collection If I then change the criteria to "Installed Applications.Display Name" like '%java 8%' OR "Installed Applications(64).Display Name" like '%java 8%' Update membership, and the collection drops to 26 members. Some of the dropped ones are systems that I know for a fact have 32-bit Java 8 installed. All of the dropped systems are running a 32 bit version of Windows. I would expect that this would get *any* systems, regardless of OS architecture, with 32-bit Java 8 (since these match the statement before the OR) plus any systems with 64-bit Java 8 (since they match the statement after the OR). What appears to actually be happening is that once the statement to check the 64 bit app list is added, all systems that are running a 32 bit OS are removed from the collection, even though they do match the first criterion. Is this intended behavior? Do I need to massage the query statement? To this point, I've just been using the auto-generated query statement that setting up via the GUI comes back with. Or is this just not possible, and I'd need to create two (32 bit via query, 64 via query + include other collection) or three (32, 64, both via inclusion) separate collections?
-
I work at a community college and we have sccm clients running Office versions from 2003-2013. I have scoured the web trying to find a query that could accomplish this task, but with my lack of SQL knowledge and specificity of the statement, I couldn't find a solution. Any thoughts?
-
Hi, Does anyone know where to pull info like an error from Windows Update? I want to make a SCCM-collection based on computers that get a specific error in Windows Update. Appreciate the help guys
-
Hi. I'm hoping someone has an easy answer to this.... I need to create a SCCM collection which membership is based on the clients Content location. We have an environment with just over 147 Distribution points (Primary, Secondary, and DP's below both these primary & secondary sites) The requirement comes to work out SLA excuse performance (we have a very strict SLA, where we need to deploy packages to clients in under 48 Hours, but due to network constraints we can't even get the content to certain DP's in this environment, in under 4 days). We have over 4000 boundaries, with just over 29 000 active SCCM clients. (1 central, 7 Primary and 19 secondary sites) Most boundaries are configured as protected to their relevant DP. We've identified which DP's are sitting on the end of problematic / slow network links (based on a content distribution report), but I now have to figure out which Clients are trying to get content from these DP's, and include these clients in Excuse SLA performance on a month to month basis. I have a powershell script that basically gives a count for how many clients are getting content from which DP, however I need to manually run this on an ongoing basis. I would like to know if its possible to create a collection for each of the 147 DP's in the environment, and base each of these collection memberships on the clients that are getting content from these DP's? Hope this makes sense, and hope it's possible?
-
Hello All, I'm looking for a query or report that will return the network information for all systems or all machines in a collection. Our environment unfortunately still uses wins and I need to see how many machines out in the wild might be missing their secondary wins server address. Google has proven fruitless. Thanks, Mike
-
I have a working query (Membership Rule) that gathers computers into a collection based on computer name. I also have a working query that gathers by OS (in this example we will use Windows 7). I have been trying to get the two combined into one query (basically an "and" statement instead of creating two separate membership rules, which apparently is an "or" statement). I readily admit that building queries is not my strongpoint, and I don't even know if this is the proper way to go about it, but I have been tinkering with it quite a bit. Here are the two queries...I know that both of them work individually, but I keep getting syntax errors when I try to combine them. select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "%Windows 7%" and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName like "Example" Is there a way to combine these two statements into one? Any help would be very much appreciated! -
I am looking for a query that finds all computers joined to the domain since XX date. I tried using the query below which looks back over the last week for new computers but this also discovers any computers that have been modified in the last 7 days. Does anyone have a query that is based on date created? select SMS_R_System.Name, SMS_R_System.CreationDate FROM SMS_R_System WHERE DateDiff(dd,SMS_R_System.CreationDate, GetDate ()) <= 7
-
I am working on helpdesk remediation strategies for virus notifications. I have setup alerts and find that most viruses get cleaned so the machine does not show up in the at risk collection. I want to setup a collection where a machine is infected so my helpdesk can deploy a cleaning package if the machine cannot be replaced right away. I have setup a device collection with the following criteria however I wanted to make sure this is correct. Usint he GUI it is set to Criteria Antimalware Infection Status.ComputerStatus is equal to 2 sql view looks like this select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_AntimalwareInfectionStatus on SMS_G_System_AntimalwareInfectionStatus.ResourceId = SMS_R_System.ResourceId where SMS_G_System_AntimalwareInfectionStatus.ComputerStatus = 2 Jus want to confirm if this looks correct. Running the query shows 1 machine I know to be cleaned but recently infected, however it does not list a second machine listed in the infected computers report, however the report is looking at a week long data.
-
I've looked around a bit but have yet to come up with a good query or report to show the number of physical processors on our servers. Can anyone help? -
Hi we've a large number of devices with a few PCI and PCIe devices installed, not in the same slots. would like to push out a configuration script, the settings of which, for long-winded reasons or prerequisites, depends on how many, and which, slots are in use. the solution to this would therefore be query based collections and separate packages containing the variables and then deployment en masse allowing configuration to be tailored to the hardware. -hopefully avoiding the months of work which would be a manual visual audit and inspection and then collation into collections. NOTE: it's actually irrelevant what the devices are, more whether there is, or isn't a device present in a slot, or which combination of slots are in use the problem with this plan is i can't actually find anything on which to base my query in SCCM 2012 itself, i can query an individual machine with powershell and return the slots in use. so i know the WMI classes and attributes exist but cannot find how to include these in a collection query, nor any way to report this in general. modifying and importing an updated sms_def.mof would be great but i'm unsure of what to add to this file to include the correct attributes. does anyone have any insight or experience of this, or any where they can point me in the right direction to get the correct details collected by the client and then to process this into a valid collection. thanks for your help
-
Morning Guys, I have searched everywhere online for a query that can help me list all installed softwares on all computers to include Product ID & Version. I need to uninstall an application from all computers but the problem is that there are no install files and there is about 6 different versions across the network. So my plan is to collect all Product ID's and just create a TS to remove them. Thanks in advance for any help. GT SCCM 2012 (Just 1 year xperience) -
Hello everyone, I have a query that I'm unable to make. Here's the situation. I have a collection which contain x number of computer (let's say 200). I want a query that return the top x computer from the list (let's say 33% of it or top 50 computers) and put them into a collection. The idea is to have a way to distribute update to specific computers based on there name order (like serial number), but I want it to be dynamic and always have the same ratio (33% or a top 50, something like that). In SQL, I would use "select top (33) PERCENT", but I can't find the equivalent in WQL. Thank you!
-
I'm pretty new to building queries and am currently trying to familiarize myself with SQL and the SQL Views to further help run queries. I've been having a hard time trying to build a collection of computers that have one of our inventoried software items on it. Within Inventoried Software, I have several products with different names all containing the word Coupon. Coupon toolbars, programs, etc. These are all security threats, and I have positive software counts for each. I'd like to create a collection (or run a report) of all machines with this software installed so I can remove the software. I've tried different variations of criteria in my query statement without success. Installed software, installed application, etc. each with the product name or publisher name return no results. I'll paste my most recent attempt below: select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Coupon" Any help would be appreciated!
-
Does anyone have a clue on a query that would return machines with any Version of SQL Server installed? I'm trying to create a collection of SQL machines. Thanks!!
-
i have a simple name query (System.Name is like "clma06v%) i'm running it agains all systems and the query returns 9 clients, but if i go to all systems and search for clma06v it returns all 24. this seems to be happening on my other queries also. i'm running sccm 2012 r2
-
Hello, i use the following query to get a license report but i´m not quite happy with the result and don´t know how to edit it. declare @ChannelCode varchar(32) select @ChannelCode = 'All' declare @ProductName nvarchar(255) select ProductName, FamilyName, VersionCode, coll.Name, MAX(VersionSequence) as VersionSequence, MAX(ChannelCode) as ChannelCode, count(distinct ilp.ResourceID) as InventoryCount from dbo.vInventoriedLicensedProduct ilp left join v_FullCollectionMembership fcm on ilp.ResourceID = fcm.ResourceID left join v_Collection coll on fcm.CollectionID = coll.CollectionID where (ilp.ProductName = @ProductName OR @ProductName IS NULL) and (@ChannelCode is NULL or @ChannelCode = N'All' OR ilp.ChannelCode = @ChannelCode) group by ProductName, FamilyName, VersionCode, coll.Name The result is the following: The problem with the result is that every collection is listed in the column Name. But i only want to choose some of them. How can i edit the query to get a result with only three collections ? The best result would be the following, but for this i think i have to modify the whole query. Any experts who can help me ?
-
Hey Guys, I am in the need of a query , report or script that will generate a list of all of the applications that use a specific version of Java. I've used report Software 01A and as it does give me all of the versions of software I need to know what applications actually have a dependency on these versions of Java. My fellow SCCM admins out there I know this is a unique request, but if anyone has any ideas I'm all ears. Thanks!
