PowerShell- Monitoring Multiple Services On Multiple Servers Using WMI Class -Win32_Service

The requirement is to check only those services where startup mode set to Auto and services that stopped. In my previous post have used Get-Service cmdlet which do not bind any such information hence I’m querying Win32_Service. This class has StartMode and State attributes.

Function Get-ServiceStatusReport  
$script:list = $ComputerList   
$ServiceFileName= "c:\ServiceFileName.htm"  
New-Item -ItemType file $ServiceFilename -Force  
# Function to write the HTML Header to the file  
Function writeHtmlHeader  
$date = ( get-date ).ToString('yyyy/MM/dd')  
Add-Content $fileName "<html>"  
Add-Content $fileName "<head>"  
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"  
Add-Content $fileName '<title>Service Status Report </title>'  
add-content $fileName '<STYLE TYPE="text/css">'  
add-content $fileName  "<!--"  
add-content $fileName  "td {"  
add-content $fileName  "font-family: Tahoma;"  
add-content $fileName  "font-size: 11px;"  
add-content $fileName  "border-top: 1px solid #999999;"  
add-content $fileName  "border-right: 1px solid #999999;"  
add-content $fileName  "border-bottom: 1px solid #999999;"  
add-content $fileName  "border-left: 1px solid #999999;"  
add-content $fileName  "padding-top: 0px;"  
add-content $fileName  "padding-right: 0px;"  
add-content $fileName  "padding-bottom: 0px;"  
add-content $fileName  "padding-left: 0px;"  
add-content $fileName  "}"  
add-content $fileName  "body {"  
add-content $fileName  "margin-left: 5px;"  
add-content $fileName  "margin-top: 5px;"  
add-content $fileName  "margin-right: 0px;"  
add-content $fileName  "margin-bottom: 10px;"  
add-content $fileName  ""  
add-content $fileName  "table {"  
add-content $fileName  "border: thin solid #000000;"  
add-content $fileName  "}"  
add-content $fileName  "-->"  
add-content $fileName  "</style>"  
Add-Content $fileName "</head>"  
Add-Content $fileName "<body>"  
add-content $fileName  "<table width='100%'>"  
add-content $fileName  "<tr bgcolor='#CCCCCC'>"  
add-content $fileName  "<td colspan='4' height='25' align='center'>"  
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>Service Stauts Report - $date</strong></font>"  
add-content $fileName  "</td>"  
add-content $fileName  "</tr>"  
add-content $fileName  "</table>"  
# Function to write the HTML Header to the file  
Function writeTableHeader  
Add-Content $fileName "<tr bgcolor=#CCCCCC>"  
Add-Content $fileName "<td width='10%' align='center'>ServerName</td>"  
Add-Content $fileName "<td width='50%' align='center'>Service Name</td>"  
Add-Content $fileName "<td width='10%' align='center'>status</td>"  
Add-Content $fileName "</tr>"  
Function writeHtmlFooter  
Add-Content $fileName "</body>"  
Add-Content $fileName "</html>"  
Function writeDiskInfo  
if( $status -eq "Stopped")  
 Add-Content $fileName "<tr>"  
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$servername</td>"  
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$name</td>"  
 Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$Status</td>"  
 Add-Content $fileName "</tr>"  
Add-Content $fileName "<tr>"  
 Add-Content $fileName "<td >$servername</td>"  
 Add-Content $fileName "<td >$name</td>"  
 Add-Content $fileName "<td >$Status</td>"  
Add-Content $fileName "</tr>"  
writeHtmlHeader $ServiceFileName  
 Add-Content $ServiceFileName "<table width='100%'><tbody>"  
 Add-Content $ServiceFileName "<tr bgcolor='#CCCCCC'>"  
 Add-Content $ServiceFileName "<td width='100%' align='center' colSpan=3><font face='tahoma' color='#003399' size='2'><strong> Service Details</strong></font></td>"  
 Add-Content $ServiceFileName "</tr>"  
 writeTableHeader $ServiceFileName  
