Paul Thomsen at myITforum.com : SQL queries

Learning New ConfigMgr Versions at the Database Level

pthomsen — Sun, 19 Jun 2011 01:52:56 GMT

Those of us that have been in the ConfigMgr (SCCM/SMS) business for a while have had the joy (and challenge) of learning the new versions as they’re developed and then released. There are many approaches to that learning curve, and I’m a fan of all of them. You can learn top-down, meaning you review the marketing and product documentation that highlights the differences and then focus on whatever details are relevant to you. You can experiment with the new version, seeing how it works, what’s changed, and what’s challenging - that’s a middle ground approach to me. And then there’s the bottom-up approach of looking at the technical changes and trying to understand why they were made and how you can use them.

For some reason I actually like the latter approach most of all. By seeing the technical implementation details I can understand what’s really changed. A good example is log files, both client-side and server-side. If a new log is introduced and it has some substance, then that must be an important component, and it must add some important functionality. The high-level details give us the context of that importance, but the low-level details give us the clues to make it work well.

As I’ve mentioned in the past, the first moment at which I fell in love with ConfigMgr was looking at the database. That’s hard to explain, but I still maintain that ConfigMgr is a data-centric system and thus the database is very important. When I’m learning a new version of ConfigMgr, I start by focusing on the database changes.

An easy approach to looking at database changes is to fire up SQL Server Management Studio, connect to your new ConfigMgr database, expand the Views node, and look for changes. With up to 1,000 views that’s problematic, though you can ease the process by ignoring the collection views, inventory views, and ‘secondary views’ (those that seem to be intermediate views). But that all depends on your memory of the previous version’s database schema and what’s important, so it’s easy to miss fun new views.

Another alternative is to query for the differences. Those queries (assuming you’re doing them from the database server with the previous version) would be:

