Audit assistance of large # of machines

[jagablack]

Hi Doug, hope you are well my friend.

I am tasked with a major audit of several domains to true up our Microsoft licensing.

Mainly this is around OS Version and SQL Server version.

Do you have any recommendations to do this?

I am using the “Get information” menu item to get OS, but the SQL so far have had little success. Also in relation to BatchPatch the performance has been not good when dealing with a lot of machines at once. Right now I have 1671 machines in the view and trying query them is taking a lot of time/causing window to not respond.

Sorry for the fragmented thoughts, am under the gun on this and looking for advice/assistance. Thank you again.

Jeremy

[jagablack]

Ah.. speaking of fragmented thoughts, I forgot that also we are looking to determine core count/physical socket count, as a lot of the Microsoft licensing is based on this. I am speaking of the true core count and not HyperThreading enabled.

Thanks.

[doug]

Hey Jeremy – We actually have on the list to add some basic SQL query capabilities, but it hasn’t been added yet. In the meantime, this will work for you. Create a “Local Command” in BatchPatch as follows:

sqlcmd -S $computer -d master -Q "select @@version" -U username -P password

---

With regard to CPU socket/core count, this might work for you. Off the top of my head this would really need to be done in a loop in order to do the multiplication for you, so you could write a VBScript or Powershell script to do that for you, but since I don’t have time right now to pull together a proper script, you can use the following 2 commands to give you the info you need. But you’d have to manually multiply the NumberOfProcessors by the NumberOfCores to get the total physical core count in the machine. You can eliminate the NumberOfLogicalProcessors from the command below, but if you leave it in it will essentially tell you if HyperThreading is enabled or disabled for a machine. If HyperThreading is enabled, then you’ll see that the NumberOfLogicalProcessors will be double the NumberOfCores. If HyperThreading is NOT enabled, then the NumberOfLogicalProcessors will be equal to the NumberOfCores. It might make most sense to create 2 rows per server, so that you can get the results for both of these queries, and then you can export to HTML, copy and paste the grid to Excel, or save to a .bps file and then import the .bps file as .xml into Excel:

CPU Socket Count:

WMIC PATH Win32_ComputerSystem GET NumberOfProcessors

CPU Core and Logical Processor Count:

WMIC PATH Win32_Processor GET Name,NumberOfCores,NumberOfLogicalProcessors

---

With regard to he window not responding, I’m not sure exactly what to make of it. Depends on how it’s not responding. If there is a lot of data in each cell, that will slow down the grid’s click/scroll performance (we have a fix for this coming in the next build). If that’s what you’re seeing, then your best bet is to close the columns that contain the large amounts of data or clear their contents. This could be the “All Messages” column if you’ve run many commands, or if you have the Remote Agent Log column open, that one typically gets pretty big, so closing that would help.

If the lack of responsiveness is not related to clicking and scrolling around in the grid and is actually just waiting for queries to return results, then you probably should try running less at once. I’ve never tried to query 1671 machines at once, so I’m not sure what to expect. However, I would expect that a couple hundred at a time should perform fine. If you really get stuck you could also launch a number of instances of the BP.exe and put a couple hundred in each instance. Again, it really depends on the nature of the slow-down.

I hope this helps.

-Doug

[doug]

Actually – here you go. Here’s a VBScript that you can use to retrieve the CPU info in one shot without needing to manually multiply anything:

http://batchpatch.com/downloads/CPUCoreCount.vbs

In BatchPatch use “Local Command” with the following syntax:

cscript C:scriptsFolderCPUCoreCount.vbs $computer

[jagablack]

awesome Doug, above and beyond as usual. I am definitely going to try this today. Many thanks my friend.

[doug]

Glad to be able to help, Jeremy. Let me know how it works out for you.

-Doug

[jagablack]

The core/logical count script works great. The SQL one I having issue with because I don’t have rights to the databases… however I do have domain admin rights to the machines. I tried a few vbscripts to gather SQL version but it is difficult to determine Enterprise/Standard version etc… so far anyway.

[jagablack]

I get a bunch of:

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

[doug]

Jeremy – Domain admin permissions is not sufficient to have permissions to the SQL server. I think in SQL 2000/2005 it *was* sufficient but my recollection is that starting with SQL 2008 things changed and unless the domain admins group has been explicitly added to the SQL server logins, you wouldn’t automatically have permissions. That said, it sounds like the first step will be to get yourself setup with an account that has permission to the SQL servers. Once you have that, getting the version information shouldn’t be a problem. I would then just use SQLCMD at the command line (not in BatchPatch) to test that the permissions are working properly, and then once you have it working with 1 server you can duplicate across all the servers and use BatchPatch to grab the version info. Am I missing something? Do you have a separate team who manages your SQL machines? Maybe you’ll need to work with them to give you the access you need?

I guess if you simply are not able to get access to the SQL servers, then your boss shouldn’t be tasking you with getting a list of SQL versions, no? 🙂 One other option, I suppose, would be to write a script to get the file properties on the sql exe file as described here, which wouldn’t require access to the SQL servers and would only require access to Windows: http://www.mssqltips.com/sqlservertip/1140/how-to-tell-what-sql-server-version-you-are-running/

-Doug

[jagablack]

All good points Doug. Jeesh, Microsoft should do the smart thing and just have a “version” key in the registry that says the full version. They seem to vary with every implementation and makes things very difficult. Cannot say I am surprised considering the source.

[doug]

Do you have a SQL admin team who has access to all the SQL servers? A good db administrator should be able to collect all his/her SQL instance versions, using the “select @@version” in under 5 minutes. S/he wouldn’t even need to write a script because SQL management studio allows you to query multiple SQL servers at one time with just a single query. So maybe you can just ask the SQL admin team to give you this info? Would save you some time so that you don’t have to script it out. Just a thought.

Good luck!

-Doug

[jagablack]

Hey Doug, is there any way to save output from two or more executed commands? For example, I need to have a column that shows physical or virtual machine status (queried using local script) and another column that shows the core count (executed via script). However, even when I store them in spots 1 and 2, when I execute the 2nd one, it overwrites the “Local Command Output Log” column. Just wondering if there was a way to do this without having to manually manage the data using excel. Thanks again and sorry for all the questions as I try to work through this effort.

[doug]

No worries. Unfortunately there is only one output column. Your best bet is to probably create two rows per server. Sorry about that.

-Doug

[Author]

Posts