A customer recently had the need to get a count of the number of SQL Server instances that Spotlight on SQL Server is deployed to. There are several ways to do this, depending on requirements and how fancy you'd like to get.

The simplest way is to simply look at the Spotlight interface on the left hand panel for the default "SQL Server" view and note the number of instances that Spotlight is configured to monitor. This is step #1 in the image below (click to enlarge image). We see here that there are 6 instances. Rinse and repeat for additional diagnostic servers that you may have.

The more complex way is to export the data and use Excel to massage it. It's really not that complex. In fact, I'll even provide the formula I used.

In the image above, you can see that I navigated to "Spotlight Today", and then simply right-clicked on the grid and selected Copy to Clipboard. I copied the column but Copy All works just as well (step #2).

Then in Excel, paste in the data. In the image below, I pasted it into cell W140. In cell V141, I used a formula to search the connection name for "SQL Server", and return a 1 if found. Then in cell 160 I used auto-sum to give me the count of the matches for "SQL Server." You can pretty it up further if you like.

And as promised, here's the formula so you don't need to re-type it. Just adjust the cell (replace W141 with the starting cell in your sheet).

=IF(ISNUMBER(SEARCH("SQL Server", W141)), 1, 0)

Related Content