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:

DROPTABLE#TMPTST

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

  • Created on .
Copyright © 2021 - The Twin Cities Systems Management User Group