#Change value of the following parameter as needed  
#List of programs to exclude  
#$InlcudeArray = $inlcudeService  
Foreach($ServerName in (Get-Content $script:list))  
$service = Get-WmiObject Win32_Service -ComputerName $servername |Where-Object { $_.StartMode -eq 'Auto' -and $_.state -eq 'Stopped'} 
if ($Service -ne $NULL)  
foreach ($item in $service)  
 Foreach($include in $includeService)   
 write-host $inlcude                                      
 if(($item.name).Contains($include) -eq $TRUE)  
    Write-Host  $servername $item.name $item.Status   
    writeDiskInfo $ServiceFileName $servername $item.name $item.Status   
Add-Content $ServiceFileName "</table>"   
writeHtmlFooter $ServiceFileName  
function Validate-IsEmail ([string]$Email)  
                return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"  
Function sendEmail    
$body = Get-Content $htmlFileName   
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body   
$body.isBodyhtml = $true  
$smtpServer = $MailServer  
$smtp = new-object Net.Mail.SmtpClient($smtphost)  
$validfrom= Validate-IsEmail $from  
if($validfrom -eq $TRUE)  
$validTo= Validate-IsEmail $to  
if($validTo -eq $TRUE)  
write-output "Email Sent!!"  
write-output "Invalid entries, Try again!!"  
$date = ( get-date ).ToString('yyyy/MM/dd')  
sendEmail -from $From -to $to -subject "Service Status - $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename  

The Function Get-ServiceStatusReport contains five parameters

  1. ComputerList – List of Servers
  2. ServiceName – Name of Services separated by comma
  3. SMTPMail – SMTP mail address
  4. FromID – Valid Email ID
  5. ToID – Valid Email ID

Function call –

Get-ServiceStatusReport -ComputerList C:\server.txt -includeService  "MySQL","MpsSvc","W32Time" -To pjayaram@app.com -From pjayaram@ app.com -SMTPMail  app01. app.com


Get-ServiceStatusReport -ComputerList C:\server.txt -includeService  MySQL,MpsSvc,W32Time -To pjayaram@app.com -From pjayaram@ app.com -SMTPMail  app01. app.com



Posted in PowerShell, Uncategorized | Tagged , , , | Leave a comment

Mailbox Statistics report with Email addresses

Script to collect and export the mailbox properties from Get-Mailbox and Get-MailboxStatistics cmdlets to a CSV file

This script can be extended based on the required attributes

Get-Mailbox -ResultSize Unlimited  | 
Select-Object DisplayName, 
@{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}},
@{label="TotalItemSize";expression={(Get-MailboxStatistics $_).TotalItemSize}},  
@{label="DeletedItemCount";expression={(Get-MailboxStatistics $_).DeletedItemCount}},
@{label="TotalDeletedItemSize";expression={(Get-MailboxStatistics $_).TotalDeletedItemSize}},
@{label="MailboxGuid";expression={(Get-MailboxStatistics $_).MailboxGuid}},
@{label="LastLogoffTime";expression={(Get-MailboxStatistics $_).LastLogoffTime}}, 
@{label="LastLogonTime";expression={(Get-MailboxStatistics $_).LastLogonTime}},IssueWarningQuota, ProhibitSendQuota 
 Export-Csv "Path to the output file " -NoTypeInformation
Posted in Exchange, PowerShell | Tagged , , , , , | Leave a comment

MongoDB -Access different databases and Collections

You can use db.getSiblingDB() method to access another database without switching the database.

To List Collections of PP database and query collection named “restaurants”


The PP Database has three collections

  1. first
  2. restaurants
  3. second

Accessing PP database temporarily from Test database


To access ‘restaurants’ collection of PP database





Posted in MongoDB, Uncategorized | Tagged , , , , , , | Leave a comment

XenApp/XenDesktop 7.6 FP3 VDA Deployment Issue

During the upgrade to XenApp 7.6 FP3 VDA encountered the following message.



