Skip to main content

Powershell, SQLLDR, CMDB for MSSQL

The following are the end-to-end coding solution to collect MSSQL system information (CPU/cores, memroy, disk space and free space, OS version, database version and edition), and load the information to Oracle database as CMDB repository, the steps are:


  1. Create tables as CMDB repository in oracle db: mssqlhostcpuinfo ; mssqlhostmemoryinfo ; mssqlhostdiskinfo ; mssqlhostosinfo ; mssqlhostdbinfo ;
  2. Truncate the above tables
  3. Run powershell to collect MSSQL server information and output to files mssqlcpu.txt, ssqlos.txt,mssqldb.txt,mssqlmem.txt,mssqldisk.txt
  4. Run sqlldr and load above txt file into the repository tables
  5. Create view mssqlcmdball, mssqlcmdbserver, mssqlcmdbdb

Step 1:


create table mssqlhostcpuinfo (hostname varchar2(20), cores int,logicalcors int);

create table mssqlhostmemoryinfo (hostname varchar2(20), membytes number(13));
create table mssqlhostdiskinfo (hostname varchar2(20), diskletter varchar2(2),capacity number(13), freedisk number(13));
create table mssqlhostosinfo (hostname varchar2(20), os varchar2(60),version varchar2(15));
create table mssqlhostdbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60));
create table mssqlaux (host varchar2(20),dbname varchar2(100),lifecyclestatus varchar2(10))

Step 5:

create or replace view mssqlcmdball
as
select cpu.hostname, db.instancename,os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB, disk.diskletter,disk.capacity,disk.freedisk,substr(db.dbname,1,50) dbname,db.version dbversion
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostdiskinfo disk,
mssqlhostosinfo os,
mssqlhostdbinfo db
where cpu.hostname=mem.hostname(+)
and cpu.hostname=disk.hostname(+)
and cpu.hostname=os.hostname(+)
and cpu.hostname=db.hostname(+)
;

create or replace view mssqlcmdbserver
as
select cpu.hostname, os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB, disk.diskletter,disk.capacity,disk.freedisk
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostdiskinfo disk,
mssqlhostosinfo os
where cpu.hostname=mem.hostname(+)
and cpu.hostname=disk.hostname(+)
and cpu.hostname=os.hostname(+)
;

create or replace view mssqlcmdbdb
as
select cpu.hostname, db.instancename,os.version osversion,cpu.cores,mem.membytes/1024/1024 MEM_MB,substr(db.dbname,1,60) dbname,db.version dbversion
from mssqlhostcpuinfo cpu,
mssqlhostmemoryinfo mem,
mssqlhostosinfo os,
mssqlhostdbinfo db
where cpu.hostname=mem.hostname(+)
and cpu.hostname=os.hostname(+)
and cpu.hostname=db.hostname(+)
;

 Powershell script to run step 2,3,4:

#* FileName: ServerInventory.ps1
#*=============================================================================
#* Script Name: [CollectMSSQLServerinfo.ps1]
#* Created: [Nov 6th, 2018]
#* Author: jiulu Sun
#* Company: BCFerries
# usage : BCFerries data service team managed sqlservers
# How to use : run powershell, type $PathOf\filename.ps1, it will loop all servers defined in $arrComputers
# you need to change executionpolicy first if its at restricted mode. or you will get an error
# "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details."
# this is an example
# PS C:\vb> Get-ExecutionPolicy
# Restricted
# PS C:\vb> set-executionpolicy remotesigned
# PS C:\vb> Get-ExecutionPolicy
# RemoteSigned
# define the file_name which has sqlserver instance names, this file will be read in loop
$arrComputers = get-Content -Path "C:\projects\mssql\CMDB\SQLServerList.txt"
#define output files
$OutputfileCPU = "C:\projects\mssql\CMDB\MSSQLCPU.txt"
$OutputfileMem = "C:\projects\mssql\CMDB\MSSQLMem.txt"
$OutputfileDisk = "C:\projects\mssql\CMDB\MSSQLDisk.txt"
$OutputfileDB = "C:\projects\mssql\CMDB\MSSQLDB.txt"
$OutputfileOS = "C:\projects\mssql\CMDB\MSSQLOS.txt"
#clear the outputfiles
remove-item -Path $OutputfileCPU -ErrorAction SilentlyContinue
remove-item -Path $OutputfileMem -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDisk -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDB -ErrorAction SilentlyContinue
remove-item -Path $OutputfileOS -ErrorAction SilentlyContinue

