Getting Valid Clients off a Spreadsheet

I had a request last week from someone wanting to know which of his machines had the CM client on them.

"What OU are they in?" I asked.

"They're all over the place. Lots of OUs," he replied.

"OK, so do they have a common name or some identifying feature at all?" I asked.

"Nope. I can send you a spreadsheet."

OMG

What is it with people and spreadsheets? So I start thinking about how I could compare a spreadsheet to my clients in CM. I wasn't about to import that list into a collection; it was like 2000 clients.  So I figured I'd import them to a temp table in SQL and then just join that to the v_r_system_valid view. With a little online searching I came up with this:

CREATETABLE#TMPTST
(

   name NVARCHAR(30) NULL
)

 BULKINSERT#TMPTST

  FROM 'C:\Storage\test.csv'
  WITH

    (
      ROWTERMINATOR = '\n'
    )

Note that the csv file location is in relation to SQL, so in my case, I parked it on the C drive of my central (where SQL is installed; we never install CM off box).

Also note that my ROWTERMINATOR is a carriage return to match a list of machines that are top down in a list. If you have a real CSV file where they are comma seperated, you can just use ROWTERMINATOR = ','instead.

Now you just execute this query to find matching machines and copy the output to email to whoever is asking for it:

SELECT#tmptst.name

FROM   v_r_system_validASsys

       INNER JOIN #TMPTST

       ON sys.netbios_name0 = #tmptst.name

And if you want to be nice, you can drop the table:

If you don't do that, it's erased when you close your session.