-- lost views
SELECT * from sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%'
and name not in
(SELECT name from [new_version_server.new_version_database].dbo.sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%')
order by name

-- new views
SELECT * from [new_version_server.new_version_database].dbo.sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%'
and name not in
(SELECT name from sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%')
order by name

You’ll notice the queries ignore the collection and hardware inventory history views. Collections are a constant and I’d rather compare hardware inventory changes by looking at the SMS_def.mof changes.

The lost views will likely be a manageable number but the list of new views could be quite numerous, depending on how big the version differences are between the versions you’re comparing. Service packs are likely to introduce few changes, major versions will introduce a lot, and minor versions will be somewhere in between.

If there are a lot of changes then you’ll want to do variations on the above queries, filtering out groups of views as you understand them. Views do often have naming conventions that group related views to each other and so if you understand the significance of the group then you can eliminate them from your list of views to study.

Admittedly, interpreting what a new view adds can be tricky. The name of the views and the columns will give clues. Doing queries against the view when it’s got some data will help further. To a large degree you just have to follow your instincts and focus on those that seem most interesting. And this is just one way to learn the new version, so don’t spend too much time focusing on this technique.

Finally, you might ask what can be seen by using this technique to compare ConfigMgr 2007 with ConfigMgr 2012. My suggestion is that it’s too early to jump to conclusions. ConfigMgr 2012 will change as time goes on (otherwise it would be released already). Exciting view changes are blog posts for future dates.

p.s. I focus on views here, as opposed to tables, because views are what we have always been encouraged to use as ConfigMgr customers. Generally that works, but sometimes there are interesting table additions that don’t get reflected in views. For that reason you may want to also look at tables, but that’s an easy extension of the above concepts.

Checking Client Health for a Large List of Computer Names

pthomsen — Wed, 05 Jan 2011 05:26:24 GMT

One of my pet peeves is reports (of any sort) that are long lists of computers. But the reality is that many people use such reports. I’ve found that people like to take such lists, import them into Excel, and do their own analysis. I must admit that’s cool in that people are analyzing data and getting work done. (My own approach is to build reports that summarize data or to query directly and adjust the queries until I get the information I need).

Once our internal customers have such lists of computers with suspicious client health issues they come to my team and ask us what’s going on with those computers. If the list of computers is reasonably small then you can use a simple “IN” clause such, as:

select count(*) from v_R_System where active0=1 and name0 in ('computer1', 'computer2', 'computer2')

But what if the list is very long, as in thousands of clients or more? In that case you can import the computer names into a temp table and query against that. For example:

CREATE TABLE #temp1( name varchar(30) )

BULK INSERT #temp1

FROM 'C:\<path>\file.txt'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

)

The trick is obviously to do a bulk import from the file with details as to how the file is formatted. A simple trick, once you know it. The only complication is that the file must be available to the SQL Server service on the server itself, as opposed to your console.

Then you can do queries such as:

select count(*) from v_R_System where active0=1 and name0 in (select name from #temp1)

So working with lists of computers is possible, no matter how long they are. The one caution I’ll offer is that if the list was produced significantly long before you do the investigation then be sure to go back to the original source. Otherwise too many of the clients will have changed states (for various reasons) and thus you won’t be able to draw any meaningful conclusions.

p.s. If you have shorter lists of computers and they’re also in long lists (as opposed to being comma delimited with single quotes (why don’t people do that?…)), then you might like to use Notepad2.exe or a similar program to easily adjust the lines. In Notepad2 you can select the block (Ctrl-A) and then do a block “Modify Line” followed by a “Join Lines”. That only take seconds.

ConfigMgr v.Next site settings query

pthomsen — Fri, 12 Nov 2010 04:24:00 GMT

ConfigMgr v.Next has a lot of wonderful improvements, and I look forward to talking about my favorites over time. But often the small ones are very delightful, and I’m pleased to share my thoughts on those as well. One of them is that the ConfigMgr v.Next site settings are entirely stored in the database, and thus can be queried. Historically they’ve been stored in the site control file, and thus required manual or tricky file parsing to read. In ConfigMgr 2007, if not earlier, there was a database representation of those values but that took a lot of parsing so that wasn’t easy either. In v.Next they’re only in the database and are largely already parsed for you.

The following query should make them reasonably easy to read if you’re looking for client-specific settings. There’s about 174 such settings, so that’s a good start. But if you want other settings then you’ll need to do variations on this query to get them (and I hope to cover them in future blog postings).

select ClientComponentName 'Agent', Flags ‘Enabled’, Name 'Property',

case Value1

when 'REG_SZ' then Value2

when 'REG_DWORD' then cast(Value3 as varchar(20))

when '' then cast(Value3 as varchar(20))

else Value1

end 'Value'

from dbo.SC_ClientComponent agents join SC_ClientComponent_Property props on agents.ID=props.ClientComponentID

You’ll see lots of details related to software inventory, hardware inventory, software metering, software updates, etc. Very useful stuff. For example, you can confirm all your sites are consistently configured. You can confirm your predecessor configured things reasonably. Stuff like that.

The trickiest problem you’ll soon notice is that properties like agent schedules are stored in WMI tokens, which mean a lot to WMI but not so much to you and I. I don’t know of a SQL mechanism to translate them, so that’s when I revert to vbscript. The following scriptlet gives you an idea of how to do that. Just substitute the relevant values.

server="<server>"
sitecode=”<sitecode>"
Set loc = CreateObject("WbemScripting.SWbemLocator")
Set WbemServices = loc.ConnectServer(server, "root\sms\site_" & sitecode)

Set clsScheduleMethods = WbemServices.Get("SMS_ScheduleMethods")

Interval = "0001200000100018" 'insert your token here

clsScheduleMethods.ReadFromString Interval, avTokens
For each vToken In avTokens
wscript.echo vToken.GetObjectText_
Next

ConfigMgr queries grouped by operating system family

pthomsen — Thu, 04 Nov 2010 04:41:00 GMT

If you’ve done ConfigMgr queries for awhile you’ve probably done some reports based on operating system. That has meant using the Caption0 column from the v_GS_Operating_System view, but you probably found you had a lot of variations on the various operating system families. For example, Vista Professional, Vista Enterprise, etc. But what if you would rather just categorize at a higher level (by ‘family’) such as XP vs. Vista vs. Win7?

The following query shows how you can do that level of categorization. The trick is to use CASE clauses with LIKE clauses, like so:

declare @grand_total integer

select @grand_total = COUNT(distinct sys.Name0)

from v_R_System sys join v_CH_EvalResults eval on sys.ResourceID=eval.MachineID join v_GS_OPERATING_SYSTEM os on sys.ResourceID=os.ResourceID

where Client0=1 and Obsolete0=0

select @grand_total 'total clients with health results'

select

case when Caption0 like '%Windows 7%' then 'Win7' when Caption0 like '%XP%' then 'XP' when Caption0 like '%Server 2008 R2%' then 'Server 2008 R2' when Caption0 like '%Server% 2008%' then 'Server 2008' when Caption0 like '%Server% 2003%' then 'Server 2003' when Caption0 like '%Vista%' then 'Vista' when Caption0 like '%Hyper-V%' then 'Server 2008 R2' else 'other' end 'OS'

,COUNT(distinct sys.Name0) 'clients with health results'

,COUNT(distinct sys.Name0)*100 / @grand_total '% of all clients'

,SUM(Active0) 'active clients'

,SUM(Active0) * 100 / COUNT(distinct sys.Name0) '% active'

,SUM(case Result when 6 then 0 when 7 then 0 else 1 end) 'unhealthy clients'

,SUM(case Result when 6 then 0 when 7 then 0 else 1 end) * 100.0 / SUM(Active0) '% unhealthy/active'

from v_R_System sys join v_CH_EvalResults eval on sys.ResourceID=eval.MachineID join v_GS_OPERATING_SYSTEM os on sys.ResourceID=os.ResourceID

where Client0=1 and Obsolete0=0

group by

order by COUNT(*) desc

This is actually for ConfigMgr v.Next client health, but the concept is applicable to any such query for SMS or ConfigMgr (SCCM).

p.s. Credit goes to a co-worker, Benjamin Reynolds, for pointing out this option. I’ve done plenty of CASE statements over the years but didn’t realize that LIKE and similar clauses were an option for the sub-clauses.

How long are your SMS 2003 patch scans taking?

pthomsen — Wed, 19 Mar 2008 02:06:00 GMT

Summary: patch scanning is normally a quiet behind-the-scenes activity that computer managers don't have to worry about. But that doesn't mean we shouldn't proactively look for worst-case scenarios.

Those of us that have been in the patch management business a couple of years or more will recall that sometimes patch scanning can be less quiet and behind-the-scenes than it should be. So we know that it's wise to watch patch scanning times. Even if there isn't a widespread issue, maybe there are some corner case scenarios we can identify and improve.

The following SQL script calculates the scan time for SMS 2003 clients:

-- get a sample set of relevant records to work with - for large hierarchies the whole table would be too large
SELECT top 100000 machinename, time, messageID into #temp
FROM v_StatusMessage s3 LEFT OUTER JOIN v_StatMsgAttributes AS att ON s3.RecordID = att.RecordID
WHERE att.AttributeID = 401 AND att.AttributeValue = '<patching advertisement ID>' AND messageID in (10005,10009)
group by machinename, time, messageID

-- put the 10005's (advertisement started) into a seperate table, so the max time select won't be confused with the 10009's max time select
select machinename, time, messageID into #temp5 FROM #temp where messageID=10005
-- same for 10009's (advertisement successfully run, with details returned via status MIF)
select machinename, time, messageID into #temp9 FROM #temp where messageID=10009

--get the most recent records for the 10005's
select t1.machinename, t1.time, t1.messageID into #temp2
from #temp5 t1 join #temp5 t2 on t1.machinename=t2.machinename
group by t1.machinename, t1.time, t1.messageID
having t1.time=max(t2.time) order by t1.machinename
--same for the 10009's, and put them into the same temp table
insert into #temp2 (machinename,time,messageID)
select t1.machinename, t1.time, t1.messageID
from #temp9 t1 join #temp9 t2 on t1.machinename=t2.machinename
group by t1.machinename, t1.time, t1.messageID
having t1.time=max(t2.time) order by t1.machinename

--build a new temp table with just the times, so that the datediff calculation is easy
SELECT machinename,
( select time from #temp2 s1 where messageID=10005 and s1.machinename=s3.machinename) 'Start',
( select time from #temp2 s2 where messageID=10009 and s2.machinename=s3.machinename ) 'End'
into #temp3 FROM #temp2 s3 order by machinename

--look at the results, without the exceptional data (with is a smallish percentage, like 15%)
select datediff(s, [start], [end]) from #temp3
where [start] is not null and [end] is not null
and datediff(s, [start], [end]) >0 and datediff(s, [start], [end]) <2000
order by datediff(s, [start], [end])

--the all important average
select avg(datediff(s, [start], [end]) ) from #temp3
where [start] is not null and [end] is not null
and datediff(s, [start], [end]) >0 and datediff(s, [start], [end]) <2000

This script is also a good example of the usefulness of temporary tables. I don't pretend to be a SQL guru, but I like techniques that allow non-guru's to accomplish complex tasks using SQL alone.

As usual, I can't guarantee that this is the only or best way to accomplish this goal. But it has worked well for me and seems to return accurate results. Now I have to come up with a ConfigMgr equivalent...

Saving client health (or other) data to a SQL Server

pthomsen — Fri, 11 Jan 2008 00:53:00 GMT

Summary: most computer management specialists write and run SQL queries, but not many save data for later use. Yet saved data can allow you to do historic comparisons, trending, complex dataset queries, and other powerful analysis.

I routinely save client health data and then run queries against that saved data to produce graphs of client health trends. An example is my blog posting on client activity history. I often show those reports to people, in public and private forums, and a common question is how to produce such reports. I'm pleased to share the general process with you, in five parts: the queries, saving the data, producing HTML reports from script (automatically), producing graphs from script, and e-mailing the reports to the interested parties from script (we won't go into every detail, but will cover the key points that might not be obvious). Today we'll focus on saving the data.

WARNING: Do NOT write data to your SMS or ConfigMgr databases. Instead, create a seperate database (anywhere, even on your console machine). Anytime you directly write to the SMS or ConfigMgr databases you run various serious risks of corrupting production data, losing data, creating future conflicts that will cause upgrades to fail, or causing various other unpredictable problems that no one can help you correct (other than to rebuild the site from scratch). Even backups won't help, because the bad data could exist for a long time before the problem is detected, and thus be stored in all your backups. You might even want to use a seperate computer, just to minimize the risks as much as possible (the risks including performance or capacity problems, or accidentally using the wrong database). Installing a seperate instance of SQL Server somewhere else is quite easy, as is creating a database (see the online help if you don't find it intuitive). It's true that setting up a serious large-scale production SQL Server is a task that must be left to a professional DBA, but we don't need anything near that serious.

Given that you have a database, it's actually quite easy to save data using SQL Server. The first thing you'll need is a table, and SQL Server's Enterprise Manager makes that easy. Navigate to the Tables node, right click, and select New Table... It will prompt you for column name, datatype, and whether NULLs are accepted ('yes' is a reasonable answer in most informal cases like ours). You don't have to worry about adding all the right columns now because you can come back and add more later if you like (by right clicking the table name and selecting the Design menu option). You might like to use some kind of naming convention for your columns, to have some consistency that will make your code more readable later, but that's a minor point. When you close the window, SQL Server will ask for a name for the table. Anything will do, but a naming convention can help there too.

Now that you have a table, you'll want to put data into it. You could just "Open Table" the table in SQL Server Enterprise Manager and start typing. That works but it's not automatic at all, so there's rarely any benefit to it. Better yet is to "insert" data. If you have reason to change it (maybe correct it), you can "update" the data. Or if you end up with some bad data that you don't need, then you can "delete" it. Inserting is quite safe, but updates and deletes can easily affect data that you didn't intend to affect (maybe all of it), so you have to be more careful with them. Backups are good, of course. Or experiment on a test table until you're confident you understand the syntax (online help, a book, or an Internet search engine will soon get you up to speed on any syntax subtleties you need). Aren't you glad you're not doing this in the SMS database? ;-)

I always save my data using a bit of vbscript that is a varient on the following example. I've greatly reduced the number of columns in the example, for readability, but otherwise it's a complete code sample. (Well, you'll have to connect to the database, and come up with the data values somehow, but that's exactly the same as any script that queries for data (using SQL "select" statements) - there's lots of examples of that on the Internet).

If you're not inclined to use vbscript (or similar scripting), you can still save data by using the SQL statements directly. You could even write up the relevant SQL statements (a SQL script) and run the script automatically every day by creating a SQL job (see the online help for that, but it's fairly straightforward).

To save the data as a new record, you would use a statement like: INSERT INTO ClientHealth_CHT VALUES ('central', 'CEN', '1/1/2008', 3282, 3875, 4257)

To update (change) the record, you would use a statement like: UPDATE ClientHealth_CHT SET polled_1day=3175, polled_7day=3986, client_count=4256 WHERE sitecode='CEN' AND date_stored='1/1/2008' AND environment='central'

For those who are into vbscript (or might get into it), here's my subroutine for saving data. You'll have to change table and column names, and add columns, as needed for your table.

todays_date = left(now, instr(1,now," "))
Sub Save_Data( hierarchy, sitecode, polled_1day, polled_7day, total )

'check if the record already has the numbers for this day
exists=vbFalse
SQL="Select * FROM ClientHealth_CHT WHERE sitecode='" & sitecode & "' AND date_stored='" & CDate(todays_date) & "' AND environment='" & Left(hierarchy,30) & "'"
objRS_Store.Source = SQL
objRS_Store.Open
if objRS_Store.RecordCount>0 then exists=vbTrue
objRS_Store.Close

if NOT exists then
   data = "'" & left(hierarchy,30) &"','"& sitecode &"',"& polled_1day &","& polled_7day &","& CDate(todays_date) &"',"& total
   SQL="INSERT INTO ClientHealth_CHT VALUES (" & data & ")"
    objRS_Store.Source = SQL
    objRS_Store.Open
    wscript.echo "saved the data for " & hierarchy & " " & sitecode
Else
   SQL="UPDATE ClientHealth_CHT SET polled_1day=" & polled_1day & ", polled_7day=" & polled_7day & ", client_count=" & total & " WHERE sitecode='" & sitecode & "' AND date_stored='" & CDate(todays_date) & "' AND environment='" & left(hierarchy,30) & "'"
    objRS_Store.Source = SQL
    objRS_Store.Open
    wscript.echo "updated the data for " & hierarchy & " " & sitecode
End If

End Sub

Quick advertisement status

pthomsen — Tue, 07 Aug 2007 03:10:00 GMT

Summary: a few simple queries can give you a quick update on your latest advertisement. You can then react as needed or get on to more important tasks...(read more)

Client Activity History - the original queries

pthomsen — Mon, 06 Aug 2007 05:12:00 GMT

Summary: starting at the basics, we need to know how much activity we're seeing from our computer management clients, and thus how much we're likely...(read more)

The joys of computer management data analysis

pthomsen — Tue, 08 May 2007 03:48:00 GMT

Summary: why do we enjoy computer management? One of my biggest answers is the joy of data analysis. Computer management involves a ton of diverse data...(read more)

How Microsoft IT Does Patch Management

pthomsen — Wed, 02 May 2007 20:09:00 GMT

Summary: one of the joys of working at Microsoft IT is that we get to talk to customers frequently about what we do (and yes, I am serious when I say it's...(read more)

Paul's TechSexy 2: how much time are your collections taking to evaluate?

pthomsen — Tue, 01 May 2007 18:39:00 GMT

Summary: computer management is bound to be complex, as I often say, and so SMS is a very busy system. Many subsystems, at multiple levels, process a lot...(read more)

Paul's TechSexy 1: great client deployment monitoring, thanks to v_ClientDeploymentState

pthomsen — Tue, 01 May 2007 01:33:00 GMT

Summary: client deployments (new installs or upgrades) are a critical part of the life of any SMS administrator. Whether you're rolling out your first...(read more)

Favorite SQL tricks: the distribution of recent activity

pthomsen — Tue, 10 Apr 2007 02:58:00 GMT

Summary: most questions that I get that can be answered with SQL queries are 'now' questions - how many Vista clients do we have at this point...(read more)

Favorite SQL tricks: Analyzing SQL Server messages with vbScript

pthomsen — Wed, 21 Mar 2007 02:36:00 GMT

Summary: combining SQL Server query results and vbscript is a powerful tool, and well documented. But what if you want SQL Server error messages returned...(read more)

Counting clients - the SQL details

pthomsen — Thu, 01 Feb 2007 04:27:00 GMT

Summary: there are plenty of ways to count SMS clients - which is the best way? I actually use SQL Server Management Studio more than I use the SMS Administrator...(read more)