Wednesday, August 28, 2013

Scan Software Installed Powershell Script

The process has been design to scan on the windows based computer  installed software and logs it to text file and SQL Server database.   It requires the tblHardware_Populated and tblSoftware_Populated tables.  The following DML commands will create them.


CREATE TABLE [dbo].[tblHardware_Populated](
 [ComputerName] [nvarchar](30) NOT NULL,
 [SerialNumber] [nvarchar](50) NULL,
 [PhysicalMemory] [nvarchar](10) NOT NULL,
 [DiskSpace] [nvarchar](max) NOT NULL,
 [Domain] [nvarchar](50) NOT NULL,
 [Manufacturer] [nvarchar](100) NULL,
 [Model] [nvarchar](100) NULL,
 [PrimaryOwnerName] [nvarchar](50) NOT NULL,
 [DateCreated] [datetime] NOT NULL,
 [JobName] [nvarchar](256) NULL,
 [ActionsPath] [nvarchar](256) NULL,
 [LastRunTime] [datetime] NULL,
 [NextRunTime] [datetime] NULL,
 [Actions] [nvarchar](256) NULL,
 CONSTRAINT [PK_tblHardware_Populated] PRIMARY KEY CLUSTERED
(
 [ComputerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSoftware_Populated](
 [SoftwareID] [int] IDENTITY(1,1) NOT NULL,
 [ComputerName] [nvarchar](30) NOT NULL,
 [PackageName] [nvarchar](200) NOT NULL,
 [InstallDate] [date] NULL,
 [InstallLocation] [nvarchar](256) NULL,
 [PackageCache] [nvarchar](50) NULL,
 [Vendor] [nvarchar](200) NULL,
 [Version] [nvarchar](30) NULL,
 CONSTRAINT [PK_tblSoftware_Populated] PRIMARY KEY CLUSTERED
(
 [SoftwareID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 ALTER TABLE [dbo].[tblSoftware_Populated]  WITH NOCHECK ADD  CONSTRAINT [FK_tblSoftware_Populated_tblHardware_Populated] FOREIGN KEY([ComputerName])

REFERENCES [dbo].[tblHardware_Populated] ([ComputerName])

GO
ALTER TABLE [dbo].[tblSoftware_Populated] NOCHECK CONSTRAINT [FK_tblSoftware_Populated_tblHardware_Populated]

GO

Application Folder Structure

The Universal Data Link file dbConnection.udl provides connection to SQL Server database and Powershell script should be located in .\InstalledSoftware\Script\ folder.  Output  data and scan.log (process log) file will be created in .\InstalledSoftware\Output\ directory.  Here is the script listing:
#This script scans installed software and log it to text file and ConfigManag database
    function datetime{
      $year = (Get-Date).Year;
      $month = Get-Date -format MM;
      $day = Get-Date -format dd;
      $hour = Get-Date -format hh;
      $minute = Get-Date -format mm;
      $second = Get-Date -format ss;
      return "$day/$month/$year $hour`:$minute`:$second";
    }
  
    function getTasks($path) {
        $out = @()
        # Get root tasks
        $schedule.GetFolder($path).GetTasks(0) | % {
            $xml = [xml]$_.xml
            $out += New-Object psobject -Property @{
                "Name" = $_.Name
                "Path" = $_.Path
                "LastRunTime" = $_.LastRunTime
                "NextRunTime" = $_.NextRunTime
                "Actions" = ($xml.Task.Actions.Exec | % { "$($_.Command) $($_.Arguments)" }) -join "`n"
            }
        }
        # Get tasks from subfolders
        $schedule.GetFolder($path).GetFolders(0) | % {
            $out += getTasks($_.Path)
        }
        #Output
        $out
    }
    $tasks = @()
    $schedule = New-Object -ComObject "Schedule.Service"
    $schedule.Connect()
    # Start inventory
    $tasks += getTasks("\")
    # Close com
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($schedule) | Out-Null
    Remove-Variable schedule
    # Output all tasks
    #$tasks
    CLEAR-HOST
    [void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
    [console]::ForegroundColor = "yellow"
    $date = datetime;
    "** Start process $date **";
    write-host "`r";
    $dt = Get-Date -format "yyyy_MM_dd"
    $a = $ErrorActionPreference
    $ErrorActionPreference = "SilentlyContinue"
    try{
        $file =  "Scan_" + $dt + ".txt"
        $JobName = "Scan Software Installed"
        $folder = ".\InstalledSoftware\Output\"
        $filePath = $folder + $file
        $outlog = $folder+ "\scan.log"
        $($date) | out-file $outlog -append
        $testFileExists = Test-Path $outlog
        if ($testFileExists -eq $false) {
            "" | out-file $outlog
        }
        $scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
        $myconnectionstring = "File Name=" + $scriptPath + "\dbConnection.udl"
      
        $adOpenStatic = 3
  $adLockOptimistic = 3
        $objConnection = New-Object -comobject ADODB.Connection
        $objRecordset = New-Object -comobject ADODB.Recordset
        $objConnection.Open($myconnectionstring)
        if($objConnection.state -eq 0){
          "Could not establish connection" | out-file $outlog -append
          exit
        }
      
        $cmd = Get-ChildItem $folder -force | select name
        $testFileExists = $false
        $filesNo = 0
        foreach ($str in $cmd){
    $test = $str.name
                if ($str.name -eq $file) {
                    $testFileExists = $true
                    break
                } else {
                    $StartsWith = $test -match  "Scan"
                    if ($StartsWith -eq $true){
                        $filesNo = $filesNo + 1
                    }
                }  
        }

  #Delete existing data        if ($testFileExists -eq $false) {
            if ($filesNo -gt 0){
    Get-ChildItem $folder | Remove-Item -r -force
            }  
            New-Item $filePath -type file
        
         $strSQL = "TRUNCATE TABLE dbo.tblSoftware_Populated"
         $retval = $objConnection.Execute($strSQL)
            $strSQL = "ALTER TABLE dbo.tblSoftware_Populated DROP CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated"
            $retval = $objConnection.Execute($strSQL)
            $strSQL = "TRUNCATE TABLE dbo.tblHardware_Populated"
         $retval = $objConnection.Execute($strSQL)
            $strSQL = "ALTER TABLE dbo.tblSoftware_Populated  WITH NOCHECK ADD  CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated FOREIGN KEY(ComputerName)"
            $strSQL = $strSQL + "REFERENCES dbo.tblHardware_Populated (ComputerName)"
            $retval = $objConnection.Execute($strSQL)
            $strSQL = "ALTER TABLE dbo.tblSoftware_Populated NOCHECK CONSTRAINT FK_tblSoftware_Populated_tblHardware_Populated"
            $retval = $objConnection.Execute($strSQL)
        } 
        Add-Content $filePath $log
        $log = "*" * 50
        Add-Content $filePath $log  #Check if it is already scanned        $strSQL = "SELECT COUNT(*) counter FROM dbo.tblHardware_Populated WHERE"
        $strSQL = $strSQL + " ComputerName = '" + $($env:computername) + "'"
        $objRecordset.Open($strSQL,$objConnection,$adOpenStatic,$adLockOptimistic)
        if($objRecordset.EOF -eq $True){
     "No Data found" | out-file $outlog -append
     exit
  }
        $counter = $objRecordset.Fields.Item("counter").Value
        $objRecordset.Close()  #Populate Hardware        if ($counter -eq 0){
            $strSQL = "INSERT INTO dbo.tblHardware_Populated("
            $strSQL = $strSQL + "ComputerName"
            $strSQL = $strSQL + ",SerialNumber"
            $strSQL = $strSQL + ",PhysicalMemory"
            $strSQL = $strSQL + ",DiskSpace"
            $strSQL = $strSQL + ",Domain"
            $strSQL = $strSQL + ",Manufacturer"
            $strSQL = $strSQL + ",Model"
            $strSQL = $strSQL + ",PrimaryOwnerName"
            $strSQL = $strSQL + ",DateCreated"
            $strSQL = $strSQL + ",JobName"
            $strSQL = $strSQL + ",ActionsPath"
            $strSQL = $strSQL + ",LastRunTime"
            $strSQL = $strSQL + ",NextRunTime"
            $strSQL = $strSQL + ",Actions"
            $strSQL = $strSQL + " )VALUES("
            $strSQL = $strSQL + "'" + $($env:computername) + "'"
            $log = "Computer Name: " +$env:computername
            Add-Content $filePath $log
            $log = get-wmiobject win32_bios | select-object serialnumber
            foreach ($str in $log){
                $out = "Serial Number: " + $str.serialnumber
                $strSQL = $strSQL + ",'" + $($str.serialnumber) + "'"
                Add-Content $filePath $out
            }
          
            $txt = Get-WMIObject -class Win32_PhysicalMemory |
            Measure-Object -Property capacity -Sum |
            select @{N="~"; E={[math]::round(($_.Sum / 1GB),2)}}
            $log = $txt -replace "@{",""
            $log = $log -replace "}",""
            $log = $log -replace "=",""
            $log = $log -replace "~",""
            $strSQL = $strSQL + ",'" + $log + "'"
            $log = "Total Physical Ram: " + $log
            Add-Content $filePath $log
            $txt = ""
            $log = gwmi win32_volume -Filter 'drivetype = 3' |
            select driveletter, label, @{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)} }
            foreach ($str in $log){
                $out = "Driveletter : " + $str.driveletter + "`t"
                $txt = $txt + $out;
                #Add-Content $filePath $out
                $out = "Label : " + $str.label + "`t"
                $txt = $txt + $out;
                #Add-Content $filePath $out
                $out = "GBfreespace : " + $str.GBfreespace + "`t"
                $txt = $txt + $out;
                #Add-Content $filePath $out
            }
            $strSQL = $strSQL + ",'" + $txt + "'"
            Add-Content $filePath $txt
            $log = get-wmiobject win32_computersystem
            foreach ($str in $log){
                $out = "Domain : " + $str.Domain
                $strSQL = $strSQL + ",'" + $($str.Domain) + "'"
                Add-Content $filePath $out
                $out = "Manufacturer : " + $str.Manufacturer
                $strSQL = $strSQL + ",'" + $($str.Manufacturer) + "'"
                Add-Content $filePath $out
                $out = "Model : " + $str.Model
                $strSQL = $strSQL + ",'" + $($str.Model) + "'"
                Add-Content $filePath $out
                $out = "PrimaryOwnerName : " + $str.PrimaryOwnerName
                $strSQL = $strSQL + ",'" + $($str.PrimaryOwnerName) + "'"
                Add-Content $filePath $out
            }
            $strSQL = $strSQL + ",CONVERT(datetime,'" + $date + "',103)"
          
            $out = ""
            foreach ($str in $tasks){
                if ($str.Name -eq $JobName){
                    $out = "Name: " + $str.Name
                    $strSQL = $strSQL + ",'" + $($str.Name) + "'"
                    Add-Content $filePath $out
                    $out = "Path: " + $str.Path
                    $strSQL = $strSQL + ",'" + $($str.Path) + "'"
                    Add-Content $filePath $out
                    $out = "LastRunTime: " + $str.LastRunTime
                    $strSQL = $strSQL + ", CONVERT(datetime,'" + $($str.LastRunTime) + "',101)"
                    Add-Content $filePath $out
                    $out = "NextRunTime: " + $str.NextRunTime
                    $strSQL = $strSQL + ", CONVERT(datetime,'" + $($str.NextRunTime) + "',101)"
                    Add-Content $filePath $out
                    $out = "Actions: " + $str.Actions
                    $strSQL = $strSQL + ",'" + $($str.Actions) + "'"
                    Add-Content $filePath $out
                    Break
                }
              
            }
          
            if ($out -eq ""){
                $out = "Name: "
                $strSQL = $strSQL + ",Null"
                Add-Content $filePath $out
                $out = "Path: "
                $strSQL = $strSQL + ",Null"
                Add-Content $filePath $out
                $out = "LastRunTime: "
                $strSQL = $strSQL + ",Null"
                Add-Content $filePath $out
                $out = "NextRunTime: "
                $strSQL = $strSQL + ",Null"
                Add-Content $filePath $out
                $out = "Actions: "
                $strSQL = $strSQL + ",Null"
                Add-Content $filePath $out
            }
          
            $strSQL = $strSQL + ")"
            $retval = $objConnection.Execute($strSQL)
            $log = "*" * 50
            Add-Content $filePath $log
            Add-Content $filePath ""
            $log = " " * 15 + "Software Installed"
            Add-Content $filePath $log
        
   #Populate Software            Add-Content $filePath ""
            $log = Get-WmiObject -Class Win32_Product -ComputerName . |
             select Name,InstallDate,InstallLocation,PackageCache,Vendor,Version,IdentifyingNumber
            foreach ($str in $log){
                $strSQL = "INSERT INTO dbo.tblSoftware_Populated("
                $strSQL = $strSQL + "ComputerName"
                $strSQL = $strSQL + ",PackageName"
                $strSQL = $strSQL + ",InstallDate"
                $strSQL = $strSQL + ",InstallLocation"
                $strSQL = $strSQL + ",PackageCache"
                $strSQL = $strSQL + ",Vendor"
                $strSQL = $strSQL + ",Version"
                $strSQL = $strSQL + " )VALUES("
                $strSQL = $strSQL + "'" + $($env:computername) + "'"
                $out = "Name: " + $str.Name
                $strSQL = $strSQL + ",'" + $($str.Name) + "'"
                Add-Content $filePath $out
                $dt = $str.InstallDate
                $dtd = ",null"
                if ($dt) {
                    $dta = $dt.Substring(6,2) + "/" + $dt.Substring(4,2) + "/" + $dt.Substring(0,4)
                    $dtd = ",'" + $dt + "'"
                }
                $out = "InstallDate: " + $dta
                $strSQL = $strSQL + $dtd
                Add-Content $filePath $out
                $loc = ",null"
                if ($str.InstallLocation) {
                    $loc = ",'" + $str.InstallLocation + "'"
                }  
                $out = "InstallLocation: " + $str.InstallLocation
                $strSQL = $strSQL + $loc
                Add-Content $filePath $out
                $out = "PackageCache: " + $str.PackageCache
                $strSQL = $strSQL + ",'" + $($str.PackageCache) + "'"
                Add-Content $filePath $out
                $out = "Vendor: " + $str.Vendor
                $strSQL = $strSQL + ",'" + $($str.Vendor) + "'"
                Add-Content $filePath $out
                $out = "Version: " + $str.Version
                $strSQL = $strSQL + ",'" + $($str.Version) + "'"
                Add-Content $filePath $out
                $strSQL = $strSQL + ")"
                Add-Content $filePath ""
                #Write-Output $strSQL
                $retval = $objConnection.Execute($strSQL)
            }
            $JobStatus = "OK"
        }  
    } catch [Exception] {
            "$($_.InvocationInfo.ScriptName)($($_.InvocationInfo.ScriptLineNumber)): $($_.InvocationInfo.Line)" | out-file $outlog -append
         $("Error catched: " + $_.Exception.GetType().FullName) | out-file $outlog -append
         $("Error catched: " + $_.Exception.Message) | out-file $outlog -append
         $JobStatus = "not OK"
         continue;
    }
    $ErrorActionPreference = $a
    $objConnection.Close()
    $date = datetime;
    "** End of process $date **";
 
The process can be scheduled according your requirements.

If you find this script useful please donate generously.

Posted by at No comments:

Monday, February 4, 2013

Exporting pdf documents properties to the text file

The following script extracts pdf documents some property fields and dump them to the text file.  Note that if itextsharp.dll does not exist on your system it can be downloaded from http://sourceforge.net/projects/itextsharp/.  To add other fields to output see documentation on http://www.adobe.com.

[System.Reflection.Assembly]::LoadFrom(\\your_server\..\itextsharp.dll)

function Get_PDF_Properties($path){
    Try{
        $raf = New-object iTextSharp.text.pdf.RandomAccessFileOrArray($path)
        $reader = New-object iTextSharp.text.pdf.PdfReader($raf, $Nothing)
        $myPDF= ""| select Title,Subject
        $mypdf.title=($reader).info.item("Title")
        $sub = 0
        if ((($reader).info.item("Subject").Length -ne 0)){
            $mypdf.subject=($reader).info.item("Subject")
            $sub = 1
        } 
    }
    Catch [Exception]{
        if ($sub -eq 0) {
            $mypdf.subject=""
        } else {
            Write-Host "Generic Exception"
            Write-Host $_
            $_ | Select *
        } 
    }
  return $mypdf
}
$in = \\your_server\..\
$out = "\\your_server\..\file_name.txt"
Add-Content -Path $out -Value "ImagePath`tTitle`tSubject" -Encoding UTF8
$dirstr = Get-ChildItem $in -recurse -filter "*.pdf" | Select-Object FullName
foreach ($objItem in $dirstr) {
    $path = $objItem -replace "@{FullName=", ""
    $path = $path -replace "}", ""
    $mypdf = Get_PDF_Properties($path)
    $pdf = $mypdf -replace "@{Title=", ""
    $pdf = $pdf -replace " Subject=", ""
    $pdf = $pdf -replace "}", ""
    $pdf = $pdf -replace ";", "`t"
    write-host "$path`t$pdf"
    Add-Content -Path $out -Value "$path`t$pdf" -Encoding UTF8
}
 
If you find this script useful please donate generously.

Posted by at No comments:
Subscribe to: Posts (Atom)