Checking Client Health for a Large List of Computer Names
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.