I was wondered on seeing this popup to restart the server during the upgrade. After restarting the server for couple of times I was encountered with the same popup window. Well , after further investigation found there is one registry key which was the cause for this issue…

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

Have navigated to the above mentioned registry path and deleted the contents from the Reg key PendingFileRenameOperations which fixed the issue and completed the upgrade process.

Note- During any installation if we need to bypass the restart activity we can follow the above mentioned procedure. This key will not be available,If there are no pending operations.







Posted in Citrix, XenApp VDA, XenDesktop VDA | Tagged , , | Leave a comment

Install MongoDB 3.2 on Windows

We can install MongoDB as a windows service. The steps are given below

Prerequisite –

Install Hotfix kb2731284 on the MongoDB Server on Windows Box

STEP1 – Download MongoDB

Download the latest release of MongoDB from http://www.mongodb.org/downloads and select the Mongo package based on an OS version

Have downloaded the 64 bit mongodb-win32-x86_64-3.2.0-signed.msi

STEP 2 – Create a folder c:\data\db

MongoDB requires a folder to store db files. The default location is c:\data\db

STEP 3 –

Double click the MSI package and use the default path

STEP 4 – Go to services manager and start the Mongodb service

STEP 5 –  Go to cmd prompt and browse till bin folder and type mongo to start the services


Note: Forefront client security was blocking the executable hence the service was not starting. I have stopped it to start the service.

Posted in MongoDB | Tagged , | Leave a comment

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 48,000 times in 2015. If it were a concert at Sydney Opera House, it would take about 18 sold-out performances for that many people to see it.

Click here to see the complete report.

Posted in Uncategorized | Leave a comment

Learn How to Insert Data From Stored Procedure Into Table?

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also important to know how to retrieve data they return. Now, the question is how to insert data from stored procedure into table? It can be a simple task when the datatypes and columns are known, but when these datatypes are not known, it can get difficult. This blog discusses about two methods using which users can know how to insert data from Stored Procedure into Table. One method will describe a method where table is already available and one is where table is created in run time. This blog will highlight both of these procedures and users can utilize the one, which is required.

Step 1: Create A Stored Procedure

The first step of this discussion will be creation of Stored Procedure. Below mentioned script can be used for this.

Create procedure

Below mentioned script can be used to execute this stored procedure;

Get Database Name

Step 2: Insert Data from Stored Procedure into Table

Scenario 1: When schema is known and table is already created.

When the schema of Stored Procedure resultset is known the table can be created and executed using following code.

Insert Data From Stored Procedure

Caveats: This process can be an easy task but the only issue with this operation is if the Stored Procedure returns less or more columns than defined, it will show an error.

Scenario 2: When schema is not known and Table is created at runtime.

In this case, the table has to be populated according to the stored Procedure in Run time. In such case, resultset of the Stored Procedure is not known. Following code can be executed for such scenario;

Select Into

Caveat: This method can get more difficult and works well when users are not aware of column names. In case this method is throwing errors, enable ad hoc distributed queries. This can be done through execution of below mentioned query;

Show Advance Option


In this way, the data from stored procedure can be inserted to table in SQL Server. These methods can be implemented for different scenarios and the data from Stored Procedure can be inserted to Table by creating Table prior to execution or while run-time.

Posted in SQL | Tagged , , , , , | Leave a comment

SQL to generate Asset Information – Configuration Manager SCCM 2012

SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName,  
            s.Operating_System_Name_and0 AS OSName,  
            pr.Name0 AS ProcessorTypeSpeed,  
            pr.Manufacturer0 Manufacturer, 
            pr.NumberOfCores0 Cores, 
            pr.NumberOfLogicalProcessors0 LgicalProcessorCount, 
            case when pr.DataWidth0=64 then '64 bit' else '32 bit' end DataWidth, 
            m.TotalPhysicalMemory0/1024.00 AS MemoryMB,  
            GS1.TotalVirtualMemorySize0 VirtualMemory, 
            GS1.TotalVisibleMemorySize0 VisibleMemory, 
            T1.COL AS TotalDriveSize, 
            DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]           
