VMWARE DATASTORE REPORTS with PowerShell

That is absolutely awesome approach to monitor VMWare Datastores. I had a couple glitches whilst configuring everything from scratch, but those were due to permissions, default settings for PowerCLI, some default restrictions in PowerShell, etc. After getting though all this stuff - I can confirm above implementation works like a charm. Thank you!

Just a few follow-up questions:

* I have noticed that new data will be appended to the old data. Is it possible to actually update all rows instead? Your help would be much appreciated

--  This way I can safely run it every 5-10 minutes and always have latest snapshot of datastore usage in SolarWinds. It will also allow to utilise additional components to monitor trend and statistics for separate datastores - all what will be needed is a new component to check this table every 5-10 minutes and record data in usual SolarWinds fashion. I can even have it on my dashboard then...

Thank you for sharing this with us!

--

Alex

OK, I am answering my own question above myself:

After consulting with our DBA we came to conclusion that better way would be to utilise StoredProcedure within SQL to do the job. Here is what you need to do on top of everything what has been described above by hcy01uk

1. Change powershell script above a little bit, just SQL part only (please note that this will not work until you complete step #2 as PowerShell script is referencing SQL SP):

### Upsert rows $Sqlcmd = $SqlConnection.CreateCommand() foreach ($currentRow in $report){   $rowValues = $currentRow.Split(",")   $Upsert = "exec stpUpsert_DatastoreInfo @p0 = " + $rowValues[0] + ","   $Upsert = $Upsert + "@p1 = '" + $rowValues[1] + "',"   $Upsert = $Upsert + "@p2 = '" + $rowValues[2] + "',"   $Upsert = $Upsert + "@p3 = '" + $rowValues[3] + "',"   $Upsert = $Upsert + "@p4 = '" + $rowValues[4] + "',"   $Upsert = $Upsert + "@p5 = " + $rowValues[5] + ","   $Upsert = $Upsert + "@p6 = " + $rowValues[6] + ","   $Upsert = $Upsert + "@p7 = " + $rowValues[7] + ","   $Upsert = $Upsert + "@p8 = '" + $rowValues[8]+ "'"   # Write-Host $Upsert   $Sqlcmd.CommandText = $Upsert   $Sqlcmd.ExecuteNonQuery() } $SqlConnection.Close() ###########################################

2. Create StoredProcedure in SQL as follows:

USE [YourSolarWindsDatabaseNameHere] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[stpUpsert_DatastoreInfo] -- ALTER PROCEDURE [dbo].[stpUpsert_DatastoreInfo]   @p0 nvarchar(255),   @p1 nvarchar(50),   @p2 nvarchar(255),   @p3 nvarchar(250),   @p4 nvarchar(50),   @p5 float,   @p6 float,   @p7 float,   @p8 datetime AS BEGIN   SET NOCOUNT ON;   if (select COUNT(*) from [dbo].[VIM_DatastoreInfo] where [VcenterName] = @p0 and [DatacenterName] = @p1 and [DatastoreUID] = @p2 and [DatastoreName] = @p3 and [DatastoreType] = @p4)>0   begin   update S set   [CapacityGB] = @p5,   [FreeSpaceGB] = @p6,   [FreeSpacePercentage] = @p7,   [DateTimeStamp] = @p8   from [dbo].[VIM_DatastoreInfo] s   where [VcenterName] = @p0 and [DatacenterName] = @p1 and [DatastoreUID] = @p2 and [DatastoreName] = @p3 and [DatastoreType] = @p4   end   else    begin     insert into [dbo].[VIM_DatastoreInfo] ([VcenterName],[DatacenterName],[DatastoreUID],[DatastoreName],[DatastoreType],[CapacityGB],[FreeSpaceGB],[FreeSpacePercentage],[DateTimeStamp]) values (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)    end END

3. Modify your Scheduled Task to run it every 5 minutes (or whatever interval is more applicable to your environment)

4. Remove "WHERE" statement from the report (your table will only consist of one row per datastore at all times. So, you need to select all rows to see the latest info on all datastores). By the way, I have used web-based reports instead

5. Voilà!

Now you have a reference table in SQL server, which you can use to track datastore usage using separate components in SolarWinds. I am going to set this up now and will post an article how do do this.

Thank you all,

--

Alex

Thank you Alex. It is brilliant. I will update my script when I am back from holiday. Patrick

I have summarised the whole process how to setup live VMWare Datastores monitoring here: Monitoring VMWare Datastores With SolarWinds SAM

Thank You once again for sharing the above scripts

--

Alex

Alex,

I am following your procedure in setting up monitoring VMware Datastores but I am getting error message when I insert the "Upsert Row" section into the powershell script. The error message I get is

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '-'."

The script runs fine without the "Upsert Row" section but I'm looking for the report to show me only latest information rather than all the rows. The SQL Part is being put in after the Insert Row section.

Can you help me out?

Thanks

Hmm.. not entirely sure. I have notice that sometimes copy-paste would screw characters. Try re-typing... I could have uploaded text attachment here, but there is no option to upload a file

OK, I think I know. Do step (2) before step (1) in my previous comment. You do need to have Stored Procedure in SQL beforehand. Let me know how it goes...

Another test would be to run this PowerShell script with "Write-Host $Upsert" uncommented and ensure that every rowValue has a valid value. Non of them should be NULL

Thanks Alex,

Had to create the stored procedure first before the Upsert rows commands would work...

well done thanks