Number2 Blog - myITforum

UPDATE - ConfigMgr 2007 - Dynamic SQL to show missing hardware inventory

2009-03-13T18:05:00Z

UPDATE: This is an update to yesterday's post which used dynamic SQL to show missing hardware inventory in ConfigMgr. I noticed that we had like 95+ classes, but many of them had NO records for any clients whatsoever. To me, that indicates perhaps a class that doesn't have anything turned on for it and is thus a waste of space to have in the query. So I quickly wrote a little more to first find only those classes which have at least one row and only use those classes in the list of hardware...(read more)

M@d Skillz - ConfigMgr 2007 - Dynamic SQL to show missing hardware inventory

2009-03-13T00:23:00Z

My fellow ConfigMgr MVP Torsten Meringer hit me up with an interesting question this morning. He wanted to identify those machines that are missing hardware inventory. He mentioned the v_GroupMap view that shows you all of the hardware inventory classes (which I shamefully admit I didn't know about until he pointed it out) and manually wrote a query to show which classes are missing from which machines. This is a pretty good idea, actually...I've done the same thing, only by writing a few...(read more)

SMS/ConfigMgr - Create status MIFs using batch script with no dependencies

2009-01-13T20:39:00Z

In a past position, I've tried to use a utility from the Back Office Resource Kit called ismif32.exe to create status MIFs for those programs that aren't MSI-based or don't have the ability to create status mifs. The problem I've had is that it just didn't always work. Usually there was some error about a DLL missing or some entry point not found or some such nonsense and I didn't have the luxury of changing anything on the clients to get it to work. So, I did what any self...(read more)

Digital Decor - Digital Pocket Viewer - How to get it to work (DPCI: 056-13-0705) (ASIN: B001GN416O) (Catalog #: 11018416)

2008-12-27T17:39:00Z

OVERVIEW So, it's Christmas time and you've either just received or just given the perfect $20 gift, a digital pocket viewer like the one from Digital Decor that you can find at Target DPCI: 056-13-0705 ASIN: B001GN416O Catalog #: 11018416 Just so you know we're talking about the same thing, here are some of the specs and pictures: Style and color assortment may vary Digital Photo Frame 1MB Memory Storage Capacity Supports JPEG, BMP Resolution: 128x128 Operating Requirements: Windows...(read more)

Calculate a client's subnet mask when defining subnet boundaries (using BITWISE AND "&" in SQL)

2008-10-29T19:36:00Z

OVERVIEW I was recently pointed to Jeff Gilbert's blog where he has an article on determining a client's subnet mask and using them in subnet boundaries. http://myitforum.com/cs2/blogs/jgilbert/archive/2007/08/21/how-to-determine-a-client-s-subnet-mask-when-defining-ip-subnet-boundaries.aspx Now, in that article is a VBScript script to calculate the subnet. It got me thinking...I usually just lay down some M@d SQL to do the same thing. Perhaps someone would want to see that. So here it is...(read more)

WQL - Using the bracket wildcard to match a range of version numbers (MNSCUG)

2008-10-17T06:23:00Z

At the Minneapolis area System Center User Group meeting last night, I mentioned the notion of using LIKE and the bracket wildcard [] to match a range of version numbers (which incidentally works for versions, IP Addresses, IP Subnets, etc.) instead of using greater than or less than because of the way WQL/SQL does a dictionary sort, not a numerical sort. I mentioned that I had written an article describing how to do that, but that it was embedded in a broader article on something else. In case anyone's...(read more)

SMS 2003 - SQL to compare software on two systems

2008-10-16T03:34:00Z

OVERVIEW You want to look at the difference in software between 2 computers, but you either don't have Desired Configuration Management (DCM) implemented, or you'd rather just compare the software without creating a DCM rule. The answer is actually fairly simple, in this article I'll lay out how to do just that. I'll use the software in Add/Remove Programs (v_Add_Remove_Programs) and software inventory (v_GS_SoftwareFile) as examples. These principles can be used to compare pretty...(read more)

SMS 2003 - What do those C054 columns mean in reports? (Localized Name Lookup)

2008-10-02T16:39:00Z

If you look at certain pre-canned reports that come with SMS 2003, you'll notice some of them have wierd column names like C054, but show up in the report output as "Operating System". The reason behind this is that SMS was designed as a multi-language application that should be able to easily change its output based on the different languages of the users. One of the first things you're going to want to know is what are all those C054, C056, etc. columns (how high does it go) and...(read more)

SMS 2003 - Query to pull logged on user records from all sources

2008-08-28T19:31:00Z

I was trolling the forums lately on MyITForm.com and was reading some conversations about logged on user records. It made me think of all the sources where you could get that information. Off the top of my head there's V_GS_COMPUTER_SYSTEM.username0 V_HS_COMPUTER_SYSTEM.username0 V_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 (SMS 2003 SP3 Asset Intelligence stuff) V_HS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 V_R_SYSTEM.user_domain0\user_name0 If you unioned all of the records from those sources...(read more)

SQL/WQL Tip - How to escape special SQL/WQL wildcard characters like % _

2008-08-27T07:31:00Z

THE PROBLEM You've written a SQL query or a WQL query (like an SMS/CM collection query) and you need to look for a string that has a percent % or underscore _ in it. The problem is, those characters are special wildcard characters to SQL/WQL. So how do you "escape" those characters and write a query to look for all records that have a percent sign or an underscore in them? THE SOLUTIONS SOLUTION 1 (for SQL and WQL) Use square brackets [] to signify you're looking for that specific...(read more)

SMS/SCCM vs Promisec Spectator - Part 2 of 2 (The rebuttal)

2008-08-22T15:12:00Z

If you read my previous post on the blood-boiling comparison between SMS/SCCM & Promisec Spectator, you've likely developed all kinds of responses in your head about how they're so wrong on SMS/SCCM. I couldn't believe it so I just had to respond. THE REBUTTAL I'd like to address their points to make sure we're being evaluated in the proper light, so the tone of this is simply an honest rebuttal of their points to them. This is delivered with no disrespect or malice, but with...(read more)

SMS/SCCM vs Promisec Spectator - Part 1 of 2 (Their Comparison)

2008-08-22T14:55:00Z

If you're an SMS or SCCM guy and you want to get your blood boiling, just look at this writeup the people at Promisec created that compares SMS to their Promisec Spectator. I mean no disrespect to the people at Promisec, but man...this thing couldn't be more wrong. Either they don't really know what SMS does, or they're comparing it to an alpha release of SMS 1.0, or they've had a temporary lapse in ethics in an effort to get a sale. http://www.myitforum.com/absolutefs/?f=17&fid...(read more)

SQL 2005 Security - Revoke EXECUTE rights for PUBLIC on (potentially) unsafe extended stored procedures

2008-08-16T06:15:00Z

Where I work, we have an amazing crew of security architects and analysts who have decades of experience in all things security. Sure, at times they may seem paranoid, but that's just because they've seen bad things that you and I couldn't even dream up. Recently I've been going through our security baseline to verify we're being as secure as possible with SQL (on the servers our team supports) and I'd like to share some code that will help identify any extended stored procedures...(read more)

SMS/SCCM & Batch Files - Important notes on running .BAT files from UNC in SMS/SCCM

2008-08-15T21:31:00Z

THE PROBLEM When you run a .BAT file from a UNC path, the first thing you might notice is the warning about "UNC paths are not supported. Defaulting to the Windows Directory" All this really means is that the current working folder is your Windows folder, and not the UNC path to where the script is sitting. The problem with this is that any commands inside your .bat file that try to call or reference a file that you know is in the same UNC path as the .bat file will come up empty because...(read more)

Troubleshooting 301 - Finding a needle (bad program) in a haystack (list of 150 programs) in 7 tries.

2008-07-16T20:06:00Z

THE PROBLEM Stay with me on this one, I think it's a concept that every techie should understand and use at some point in their careers. The problem goes like this: There's one program somewhere on your machine that's conflicting with another and causing CPU spikes and bringing the computer to a standstill, but you don't know which one. The problem is, you have 150 items in your Add/Remove Programs and you've been tasked with finding out which one is the problem. Now, many people...(read more)

SMS Web Report (SQL 2005+) - Show SMS program execution chaining hierarchy visually using CTE (Common Table Expressions) in SQL 2005+

2008-06-20T00:10:00Z

In response to a request on the forums, here's a SQL query that uses common table expressions (CTE) to visually return the program execution chain hierarchy. (something like this) 00000000 - ProgramA 00000001 - ProgramB 0000004 - ProgramE 00000002 - ProgramC 00000003 - ProgramD Here you can see that ProgramE is dependent upon ProgramB, which is dependent upon Program A. ProgramC&D have no dependents. NOTE: This only works in SQL 2005 or higher since CTE was introduced with 2005. WITH ProgramHierarchy...(read more)

SMS 2003 - Verify WMI Stability Backport on Windows XP/Server 2003 with this report SQL (deploy to them with this collection query WQL)

2008-06-05T01:05:00Z

BACKGROUND (click HERE to skip to the summary) I've been looking into our client health a lot deeper recently and something that I keep coming back to is how fragile WMI seems. After some great conversations at MMS with some really bright folks, I started really trying to fix those unhealthy clients. I got to reading a blog that pointed to a blog what pointed to some Microsoft articles which gave me some good information. I'd like to share some of that information because it showed us that...(read more)

SCCM 2007 - SCCM Permissions Matrix

2008-05-30T17:38:00Z

This SQL query pulls all SCCM permissions for each user and displays them in a matrix. Put this into an SCCM Web report. This should work for SQL 2000, SQL 2005 and higher. SELECT UCP.UserName, SO.ObjectName, MAX(CASE UCP.PermissionName WHEN 'Administer' THEN 'X' END) as [Administer], MAX(CASE UCP.PermissionName WHEN 'Advertise' THEN 'X' END) as [Advertise], MAX(CASE UCP.PermissionName WHEN 'Create' THEN 'X' END) as [Create], MAX(CASE UCP.PermissionName...(read more)

SMS 2003 - SMS Permissions Matrix

2008-05-30T17:32:00Z

This SQL query pulls all SMS permissions for each user and displays them in a matrix. Put this into an SMS Web report. This should work for SQL 2000, SQL 2005 and higher. SELECT UCP.UserName, SO.ObjectName, MAX(CASE UCP.PermissionName WHEN 'Administer' THEN 'X' END) as [Administer], MAX(CASE UCP.PermissionName WHEN 'Advertise' THEN 'X' END) as [Advertise], MAX(CASE UCP.PermissionName WHEN 'Create' THEN 'X' END) as [Create], MAX(CASE UCP.PermissionName...(read more)

M@d Skillz - SMS Status Messages - ASP to show all MessageIDs and their meanings

2008-05-21T05:23:00Z

THE PROBLEM So I'm digging through the SMS status message views (v_StatusMessage, v_StatMsgAttributes, v_StatMsgInsStrings, v_StatMsgModuleNames) to find some pretty specific data to do my job, but I was having trouble understanding what all the messageIDs mean. First, I did what any good techie does and did a Google search to see if there's a list of all MessageIDs and their meanings. You come across what seems like the perfect Excel spreadsheet from Microsoft showing exactly what you need...(read more)

M@d Skillz - Use data outside the SMS DB in your SMS Web Reports

2008-04-04T05:32:00Z

OVERVIEW (note: this is written with SMS 2003 & SQL 2005 in mind because that's the environment I'm in right now, but I believe if the equivalent work was done in SCCM or in SQL 2000, the outcome would be the same...just a hunch) If you're like any normal SMS administrator, you've played around with SMS web reports and started getting some pretty useful data out of your SMS database. The addition of web reports to SMS was quite a great addition because it really made rolling your...(read more)

VBScript - (UPDATED) Script to export SMS Web report results to an Excel spreadsheet

2008-04-02T07:06:00Z

BACKGROUND In a previous post I created a VBScript in response to a discussion in the forums where somebody wanted to automatically run a report once per month and was looking for ideas. I wrote a first draft of the script and it was OK. Go check out that post first if you haven't already. I've since updated it to handle extended characters like ® and ™ by using ADODB.Stream...but as long as I had the script open, I decided now was a good time to add XLS output (not just CSV) so we can have...(read more)

SMS 2003 Performance - The performance benefits of an x64 DP in a busy SMS 2003 hierarchy

2008-03-27T14:45:00Z

QUICK BACKGROUND Although x64 is officially unsupported in SMS 2003 for any site components, the reality is that you CAN put the Distribution Point role on an x64 server in SMS 2003. Now, I'm sure like us some of you have tried but get all kinds of 500 level errors out of IIS. The problem is that the SMSFILEISAPI filter for SMS is 32-bit but ASP.NET is in 64-bit mode. When the DP role is installed by default, that ISAPI filter seems to get put into the SYSTEM32\INETSRV folder which on an x64...(read more)

Perfmon - Common counters to find bottlenecks on a server with SQL (SMS/SCCM/SCOM)

2008-03-26T18:10:00Z

I'm seriously busy right now at my day job so I don't have time to get cr@zy with the det@ilz, but I would like to quickly post the Perfmon counters I use if I want to determine if a server has a processor, memory, network or disk bottleneck. Performance monitoring is kindof a voodoo arts kinda thing so everyone has their own special pixie dust counter that they use to look for problems. Because of this you may find that I'm missing a counter you would be using or I've got some counters...(read more)

WinBatch - Script to add boundaries to SMS

2008-03-17T14:56:00Z

This is a VERY old script I wrote in 2000 for SMS 2.0, that I've used for SMS 2003 too. I have no idea how well it works anymore, but I know it worked for SMS 2.0 and SMS 2003 RTM. The purpose of it is to add a range of boundaries to SMS without having to go through them by hand one-by-one. Back in 2000, I was heavy into WinBatch and was teaching a WinBatch class so it was easy to whip something up that walked through the SMS console clicking buttons on behalf of the user. I'm sure I'd...(read more)

VBScript - Script to export SMS Web report results to a CSV file

2008-03-10T18:01:00Z

THE PROBLEM You want to automatically run a set of SMS Web reports, but you don't have something like SQL Server Reporting Services installed (which would give you the ability to have people subscribe to reports and have them delivered a bunch of ways). So, you need the ability to run SMS Web reports unattended and save the results somewhere. THE SOLUTION (Get the full code here--> ExportSMSReport.txt <-- rename to .VBS) (UPDATED 3/24/2008 Made OpenTextFile support unicode so special characters...(read more)

Windows Shell Script - Better way to run CMD as System

2008-02-15T17:00:00Z

Most people are familiar with using AT to open a command prompt as SYSTEM (LOCAL SYSTEM) at 11:05 /interactive cmd.exe This will open a command prompt as system (or actually, as whatever account the task scheduler service is running under) This works OK but trying to script it is a complicated mess, plus you have to wait a minute or so for the window to pop up...and if the task scheduler service is running as another user then you're not really getting your prompt to run as SYSTEM. So, a better...(read more)

SMS 2003 Database - Where can I find things?

2008-01-23T05:57:00Z

Here is a list of common items that people want to query for and some locations they're found in the SMS database I'm basing this on SMS 2003, we have SP3+ Depending on your setup, you may not have some of these views in your environment or they may be empty. You may also find other places where these items exist. This is not indended to be an exhaustive list, just a quick reference of items that most people want to see in a web report at some point and the places you can find these items...(read more)

M@d Skillz - SMS Web Reports - Passing a delimited list to SMS Web Reports like a table - (also convert list to rows in SQL)

2008-01-16T11:25:00Z

BACKGROUND Pass multiple items into a single parameter? With SMS Web reports, there's a lot of power in being able to pass parameters to your report query and have it substitute those values into the SQL code. It really saves you a lot of time not having to write your own front end for your SQL queries. But the way the SMS Web Report process was designed seems to be focused around passing parameters that are single strings, not multiple lines of text or delimited lists. If you pass in a really...(read more)

SQL Function - Convert Bytes to KB, MB, GB, TB, PB or EB and format the output to human readable format

2008-01-11T07:14:00Z

When you query a bunch of SMS/SCCM data that has it's output in bytes or KB or MB, often times you want that expressed in a more human readable format. This function takes 2 parameters: a number, and the unit of measurement that number is in. Examples: Number : 1024 UOM : Bytes Results : 1.00 KB Number : 0.5 UOM : GB Results : 512.00 MB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Number2 (John Nelson) - http://number2blog.com -...(read more)