FROM v_R_System_Valid s  
       INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID 
       INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID  
       INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID 
      -- INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID  
       INNER JOIN  
            SELECT COL.deviceid0 +' '+ cast(COL.Size0/1024.00 AS varchar(20))+' ' 
            FROM v_GS_LOGICAL_DISK COL   
                COL.ResourceID = TAB.ResourceID AND COL.DriveType0=3 
             FOR XML PATH ('')  
            ) COL  
FROM v_R_System_Valid TAB  
 where T.COL is NOT NULL  
 ) T1 on T1.RESOURCENAME=s.Netbios_Name0 
       INNER JOIN V_GS_OPERATING_SYSTEM GS1 on GS1.ResourceID=s.ResourceID 
            s.Operating_System_Name_and0 LIKE '%Windows NT Server%' 
       ip.IPAddress0 IS NOT NULL AND ip.DefaultIPGateway0 IS NOT NULL        
Posted in SCCM, SQL, T-SQL | Tagged , | Leave a comment

Use FILEPROPERTY to find free space in all the database

The use of SpaceUsed property of the FILEPROPERTY function gives how much space is used also we can derive lot of other attributes of it such as free space and percentage of free space.

For other versions of SQL you can refer the below SQL.

DECLARE @command VARCHAR(5000)   
( ServerName VARCHAR(100),   
DatabaseName VARCHAR(100),   
PhysicalFileName NVARCHAR(520),   
FileSizeMB DECIMAL(10,2),   
SpaceUsedMB DECIMAL(10,2),   
FreeSpaceMB DECIMAL(10,2), 
FreeSpacePct varchar(8) 
SELECT @command = 'Use [' + '?' + '] SELECT   
@@servername as ServerName,   
' + '''' + '?' + '''' + ' AS DatabaseName   , name 
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB 
    , convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB 
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB, 
    CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct 
from sys.database_files a' 
EXEC sp_MSForEachDB @command   
SELECT * from @DBSpaceInfo 

For SQL 2000 you can refer the below sql  using sysfiles system table.

DECLARE @command VARCHAR(5000)   
( ServerName VARCHAR(100),   
DatabaseName VARCHAR(100),   
PhysicalFileName NVARCHAR(520),   
FileSizeMB DECIMAL(10,2),   
SpaceUsedMB DECIMAL(10,2),   
FreeSpaceMB DECIMAL(10,2), 
FreeSpacePct varchar(8) 
SELECT @command = 'Use [' + '?' + '] SELECT   
@@servername as ServerName,   
' + '''' + '?' + '''' + ' AS DatabaseName   , filename 
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB 
    , convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB 
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB, 
    CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct 
from dbo.sysfiles a' 
EXEC sp_MSForEachDB @command   
SELECT * from @DBInfo


Posted in databases_files, FileProperty, sp_msforeachDB, SQL, sysfiles | Tagged , | Leave a comment

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

Added few more examples

Prashanth Jayaram

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

The above undocumented SP’s  iterate through each database and each table of a SQL instance

Download SQL :- UndocumentSPToGetNoOfRowsAllDatabases


CREATE TABLE  #TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;

EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
INSERT INTO #TableRowCounts ([databaseNAme],[TableName], [RowCount])
EXEC [?].dbo.sp_MSforeachtable @command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',@replacechar = ''&'''

SELECT * FROM #TableRowCounts
DROP TABLE #TableRowCounts	

Note:-The above query is expensive. Please use at your risk



The use of IF and Like clause with sp_MSforeachtable

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' =''[dbo].[log]'')
 PRINT N''?''
 SELECT COUNT(*) from ? 

EXEC [sp_MSforeachtable] @command1=N' IF (N''?'' LIKE N''%Resource%]'') BEGIN PRINT N''?'' SELECT ''?'' TABLENAME,COUNT(*)…

View original post 102 more words

Posted in Uncategorized | Leave a comment