$ONETAB+=" "
$TWOTABS=" "
$BLANKLINE=""

Function MemInfo {
Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $strComputer|Select-Object -Property "Name","TotalPhysicalMemory" |Export-Csv -Path $OutputfileMEM -append -NoTypeInformation -force
}

Function OSInfo {
 Get-WmiObject Win32_OperatingSystem -ComputerName $strComputer|select-object csname,caption,version|Export-Csv -Path $OutputfileOS -append -NoTypeInformation -force
}

Function CPUInfo {
$property = "systemname","numberOfCores", "NumberOfLogicalProcessors"
Get-WmiObject win32_processor -computername $strComputer |Select-Object -Property $property |sort -unique|Export-Csv -Path $OutputfileCPU -append -NoTypeInformation -force
}

Function DiskInfo {
$property = "__SERVER","DriveLetter","Capacity","FreeSpace"
Get-WmiObject Win32_Volume -filter "DriveLetter IS NOT NULL AND FileSystem IS NOT NULL" -computername $strComputer|Select-Object -Property $property|Export-Csv -Path $OutputfileDISK -append -NoTypeInformation -force
}


Function sysdbInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel')) 
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
  FROM master.dbo.sysdatabases sd
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}


#*=============================================================================
#* SCRIPT BODY
#*=============================================================================

#truncate the cmdb tables before
$server="emservername"
(echo "truncate table mssqlhostcpuinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostmemoryinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostdiskinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostosinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"
(echo "truncate table mssqlhostdbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1528))) (CONNECT_DATA = (service_name =emservicename.bcferries.com)))"

foreach ($strComputer in $arrComputers)

{ #Function Calls go here

"#########################################################################"
"Computer Name:" +$strComputer
"#########################################################################"


#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbInfo

####now remove the name instance.
if ($strComputer.contains("\"))
{#'this is a named instance
$position=$strComputer.indexof("\")
$strComputer = $strComputer.substring(0,$position)
#"computer name is :" + $strComputer
}

#* Call SysInfo Function
"Sytem Information"
MemInfo

#* Call OSInfo Function
"Operating System Information"
OSInfo

#* Call CPUInfo Function
"Processor Information"
CPUInfo

#* Call DiskInfo Function
"Disk Information"
DiskInfo

}

#now load these tables
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlcpu.ctl log=C:\projects\MSSQL\CMDB\mssqlcpu.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlOS.ctl log=C:\projects\MSSQL\CMDB\mssqlOS.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDB.ctl log=C:\projects\MSSQL\CMDB\mssqlDB.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlMEM.ctl log=C:\projects\MSSQL\CMDB\mssqlMEM.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDisk.ctl log=C:\projects\MSSQL\CMDB\mssqlDISK.log


SQLLDR ctrl files are listed below:

MSSQLCPU.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLCPU.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.dis'
    APPEND
    INTO TABLE jsun.mssqlhostcpuinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     CORES ,
     LOGICALCORS
    )
    
MSSQLDB.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDB.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDB.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDB.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdbinfo 
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char,
     RECOVERYMODE char,
     STATUS char,
     VERSION char,
     SP char,
     edition char "rtrim(:edition)" 
     )
    
MSSQLDISK.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDISK.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdiskinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     diskletter char,
     capacity,
     freedisk
     )
    
    
 MSSQLMEM.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLMEM.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.dis'
    APPEND
    INTO TABLE jsun.mssqlhostmemoryinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     membytes
     )
MSSQLOS.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLOS.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlOS.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlOS.dis'
    APPEND
    INTO TABLE jsun.mssqlhostosinfo  
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     OS char ,
     version char
    )
    
 Sample query result of view:
















Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca
Post a Comment
Read more

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k
3 comments
Read more

shell script to clean up oracle dumpfile

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [
Post a Comment
Read more