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."
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:
name NVARCHAR(30) NULL
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:
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.
- Created on .