2014-11-24

取得電腦安裝軟體清單及基本資訊 Software List V2

先前寫過一篇 取得電腦安裝軟體清單及基本資訊 Software List
但是撈出來的資料很亂, 如今改寫過, 可以正確地抓出 [新增移除程式] 裡面的清單
加上硬體資訊, 然後寫入 SQL 資料庫

以下先將程式全部刊出, 不深入講解用法
InfoSearch.vbs 是掃描資訊的核心程式, 可以修改前面的變數後單獨執行

總之用 RunInfoSearchLoop.vbs 啟動 RunInfoSearch.vbs
一個網段一個 Process, Scan IP 1 - 253
RunInfoSearch.vbs 會將 IP 丟給 InfoSearch.vbs 去執行
我不知道怎麼改 Wbemscripting.SWbemLocator 的 Timeout 時間
所以掃 253 個 IP 約需時 3.5 小時

InfoSearch.vbs 裡面也可以設定 SQL IP及帳號密碼
或是寫成 File 或 echo 在畫面上
自己看一下裡面的變數改一下就行

SoftwareListReport.vbs 是將每一個被安裝的應用軟體被安裝的數量計算出來
另外存一個 Table 的程式
因為我不是 DBA , 所以 SQL 語法不太會用, 用 Join 來 Join 去的
SQL 會耗用大量 CPU 運算才能得到這個數量, 所以先跑一次寫到 Table 裡去
下次要同樣資料時就不用再計算一次

至於前台要怎麼產生報表
我花了一個下午研究以前沒學過的 ASP .Net , 決定放棄
只用 asp 簡單寫一下, 就不提供檔案了, 文末有提供各種資料 Query 的語法
請自行套用, 如果您有更好的寫法拜託您一定要提供給我


===== RunInfoSearchLoop.vbs 程式開始 =====

set service = GetObject ("winmgmts:")

CountProcess = 0
for each Process in Service.InstancesOf ("Win32_Process")
If LCase(Process.Name) = LCase("wscript.exe") then
CountProcess = CountProcess + 1
End if
if (CountProcess > 1) Then
wscript.quit
exit for
end if
next

' 沒有在執行, 開始跑 SoftwareList Report
'Set WshShell = WScript.CreateObject("WScript.Shell")
'Return = WshShell.Run("SoftwareListReport.vbs", 0, True)

' 沒有在執行, 開始執行
Set WshShell = WScript.CreateObject("WScript.Shell")
Return = WshShell.Run("RunInfoSearch.vbs", 0, False)

===== RunInfoSearchLoop.vbs 程式結束 =====



===== RunInfoSearch.vbs 程式開始 =====

if WScript.Arguments.Count = 0 then
Return = LaunchSearch("Search","192.168.1")
Return = LaunchSearch("Search","192.168.2")
wscript.quit
elseif WScript.Arguments.Count = 1 then
RunInfoSearch(Wscript.Arguments(0))
end if

Function LaunchSearch(Method,IPClass)
Set WshShell = WScript.CreateObject("WScript.Shell")
Return = WshShell.Run(WScript.ScriptFullName & " " & IPClass, 0, False)
End Function

Function RunInfoSearch(IPClass)
IPStart = 1
IPEnd = 253
Set WshShell = WScript.CreateObject("WScript.Shell")
For i = IPStart To IPEnd
RemoteComputer = IPClass & "." & i
Return = WshShell.Run("InfoSearch.vbs " & RemoteComputer, 0, true)
Next
End Function

===== RunInfoSearch.vbs 程式結束 =====


===== InfoSearch.vbs 程式開始 =====

On Error Resume Next

if WScript.Arguments.Count <> 1 then
wscript.quit
end if

RemoteComputer = Wscript.Arguments(0)

AuthUserName = "Contoso\Administrator"
AuthPassword = "password"

WriteFile = 0
WriteSQL = 1

SQLServer="localhost"
SQLUser="Info"
SQLPassword="password"
SQLDB="Info"
SQLConnectionTimeout = 300

Dim SQLConnString
Dim SQLConnection
Dim SQLString
Dim SQLRecordSet

' ----- Set Object Class & Variables
Set objCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
Set objLocator = CreateObject("Wbemscripting.SWbemLocator")

Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")

Const HKEY_LOCAL_MACHINE = &H80000002
SQLConnString = "DRIVER={SQL Server};SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPassword & ";DATABASE=" & SQLDB

' ----- MB
Dim MBIndex
Dim MBManufacturer()
Dim MBProduct()
Dim MBSerialNumber()
Dim MBVersion()
MBIndex = -1

' ----- CPU
Dim CPUIndex
Dim CPUAddressWidth()
Dim CPUArchitecture()
Dim CPUCaption()
Dim CPUDataWidth()
Dim CPUDeviceID()
Dim CPUExtClock()
Dim CPUFamily()
Dim CPULevel()
Dim CPUManufacturer()
Dim CPUMaxClockSpeed()
Dim CPUName()
Dim CPUNumberOfCores()
Dim CPUNumberOfLogicalProcessors()
Dim CPUProcessorId()
Dim CPUSocketDesignation()
CPUIndex = -1

' ----- MEM
Dim MEMIndex
Dim MEMBankLabel()
Dim MEMCapacity()
Dim MEMDeviceLocator()
Dim MEMManufacturer()
Dim MEMMemoryType()
Dim MEMPartNumber()
Dim MEMSerialNumber()
Dim MEMSpeed()
Dim MEMTag()
MEMIndex = -1

' ----- NIC
Dim NICIndex
Dim NICCaption()
Dim NICDHCPEnabled()
Dim NICDNSHostName()
Dim NICIPAddress()
Dim NICMACAddress()
Dim NICServiceName()
NICIndex = -1
Dim AllInfoDNSHostName

' ----- DiskIndex
Dim DiskIndex
Dim DiskCaption()
Dim DiskFirmwareRevision()
Dim DiskInterfaceType()
Dim DiskMediaType()
Dim DiskModel()
Dim DiskSerialNumber()
Dim DiskSignature()
Dim DiskSize()
DiskIndex = -1

' ----- CDROMIndex
Dim CDROMIndex
Dim CDROMCaption()
Dim CDROMMediaType()
Dim CDROMMfrAssignedRevisionLevel()
Dim CDROMName()
CDROMIndex = -1

' ----- OS
Dim OSIndex
Dim OSBuildNumber()
Dim OSCaption()
Dim OSCodeSet()
Dim OSCountryCode()
Dim OSCSDVersion()
Dim OSCSName()
Dim OSCurrentTimeZone()
Dim OSInstallDate()
Dim OSLastBootUpTime()
Dim OSLocale()
Dim OSManufacturer()
Dim OSOperatingSystemSKU()
Dim OSOSArchitecture()
Dim OSOSLanguage()
Dim OSOSProductSuite()
Dim OSOSType()
Dim OSOtherTypeDescription()
Dim OSProductKey()
Dim OSProductType()
Dim OSRegisteredUser()
Dim OSSerialNumber()
Dim OSVersion()
OSIndex = -1

' ----- Software List
Dim SoftwareListIndex
Dim SoftwareDisplayName()
Dim SoftwareDisplayVersion()
Dim SoftwarePublisher()
Dim SoftwareInstallDate()
SoftwareListIndex = -1

' ----- Office List
Dim OfficeListIndex
Dim OfficeVersion()
Dim OfficeProductName()
Dim OfficeProductID()
Dim OfficeType()
Dim OfficeProductKey()
Dim OfficeNote()
OfficeListIndex = -1
Dim oReg
Dim osType
Dim aOffID(4,1)
Dim aOffIDX

' ----- SQL

Dim AllInfoID
Dim InsertAllInfoIndex
Dim InsertAllInfoLinkType()
Dim InsertAllInfoLinkValue()
InsertAllInfoIndex = -1

' ----- Get Info

Set objServices = objLocator.ConnectServer(RemoteComputer,"root\cimv2",AuthUserName,AuthPassword,,,,objCtx)
If Err = 0 Then
GetMB()
GetCPU()
GetMEM()
GetNIC()
GetDisk()
GetCDROM()
GetOS()
GetOfficeList()
GetSoftwareList(32)
GetSoftwareList(64)
If (WriteSQL = 1) Then
WriteInfoToSQL()
end if
If (WriteFile = 1) Then
WriteInfoToFile()
End If
End If

wscript.quit

Function WriteInfoToSQL()

Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")

SQLConnString = "DRIVER={SQL Server};SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPassword & ";DATABASE=" & SQLDB

SQLConnection.ConnectionTimeout = SQLConnectionTimeout
SQLConnection.Open SQLConnString

AllInfoID = InsertAllInfo()

For i=0 to (MBIndex)
TableName = "ListMB"
ColumnName = Array("Manufacturer","Product","SerialNumber","Version")
ColumnValue = Array(MBManufacturer(i),MBProduct(i),MBSerialNumber(i),MBVersion(i))
InsertAllInfoMBID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "MB"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoMBID
Next

For i=0 to (CPUIndex)
TableName = "ListCPU"
ColumnName = Array("AddressWidth","Architecture","Caption","DataWidth","DeviceID","ExtClock","Family","Level","Manufacturer","MaxClockSpeed","Name","NumberOfCores","NumberOfLogicalProcessors","ProcessorId","SocketDesignation")
ColumnValue = Array(CPUAddressWidth(i),CPUArchitecture(i),CPUCaption(i),CPUDataWidth(i),CPUDeviceID(i),CPUExtClock(i),CPUFamily(i),CPULevel(i),CPUManufacturer(i),CPUMaxClockSpeed(i),CPUName(i),CPUNumberOfCores(i),CPUNumberOfLogicalProcessors(i),CPUProcessorId(i),CPUSocketDesignation(i))
InsertAllInfoCPUID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "CPU"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoCPUID
Next

For i=0 to (MEMIndex)
TableName = "ListMEM"
ColumnName = Array("Capacity","Manufacturer","MemoryType","PartNumber","SerialNumber","Speed")
ColumnValue = Array(MEMCapacity(i),MEMManufacturer(i),MEMMemoryType(i),MEMPartNumber(i),MEMSerialNumber(i),MEMSpeed(i))
MEMSerial = InsertData(TableName,ColumnName,ColumnValue)

TableName = "ListMEMLink"
ColumnName = Array("MEMSerial","BankLabel","DeviceLocator","Tag")
ColumnValue = Array(MEMSerial,MEMBankLabel(i),MEMDeviceLocator(i),MEMTag(i))
InsertAllInfoMEMID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "MEM"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoMEMID
Next

For i=0 to (NICIndex)
TableName = "ListNIC"
ColumnName = Array("Caption","MACAddress","ServiceName")
ColumnValue = Array(NICCaption(i),NICMACAddress(i),NICServiceName(i))
NICSerial = InsertData(TableName,ColumnName,ColumnValue)

TableName = "ListNICLink"
ColumnName = Array("NICSerial","DHCPEnabled","DNSHostName","IPAddress")
ColumnValue = Array(NICSerial,NICDHCPEnabled(i),NICDNSHostName(i),NICIPAddress(i))
InsertAllInfoNICID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "NIC"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoNICID
Next

For i=0 to (DiskIndex)
TableName = "ListDisk"
ColumnName = Array("Caption","FirmwareRevision","InterfaceType","MediaType","Model","SerialNumber","Signature","Size")
ColumnValue = Array(DiskCaption(i),DiskFirmwareRevision(i),DiskInterfaceType(i),DiskMediaType(i),DiskModel(i),DiskSerialNumber(i),DiskSignature(i),DiskSize(i))
InsertAllInfoDiskID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "Disk"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoDiskID
Next

For i=0 to (CDROMIndex)
TableName = "ListCDROM"
ColumnName = Array("Caption","MediaType","MfrAssignedRevisionLevel","Name")
ColumnValue = Array(CDROMCaption(i),CDROMMediaType(i),CDROMMfrAssignedRevisionLevel(i),CDROMName(i))
InsertAllInfoCDROMID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "CDROM"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoCDROMID
Next

For i=0 to (OSIndex)
TableName = "ListOS"
ColumnName = Array("BuildNumber","Caption","CodeSet","CountryCode","CSDVersion","CurrentTimeZone","Locale","Manufacturer","OperatingSystemSKU","OSArchitecture","OSLanguage","OSProductSuite","OSType","OtherTypeDescription","ProductKey","ProductType","Version")
ColumnValue = Array(OSBuildNumber(i),OSCaption(i),OSCodeSet(i),OSCountryCode(i),OSCSDVersion(i),OSCurrentTimeZone(i),OSLocale(i),OSManufacturer(i),OSOperatingSystemSKU(i),OSOSArchitecture(i),OSOSLanguage(i),OSOSProductSuite(i),OSOSType(i),OSOtherTypeDescription(i),OSProductKey(i),OSProductType(i),OSVersion(i))
OSSerial = InsertData(TableName,ColumnName,ColumnValue)

TableName = "ListOSLink"
ColumnName = Array("OSSerial","CSName","RegisteredUser","SerialNumber","InstallDate","LastBootUpTime")
ColumnValue = Array(OSSerial,OSCSName(i),OSRegisteredUser(i),OSSerialNumber(i),OSInstallDate(i),OSLastBootUpTime(i))
InsertAllInfoOSID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "OS"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoOSID
Next

For i=0 to (OfficeListIndex)
TableName = "ListOffice"
ColumnName = Array("Version","ProductName","ProductID","Type","ProductKey","Note")
ColumnValue = Array(OfficeVersion(i),OfficeProductName(i),OfficeProductID(i),OfficeType(i),OfficeProductKey(i),OfficeNote(i))
InsertAllInfoOfficeID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "Office"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoOfficeID
Next

For i=0 to (SoftwareListIndex)
TableName = "ListSoftware"
ColumnName = Array("DisplayName","DisplayVersion","Publisher")
ColumnValue = Array(SoftwareDisplayName(i),SoftwareDisplayVersion(i),SoftwarePublisher(i))
SoftwareSerial = InsertData(TableName,ColumnName,ColumnValue)

TableName = "ListSoftwareLink"
ColumnName = Array("SoftwareSerial","InstallDate")
ColumnValue = Array(SoftwareSerial,SoftwareInstallDate(i))
InsertAllInfoSoftwareID = InsertData(TableName,ColumnName,ColumnValue)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "Software"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertAllInfoSoftwareID
Next


SQLString = "INSERT INTO [AllInfoLink] ([InfoSerial],[Type],[TypeSerial]) Values "
SQLStringADD = ""
For i=0 to (InsertAllInfoIndex)
If (SQLStringADD <> "") Then
SQLStringADD = SQLStringADD & ","
End If
SQLStringADD = SQLStringADD & "(" & AllInfoID & ",'" & InsertAllInfoLinkType(i) & "'," & InsertAllInfoLinkValue(i) & ")"
next
If (SQLStringADD <> "") Then
SQLString = SQLString & SQLStringADD & ";"
Set SQLRecordSet = SQLConnection.Execute(SQLString)
End If

SQLConnection.Close

End Function

Function WriteInfoToFile()

MBd = (MBIndex+1) & vbcrlf & "MBManufacturer" & vbTab & "MBProduct" & vbTab & "MBSerialNumber" & vbTab & "MBVersion"
For i=0 to (MBIndex)
MBd = MBd & vbcrlf & MBManufacturer(i) & vbTab & MBProduct(i) & vbTab & MBSerialNumber(i) & vbTab & MBVersion(i)
Next

CPUd = (CPUIndex+1) & vbcrlf & "CPUAddressWidth" & vbTab & "CPUArchitecture" & vbTab & "CPUCaption" & vbTab & "CPUDataWidth" & vbTab & "CPUDeviceID" & vbTab & "CPUExtClock" & vbTab & "CPUFamily" & vbTab & "CPULevel" & vbTab & "CPUManufacturer" & vbTab & "CPUMaxClockSpeed" & vbTab & "CPUName" & vbTab & "CPUNumberOfCores" & vbTab & "CPUNumberOfLogicalProcessors" & vbTab & "CPUProcessorId" & vbTab & "CPUSocketDesignation"
For i=0 to (CPUIndex)
CPUd = CPUd & vbcrlf & CPUAddressWidth(i) & vbTab & CPUArchitecture(i) & vbTab & CPUCaption(i) & vbTab & CPUDataWidth(i) & vbTab & CPUDeviceID(i) & vbTab & CPUExtClock(i) & vbTab & CPUFamily(i) & vbTab & CPULevel(i) & vbTab & CPUManufacturer(i) & vbTab & CPUMaxClockSpeed(i) & vbTab & CPUName(i) & vbTab & CPUNumberOfCores(i) & vbTab & CPUNumberOfLogicalProcessors(i) & vbTab & CPUProcessorId(i) & vbTab & CPUSocketDesignation(i)
Next

MEMd = (MEMIndex+1) & vbcrlf & "MEMBankLabel" & vbTab & "MEMCapacity" & vbTab & "MEMDeviceLocator" & vbTab & "MEMManufacturer" & vbTab & "MEMMemoryType" & vbTab & "MEMPartNumber" & vbTab & "MEMSerialNumber" & vbTab & "MEMSpeed" & vbTab & "MEMTag"
For i=0 to (MEMIndex)
MEMd = MEMd & vbcrlf & MEMBankLabel(i) & vbTab & MEMCapacity(i) & vbTab & MEMDeviceLocator(i) & vbTab & MEMManufacturer(i) & vbTab & MEMMemoryType(i) & vbTab & MEMPartNumber(i) & vbTab & MEMSerialNumber(i) & vbTab & MEMSpeed(i) & vbTab & MEMTag(i)
Next

NICd = (NICIndex+1) & vbcrlf & "NICCaption" & vbTab & "NICDHCPEnabled" & vbTab & "NICDNSHostName" & vbTab & "NICIPAddress" & vbTab & "NICMACAddress" & vbTab & "NICServiceName"
For i=0 to (NICIndex)
NICd = NICd & vbcrlf & NICCaption(i) & vbTab & NICDHCPEnabled(i) & vbTab & NICDNSHostName(i) & vbTab & NICIPAddress(i) & vbTab & NICMACAddress(i) & vbTab & NICServiceName(i)
Next

Diskd = (DiskIndex+1) & vbcrlf & "DiskCaption" & vbTab & "DiskFirmwareRevision" & vbTab & "DiskInterfaceType" & vbTab & "DiskMediaType" & vbTab & "DiskModel" & vbTab & "DiskSerialNumber" & vbTab & "DiskSignature" & vbTab & "DiskSize"
For i=0 to (DiskIndex)
Diskd = Diskd & vbcrlf & DiskCaption(i) & vbTab & DiskFirmwareRevision(i) & vbTab & DiskInterfaceType(i) & vbTab & DiskMediaType(i) & vbTab & DiskModel(i) & vbTab & DiskSerialNumber(i) & vbTab & DiskSignature(i) & vbTab & DiskSize(i)
Next

CDROMd = (CDROMIndex+1) & vbcrlf & "CDROMCaption" & vbTab & "CDROMMediaType" & vbTab & "CDROMMfrAssignedRevisionLevel" & vbTab & "CDROMName"
For i=0 to (CDROMIndex)
CDROMd = CDROMd & vbcrlf & CDROMCaption(i) & vbTab & CDROMMediaType(i) & vbTab & CDROMMfrAssignedRevisionLevel(i) & vbTab & CDROMName(i)
Next

OSd = (OSIndex+1) & vbcrlf & "OSBuildNumber" & vbTab & "OSCaption" & vbTab & "OSCodeSet" & vbTab & "OSCountryCode" & vbTab & "OSCSDVersion" & vbTab & "OSCSName" & vbTab & "OSCurrentTimeZone" & vbTab & "OSInstallDate" & vbTab & "OSLastBootUpTime" & vbTab & "OSLocale" & vbTab & "OSManufacturer" & vbTab & "OSOperatingSystemSKU" & vbTab & "OSOSArchitecture" & vbTab & "OSOSLanguage" & vbTab & "OSOSProductSuite" & vbTab & "OSOSType" & vbTab & "OSOtherTypeDescription" & vbTab & "OSProductKey" & vbTab & "OSProductType" & vbTab & "OSRegisteredUser" & vbTab & "OSSerialNumber" & vbTab & "OSVersion"
For i=0 to (OSIndex)
OSd = OSd & vbcrlf & OSBuildNumber(i) & vbTab & OSCaption(i) & vbTab & OSCodeSet(i) & vbTab & OSCountryCode(i) & vbTab & OSCSDVersion(i) & vbTab & OSCSName(i) & vbTab & OSCurrentTimeZone(i) & vbTab & OSInstallDate(i) & vbTab & OSLastBootUpTime(i) & vbTab & OSLocale(i) & vbTab & OSManufacturer(i) & vbTab & OSOperatingSystemSKU(i) & vbTab & OSOSArchitecture(i) & vbTab & OSOSLanguage(i) & vbTab & OSOSProductSuite(i) & vbTab & OSOSType(i) & vbTab & OSOtherTypeDescription(i) & vbTab & OSProductKey(i) & vbTab & OSProductType(i) & vbTab & OSRegisteredUser(i) & vbTab & OSSerialNumber(i) & vbTab & OSVersion(i)
Next

SoftwareListd = (SoftwareListIndex+1) & vbcrlf & "DisplayName" & vbTab & "DisplayVersion" & vbTab & "Publisher" & vbTab & "InstallDate"
For i=0 to (SoftwareListIndex)
SoftwareListd = SoftwareListd & vbcrlf & SoftwareDisplayName(i) & vbTab & SoftwareDisplayVersion(i) & vbTab & SoftwarePublisher(i) & vbTab & SoftwareInstallDate(i)
Next

OfficeListd = (OfficeListIndex+1) & vbcrlf & "OfficeVersion" & vbTab & "OfficeProductName" & vbTab & "OfficeProductID" & vbTab & "OfficeType" & vbTab & "OfficeProductKey" & vbTab & "OfficeNote"
For i=0 to (OfficeListIndex)
OfficeListd = OfficeListd & vbcrlf & OfficeVersion(i) & vbTab & OfficeProductName(i) & vbTab & OfficeProductID(i) & vbTab & OfficeType(i) & vbTab & OfficeProductKey(i) & vbTab & OfficeNote(i)
Next

echo = 0
If echo = 1 Then
wscript.echo MBd
wscript.echo CPUd
wscript.echo MEMd
wscript.echo NICd
wscript.echo Diskd
wscript.echo CDROMd
wscript.echo OSd
wscript.echo SoftwareListd
wscript.echo OfficeListd
else

Set fso=CreateObject("Scripting.FileSystemObject")
Set StdOut = fso.OpenTextFile(RemoteComputer & ".csv", 2, true, -1)
StdOut.WriteLine MBd & vbcrlf & CPUd & vbcrlf & MEMd & vbcrlf & NICd & vbcrlf & Diskd & vbcrlf & CDROMd & vbcrlf & OSd & vbcrlf & SoftwareListd & vbcrlf & OfficeListd & vbcrlf
StdOut.close
End If

End Function


' ----- End

Function GetMB()
Set BaseBoardItems = objServices.ExecQuery("SELECT * FROM Win32_BaseBoard")
For Each BaseBoardObjItem in BaseBoardItems
MBIndex = MBIndex + 1
ReDim Preserve MBManufacturer(MBIndex)
ReDim Preserve MBProduct(MBIndex)
ReDim Preserve MBSerialNumber(MBIndex)
ReDim Preserve MBVersion(MBIndex)

MBManufacturer(MBIndex) = NullToEmpty(BaseBoardObjItem.Manufacturer)
MBProduct(MBIndex) = NullToEmpty(BaseBoardObjItem.Product)
MBSerialNumber(MBIndex) = NullToEmpty(BaseBoardObjItem.SerialNumber)
MBVersion(MBIndex) = NullToEmpty(BaseBoardObjItem.Version)
Next
End Function

Function GetCPU()
Set CPUItems = objServices.ExecQuery("SELECT * FROM Win32_Processor")
For Each CPU in CPUItems
CPUIndex = CPUIndex + 1
ReDim Preserve CPUAddressWidth(CPUIndex)
ReDim Preserve CPUArchitecture(CPUIndex)
ReDim Preserve CPUCaption(CPUIndex)
ReDim Preserve CPUDataWidth(CPUIndex)
ReDim Preserve CPUDeviceID(CPUIndex)
ReDim Preserve CPUExtClock(CPUIndex)
ReDim Preserve CPUFamily(CPUIndex)
ReDim Preserve CPULevel(CPUIndex)
ReDim Preserve CPUManufacturer(CPUIndex)
ReDim Preserve CPUMaxClockSpeed(CPUIndex)
ReDim Preserve CPUName(CPUIndex)
ReDim Preserve CPUNumberOfCores(CPUIndex)
ReDim Preserve CPUNumberOfLogicalProcessors(CPUIndex)
ReDim Preserve CPUProcessorId(CPUIndex)
ReDim Preserve CPUSocketDesignation(CPUIndex)


CPUAddressWidth(CPUIndex) = NullToEmpty(CPU.AddressWidth) ' OS x86 / x64
CPUArchitecture(CPUIndex) = NullToEmpty(CPU.Architecture)
CPUCaption(CPUIndex) = NullToEmpty(CPU.Caption)
CPUDataWidth(CPUIndex) = NullToEmpty(CPU.DataWidth) ' CPU x86 / x64
CPUDeviceID(CPUIndex) = NullToEmpty(CPU.DeviceID)
CPUExtClock(CPUIndex) = NullToEmpty(CPU.ExtClock)
CPUFamily(CPUIndex) = NullToEmpty(CPU.Family)
CPULevel(CPUIndex) = NullToEmpty(CPU.Level)
CPUManufacturer(CPUIndex) = NullToEmpty(CPU.Manufacturer)
CPUMaxClockSpeed(CPUIndex) = NullToEmpty(CPU.MaxClockSpeed)
CPUName(CPUIndex) = NullToEmpty(CPU.Name)

CPUNumberOfCores(CPUIndex) = NullToEmpty(CPU.NumberOfCores)
' old os not support (2003)

CPUNumberOfLogicalProcessors(CPUIndex) = NullToEmpty(CPU.NumberOfLogicalProcessors)
CPUProcessorId(CPUIndex) = NullToEmpty(CPU.ProcessorId)
CPUSocketDesignation(CPUIndex) = NullToEmpty(CPU.SocketDesignation)
Next
End Function

Function GetMEM()
Set MemoryItems = objServices.ExecQuery("SELECT * FROM Win32_PhysicalMemory")
For Each Memory in MemoryItems
MEMIndex = MEMIndex + 1
ReDim Preserve MEMBankLabel(MEMIndex)
ReDim Preserve MEMCapacity(MEMIndex)
ReDim Preserve MEMDeviceLocator(MEMIndex)
ReDim Preserve MEMManufacturer(MEMIndex)
ReDim Preserve MEMMemoryType(MEMIndex)
ReDim Preserve MEMPartNumber(MEMIndex)
ReDim Preserve MEMSerialNumber(MEMIndex)
ReDim Preserve MEMSpeed(MEMIndex)
ReDim Preserve MEMTag(MEMIndex)

MEMBankLabel(MEMIndex) = NullToEmpty(Memory.BankLabel)
MEMCapacity(MEMIndex) = NullToEmpty(Fix(Memory.Capacity/1048576))
MEMDeviceLocator(MEMIndex) = NullToEmpty(Memory.DeviceLocator)
MEMManufacturer(MEMIndex) = NullToEmpty(Memory.Manufacturer)
MEMMemoryType(MEMIndex) = NullToEmpty(Memory.MemoryType)
MEMPartNumber(MEMIndex) = NullToEmpty(Memory.PartNumber)
MEMSerialNumber(MEMIndex) = NullToEmpty(Memory.SerialNumber)
MEMSpeed(MEMIndex) = NullToEmpty(Memory.Speed)
MEMTag(MEMIndex) = NullToEmpty(Memory.Tag)
Next
End Function


Function GetNIC()
Set NetworkAdapterItems = objServices.ExecQuery("Select * From Win32_NetworkAdapterConfiguration")
For Each NetworkAdapter in NetworkAdapterItems
If (VarType(NetworkAdapter.MACAddress) <> 1) Then
NICIndex = NICIndex + 1
ReDim Preserve NICCaption(NICIndex)
ReDim Preserve NICDHCPEnabled(NICIndex)
ReDim Preserve NICDNSHostName(NICIndex)
ReDim Preserve NICIPAddress(NICIndex)
ReDim Preserve NICMACAddress(NICIndex)
ReDim Preserve NICServiceName(NICIndex)

NICCaption(NICIndex) = NullToEmpty(Mid(NetworkAdapter.Caption,12,Len(NetworkAdapter.Caption)))
If (NetworkAdapter.DHCPEnabled = True) Then
NICDHCPEnabled(NICIndex) = 1
else
NICDHCPEnabled(NICIndex) = 0
End If
NICDNSHostName(NICIndex) = NullToEmpty(NetworkAdapter.DNSHostName)
If (NICDNSHostName(NICIndex) <> "") Then
AllInfoDNSHostName = NICDNSHostName(NICIndex)
End If
If (vartype(NetworkAdapter.IPAddress) = 8204) Then
For Each IPAddress in NetworkAdapter.IPAddress
If Not Instr(IPAddress,":") > 0 Then
If (NICIPAddress(NICIndex) <> "") Then
NICIPAddress(NICIndex) = NICIPAddress(NICIndex) & ";"
End If
NICIPAddress(NICIndex) = NICIPAddress(NICIndex) & IPAddress
End If
Next
else
NICIPAddress(NICIndex) = IPAddress
End If
NICIPAddress(NICIndex) = NullToEmpty(NICIPAddress(NICIndex))
NICMACAddress(NICIndex) = NullToEmpty(NetworkAdapter.MACAddress)
NICServiceName(NICIndex) = NullToEmpty(NetworkAdapter.ServiceName)
End If
Next
End Function

Function GetDisk()

' Vista & 2008 (含)以上版本才有這兩項
DiskFWR = 0
DiskSN = 0
Set DiskClass = objServices.Get("Win32_DiskDrive")
For Each DiskClassProperty In DiskClass.Properties_
If (UCase(DiskClassProperty.Name) = UCase("DiskFirmwareRevision")) Then
DiskFWR = 1
End If
If (UCase(DiskClassProperty.Name) = UCase("DiskSerialNumber")) Then
DiskSN = 1
End If
Next

Set DiskItems = objServices.ExecQuery("SELECT * FROM Win32_DiskDrive")
For Each Disk in DiskItems
DiskIndex = DiskIndex + 1
ReDim Preserve DiskCaption(DiskIndex)
ReDim Preserve DiskFirmwareRevision(DiskIndex)
ReDim Preserve DiskInterfaceType(DiskIndex)
ReDim Preserve DiskMediaType(DiskIndex)
ReDim Preserve DiskModel(DiskIndex)
ReDim Preserve DiskSerialNumber(DiskIndex)
ReDim Preserve DiskSignature(DiskIndex)
ReDim Preserve DiskSize(DiskIndex)

DiskCaption(DiskIndex) = NullToEmpty(Disk.Caption)
If (DiskFWR = 1) Then
DiskFirmwareRevision(DiskIndex) = NullToEmpty(Disk.FirmwareRevision)
else
DiskFirmwareRevision(DiskIndex) = ""
End If
DiskInterfaceType(DiskIndex) = NullToEmpty(Disk.InterfaceType)
DiskMediaType(DiskIndex) = NullToEmpty(Disk.MediaType)
DiskModel(DiskIndex) = NullToEmpty(Disk.Model)
If (DiskSN = 1) Then
DiskSerialNumber(DiskIndex) = NullToEmpty(Disk.SerialNumber)
else
DiskSerialNumber(DiskIndex) = ""
End If
DiskSignature(DiskIndex) = NullToEmpty(Disk.Signature)
DiskSize(DiskIndex) = NullToEmpty(Fix(Disk.Size/1000000000))
Next
End Function

Function GetCDROM()
Set CDROMItems = objServices.ExecQuery("SELECT * FROM Win32_CDROMDrive")
For Each CDROM in CDROMItems
CDROMIndex = CDROMIndex + 1
ReDim Preserve CDROMCaption(CDROMIndex)
ReDim Preserve CDROMMediaType(CDROMIndex)
ReDim Preserve CDROMMfrAssignedRevisionLevel(CDROMIndex)
ReDim Preserve CDROMName(CDROMIndex)

CDROMCaption(CDROMIndex) = NullToEmpty(CDROM.Caption)
CDROMMediaType(CDROMIndex) = NullToEmpty(CDROM.MediaType)
CDROMMfrAssignedRevisionLevel(CDROMIndex) = NullToEmpty(CDROM.MfrAssignedRevisionLevel)
CDROMName(CDROMIndex) = NullToEmpty(CDROM.Name)
Next
End Function

Function GetOS()

' 取得 Product Key
Set objSWbemServices = objLocator.ConnectServer(RemoteComputer,"root\default", AuthUserName,AuthPassword)
objSWbemServices.Security_.ImpersonationLevel = 3
Set objReg = objSWbemServices.Get("stdregprov")
objReg.GetBinaryValue HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\Windows NT\CurrentVersion","DigitalProductId",OSDigitalProductId


' Vista & 2008 (含)以上版本才有這兩項
OSSKU = 0
OSArch = 0

Set OSClass = objServices.Get("Win32_OperatingSystem")
For Each OSClassProperty In OSClass.Properties_
If (UCase(OSClassProperty.Name) = UCase("OperatingSystemSKU")) Then
OSSKU = 1
End If
If (UCase(OSClassProperty.Name) = UCase("OSArchitecture")) Then
OSArch = 1
End If
Next

Set OperatingSystemItems = objServices.ExecQuery("SELECT * FROM Win32_OperatingSystem")
For Each OperatingSystem in OperatingSystemItems
OSIndex = OSIndex + 1
ReDim Preserve OSBuildNumber(OSIndex)
ReDim Preserve OSCaption(OSIndex)
ReDim Preserve OSCodeSet(OSIndex)
ReDim Preserve OSCountryCode(OSIndex)
ReDim Preserve OSCSDVersion(OSIndex)
ReDim Preserve OSCSName(OSIndex)
ReDim Preserve OSCurrentTimeZone(OSIndex)
ReDim Preserve OSInstallDate(OSIndex)
ReDim Preserve OSLastBootUpTime(OSIndex)
ReDim Preserve OSLocale(OSIndex)
ReDim Preserve OSManufacturer(OSIndex)
ReDim Preserve OSOperatingSystemSKU(OSIndex)
ReDim Preserve OSOSArchitecture(OSIndex)
ReDim Preserve OSOSLanguage(OSIndex)
ReDim Preserve OSOSProductSuite(OSIndex)
ReDim Preserve OSOSType(OSIndex)
ReDim Preserve OSOtherTypeDescription(OSIndex)
ReDim Preserve OSProductKey(OSIndex)
ReDim Preserve OSProductType(OSIndex)
ReDim Preserve OSRegisteredUser(OSIndex)
ReDim Preserve OSSerialNumber(OSIndex)
ReDim Preserve OSVersion(OSIndex)

OSBuildNumber(OSIndex) = NullToEmpty(OperatingSystem.BuildNumber)
OSCaption(OSIndex) = NullToEmpty(OperatingSystem.Caption)
OSCodeSet(OSIndex) = NullToEmpty(OperatingSystem.CodeSet)
OSCountryCode(OSIndex) = NullToEmpty(OperatingSystem.CountryCode)
OSCSDVersion(OSIndex) = NullToEmpty(OperatingSystem.CSDVersion)
OSCSName(OSIndex) = NullToEmpty(OperatingSystem.CSName)
If (AllInfoDNSHostName = "") Then
AllInfoDNSHostName = OSCSName(OSIndex)
End If
OSCurrentTimeZone(OSIndex) = NullToEmpty(OperatingSystem.CurrentTimeZone)
OSInstallDate(OSIndex) = DateTimeFormat(OperatingSystem.InstallDate)
OSInstallDate(OSIndex) = Replace(OSInstallDate(OSIndex)," AM","")
OSInstallDate(OSIndex) = Replace(OSInstallDate(OSIndex)," PM","")
OSInstallDate(OSIndex) = Replace(OSInstallDate(OSIndex)," 上午","")
OSInstallDate(OSIndex) = Replace(OSInstallDate(OSIndex)," 下午","")
OSLastBootUpTime(OSIndex) = DateTimeFormat(OperatingSystem.LastBootUpTime)
OSLastBootUpTime(OSIndex) = Replace(OSLastBootUpTime(OSIndex)," AM","")
OSLastBootUpTime(OSIndex) = Replace(OSLastBootUpTime(OSIndex)," PM","")
OSLastBootUpTime(OSIndex) = Replace(OSLastBootUpTime(OSIndex)," 上午","")
OSLastBootUpTime(OSIndex) = Replace(OSLastBootUpTime(OSIndex)," 下午","")
OSLocale(OSIndex) = NullToEmpty(OperatingSystem.Locale)
OSManufacturer(OSIndex) = NullToEmpty(OperatingSystem.Manufacturer)
If (OSSKU = 1) Then
OSOperatingSystemSKU(OSIndex) = NullToEmpty(OperatingSystem.OperatingSystemSKU)
else
OSOperatingSystemSKU(OSIndex) = ""
End If
If (OSArch = 1) Then
OSOSArchitecture(OSIndex) = NullToEmpty(OperatingSystem.OSArchitecture)
else
OSOSArchitecture(OSIndex) = ""
End If
OSOSLanguage(OSIndex) = NullToEmpty(OperatingSystem.OSLanguage)
OSOSProductSuite(OSIndex) = NullToEmpty(OperatingSystem.OSProductSuite)
OSOSType(OSIndex) = NullToEmpty(OperatingSystem.OSType)
OSOtherTypeDescription(OSIndex) = NullToEmpty(OperatingSystem.OtherTypeDescription)
OSProductKey(OSIndex) = NullToEmpty(DecodeOSKey(OSDigitalProductId))
OSProductType(OSIndex) = NullToEmpty(OperatingSystem.ProductType)
OSRegisteredUser(OSIndex) = NullToEmpty(OperatingSystem.RegisteredUser)
OSSerialNumber(OSIndex) = NullToEmpty(OperatingSystem.SerialNumber)
OSVersion(OSIndex) = NullToEmpty(OperatingSystem.Version)
Next
End Function

Function DateTimeFormat(DateTime)
ReturnValue = NullToEmpty(DateAdd("n",Cint(Mid(DateTime,22,Len(DateTime))),Mid(DateTime,1,4) & "/" & Mid(DateTime,5,2) & "/" & Mid(DateTime,7,2) & " " & Mid(DateTime,9,2) & ":" & Mid(DateTime,11,2) & ":" & Mid(DateTime,13,2)))
ReturnValue = Replace(OSLastBootUpTime(OSIndex)," AM","")
ReturnValue = Replace(OSLastBootUpTime(OSIndex)," PM","")
ReturnValue = Replace(OSLastBootUpTime(OSIndex)," 上午","")
ReturnValue = Replace(OSLastBootUpTime(OSIndex)," 下午","")
DateTimeFormat = ReturnValue
End Function

Function DecodeOSKey(key)
Const KeyOffset = 52
i = 28
Chars = "BCDFGHJKMPQRTVWXY2346789"
Do
Cur = 0
x = 14
Do
Cur = Cur * 256
Cur = key(x + KeyOffset) + Cur
key(x + KeyOffset) = (Cur \ 24) And 255
Cur = Cur Mod 24
x = x - 1
Loop While x >= 0
i = i - 1
KeyOutput = Mid(Chars, Cur + 1, 1) & KeyOutput
If (((29 - i) Mod 6) = 0) And (i <> -1) Then
i = i - 1
KeyOutput = "-" & KeyOutput
End If
Loop While i >= 0
DecodeOSKey = KeyOutput
End Function


Function GetSoftwareList(SystemBit)

objCtx.Add "__ProviderArchitecture", SystemBit
objCtx.Add "__RequiredArchitecture", TRUE
Set objServices = objLocator.ConnectServer(RemoteComputer,"root\default",AuthUserName,AuthPassword,,,,objCtx)
Set objStdRegProv = objServices.Get("StdRegProv")

Set Inparams = objStdRegProv.Methods_("EnumKey").Inparameters
Inparams.Hdefkey = HKEY_LOCAL_MACHINE
Inparams.Ssubkeyname = "Software\Microsoft\Windows\CurrentVersion\Uninstall\"

set Outparams = objStdRegProv.ExecMethod_("EnumKey", Inparams,,objCtx)

For Each strSubKey In Outparams.snames

isSystemComponent = 0

Set Inparams = objStdRegProv.Methods_("GetStringValue").Inparameters
Inparams.Hdefkey = HKEY_LOCAL_MACHINE
Inparams.Ssubkeyname = "Software\Microsoft\Windows\CurrentVersion\Uninstall\" & strSubKey

Inparams.Svaluename = "DisplayName"
set Outparams = objStdRegProv.ExecMethod_("GetStringValue", Inparams,,objCtx)
strDisplayName = Outparams.SValue

If (strDisplayName = "") Then
strDisplayName = strSubKey
End If
strDisplayName = NullToEmpty(strDisplayName)
Inparams.Svaluename = "ParentKeyName"
set Outparams = objStdRegProv.ExecMethod_("GetStringValue", Inparams,,objCtx)
strParentKeyName = Outparams.SValue

Inparams.Svaluename = "SystemComponent"
set Outparams = objStdRegProv.ExecMethod_("GetDWORDValue", Inparams,,objCtx)
strSystemComponent = Outparams.uValue
If (vartype(strSystemComponent) = 3) Then
If (strSystemComponent = 1) Then
isSystemComponent = 1
End If
End If

If ( (strDisplayName <> "") and (vartype(strParentKeyName) = 1) and (isSystemComponent = 0) ) Then
Inparams.Svaluename = "DisplayVersion"
set Outparams = objStdRegProv.ExecMethod_("GetStringValue", Inparams,,objCtx)
strDisplayVersion = NullToEmpty(Outparams.SValue)

Inparams.Svaluename = "Publisher"
set Outparams = objStdRegProv.ExecMethod_("GetStringValue", Inparams,,objCtx)
strPublisher = NullToEmpty(Outparams.SValue)

Inparams.Svaluename = "InstallDate"
set Outparams = objStdRegProv.ExecMethod_("GetStringValue", Inparams,,objCtx)
strInstallDate = NullToEmpty(Outparams.SValue)

AddToList = 1
For i=0 to (SoftwareListIndex)
If ( (SoftwareDisplayName(i) = strDisplayName) and (SoftwareDisplayVersion(i) = strDisplayVersion) and (SoftwarePublisher(i) = strPublisher) and (SoftwareInstallDate(i) = strInstallDate) ) Then
AddToList = 0
exit for
End If
Next

strInstallDate = NullToEmpty(strInstallDate)
If ( (Len(strInstallDate) <> 0) and (Len(strInstallDate) <> 8) ) Then
strInstallDate= ""
End If

If (AddToList = 1) Then
SoftwareListIndex = SoftwareListIndex + 1
ReDim Preserve SoftwareDisplayName(SoftwareListIndex)
ReDim Preserve SoftwareDisplayVersion(SoftwareListIndex)
ReDim Preserve SoftwarePublisher(SoftwareListIndex)
ReDim Preserve SoftwareInstallDate(SoftwareListIndex)

SoftwareDisplayName(SoftwareListIndex) = strDisplayName
SoftwareDisplayVersion(SoftwareListIndex) = strDisplayVersion
SoftwarePublisher(SoftwareListIndex) = strPublisher
SoftwareInstallDate(SoftwareListIndex) = strInstallDate
End If
End If
Next

End Function


Function GetOfficeList()

aOffID(0,0) = "XP"
aOffID(0,1) = "10.0"
aOffID(1,0) = "2003"
aOffID(1,1) = "11.0"
aOffID(2,0) = "2007"
aOffID(2,1) = "12.0"
aOffID(3,0) = "2010"
aOffID(3,1) = "14.0"
aOffID(4,0) = "2013"
aOffID(4,1) = "15.0"

Set oReg = objLocator.ConnectServer(RemoteComputer,"root\default",AuthUserName,AuthPassword,,,,objCtx).Get("StdRegProv")

objCtx.Add "__ProviderArchitecture", 64
osType = 32
oReg.GetStringValue HKEY_LOCAL_MACHINE, "SYSTEM\CurrentControlSet\Control\Session Manager\Environment", "PROCESSOR_ARCHITECTURE", osProc
If osProc = "AMD64" Then osType = 64

wow = ""
If osType = "64" Then wow = "WOW6432Node\"
schKey97 "SOFTWARE\" & wow & "Microsoft\"
schKey2K "Office", "SOFTWARE\" & wow & "Microsoft\Office\9.0\", Array("0000","0001","0002","0003","0004","0010","0011","0012","0013","0014","0016","0017","0018","001A","004F"), "78E1-11D2-B60F-006097C998E7"
schKey2K "Visio", "SOFTWARE\" & wow & "Microsoft\Visio\6.0\", Array("B66F45DC"), "853B-11D3-83DE-00C04F3223C8"

For aOffIDX = LBound(aOffID, 1) To UBound(aOffID, 1)
 schKey "SOFTWARE\Wow6432Node\Microsoft\Office\" & aOffID(aOffIDX,1) & "\Registration", false
 schKey "SOFTWARE\Microsoft\Office\" & aOffID(aOffIDX,1) & "\Registration", true
Next
End Function

Sub schKey97(regKey)
 oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey & "Office\8.0", "BinDirPath", oDir97
 If IsNull(oDir97) Then Exit Sub
 oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey & "Microsoft Reference\BookshelfF\96L", "PID", oProdID
 oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey & "Windows\CurrentVersion\Uninstall\Office8.0", "DisplayName", oProd
 oInstall = "1"
 If IsNull(oProd) Then
oInstall = "0"
oProd = "Microsoft Office 97"
 End If
 writeXML "97",oProd,oProdID,32,"",oInstall,"",""
End Sub

Sub schKey2K(name, regKey, guid1, guid2)
 oProd = Null
 oInstall = "0"
 oReg.GetBinaryValue HKEY_LOCAL_MACHINE, regKey & "Registration\DigitalProductID", "", aDPIDBytes
 oKey = ""
 If Not IsNull(aDPIDBytes) Then oKey = DecodeOfficeKey(aDPIDBytes)

 oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey & "Registration\ProductID", "", oProdID
 If IsNull(oProdID) Then Exit Sub

 oReg.EnumKey HKEY_LOCAL_MACHINE, "Software\" & wow & "Microsoft\Windows\CurrentVersion\Uninstall\", aKeys
 If Not IsNull(aKeys) Then
For Each guid In aKeys
 If UCase(Right(guid,Len(guid)-InStr(guid,"-"))) = guid2 & "}" Then
For i = LBound(guid1) To UBound(guid1)
 If UCase(Left(guid,Len(guid1(i)) + 1)) = "{" & guid1(i) Then
oReg.GetStringValue HKEY_LOCAL_MACHINE, "Software\" & wow & "Microsoft\Windows\CurrentVersion\Uninstall\" & guid, "DisplayName", oProd
oGUID = guid
oInstall = "1"
 End If
Next
 End If
Next
 End If

 If IsNull(oProd) Then oProd = "Microsoft " & name & " 2000"
 writeXML "2000",oProd,oProdID,32,oGUID,oInstall,oKey,""
End Sub

Sub schKey(regKey, likeOS)
 oReg.GetBinaryValue HKEY_LOCAL_MACHINE, regKey, "DigitalProductID", aDPIDBytes
 If IsNull(aDPIDBytes) Then
oReg.EnumKey HKEY_LOCAL_MACHINE, regKey, aGUIDKeys
If Not IsNull(aGUIDKeys) Then
 For Each GUIDKey In aGUIDKeys
schKey regKey & "\" & GUIDKey, likeOS
 Next
End If
 Else
oVer = aOffID(aOffIDX,0)
oProd = Null
oKey = DecodeOfficeKey(aDPIDBytes)
oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey, "ProductID", oProdID
oBit = osType
If Not likeOS Then oBit = 32
oGUID = Right(regKey,InStr(StrReverse(regKey),"\")-1)
oInstall = "1"
wow = ""
If Not likeOS Then wow = "WOW6432Node\"

oEdit = ""
If (oVer = "2010" Or oVer = "2013") Then
 For i = 280 to 320 Step 2
If aDPIDBytes(i) <> 0 Then oEdit = oEdit & Chr(aDPIDBytes(i))
 Next
End If
oNote = oEdit

If IsNull(oProd) And (oVer = "2010" Or oVer = "2013") Then
 kEdit = UCase(oEdit)
 If Mid(oGUID,11,4) = "003D" Then kEdit = "SingleImage"
 oReg.GetStringValue HKEY_LOCAL_MACHINE, "Software\" & wow & "Microsoft\Windows\CurrentVersion\Uninstall\Office" & Left(aOffID(aOffIDX,1),2) & "." & kEdit, "DisplayName", oProd
End If

If IsNull(oProd) Then _
 oReg.GetStringValue HKEY_LOCAL_MACHINE, "Software\" & wow & "Microsoft\Windows\CurrentVersion\Uninstall\" & oGUID, "DisplayName", oProd

If IsNull(oProd) Then
 oInstall = "0"
 oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey, "ProductName", oProd
 If IsNull(oProd) Then oReg.GetStringValue HKEY_LOCAL_MACHINE, regKey, "ConvertToEdition", oProd

 ' Office Visio XP
 If IsNull(oProd) And (oVer = "XP") Then
oReg.GetStringValue HKEY_LOCAL_MACHINE, "Software\" & wow & "Microsoft\Office\XP\Common\ProductVersion", "LastProduct", pVer
' Original / SP1 / SP2
If ((pVer = "10.0.525") Or (pVer = "10.1.2514") Or (pVer = "10.2.5110")) Then
 oProd = "Microsoft Office Visio XP"
End If
 End If

 ' Office Visio Viewer 2003
 If IsNull(oProd) And (oVer = "2003") And (oKey = "MF4QD-3T4PM-26X66-4KH7R-QGTYT") Then
oProd = "Microsoft Office Visio Viewer 2003"
 End If

 If IsNull(oProd) Then oProd = "UnidentIfiable Office " & oVer
End If
writeXML oVer,oProd,oProdID,oBit,oGUID,oInstall,oKey,oNote
 End If
End Sub

Sub writeXML(oVer,oProd,oProdID,oBit,oGUID,oInstall,oKey,oNote)
OfficeListIndex = OfficeListIndex + 1
ReDim Preserve OfficeVersion(OfficeListIndex)
ReDim Preserve OfficeProductName(OfficeListIndex)
ReDim Preserve OfficeProductID(OfficeListIndex)
ReDim Preserve OfficeType(OfficeListIndex)
ReDim Preserve OfficeProductKey(OfficeListIndex)
ReDim Preserve OfficeNote(OfficeListIndex)

OfficeVersion(OfficeListIndex) = NullToEmpty(oVer)
OfficeProductName(OfficeListIndex) = NullToEmpty(oProd)
OfficeProductID(OfficeListIndex) = NullToEmpty(oProdID)
OfficeType(OfficeListIndex) = NullToEmpty(oBit)
OfficeProductKey(OfficeListIndex) = NullToEmpty(oKey)
OfficeNote(OfficeListIndex) = NullToEmpty(oNote)
End Sub

Function DecodeOfficeKey(iValues)
 Dim arrDPID, foundKeys
 arrDPID = Array()
 foundKeys = Array()

 Select Case (UBound(iValues))
Case 255:  ' 2000
 range = Array(52,66)
Case 163:  ' XP, 2003, 2007
 range = Array(52,66)
Case 1271: ' 2010, 2013
 range = Array(808,822)
Case Else
 Exit Function
 End Select

 charset = "BCDFGHJKMPQRTVWXY2346789"

 For i = range(0) to range(1)
ReDim Preserve arrDPID( UBound(arrDPID) + 1 )
arrDPID( UBound(arrDPID) ) = iValues(i)
 Next

 withN = (arrDPID(UBound(arrDPID)) \ 6) And 1
 arrDPID(UBound(arrDPID)) = (arrDPID(UBound(arrDPID)) And &HF7) Or ((withN And 2) * 4)

 For i = 24 To 0 Step -1
k = 0
For j = 14 To 0 Step -1
 k = k * 256 Xor arrDPID(j)
 arrDPID(j) = k \ 24
 k = k Mod 24
Next
strProductKey = Mid(charset, k+1, 1) & strProductKey
 Next

 If (withN = 1) Then
keypart = Mid(strProductKey,2,k)
strProductKey = Replace(strProductKey, keypart, keypart & "N", 2, 1, 0)
If k = 0 Then strProductKey = "N" & strProductKey
 End If

 DecodeOfficeKey = ""
 For i = 1 To 25
DecodeOfficeKey = DecodeOfficeKey & Mid(strProductKey,i,1)
If i Mod 5 = 0 And i <> 25 Then DecodeOfficeKey = DecodeOfficeKey & "-"
 Next
End Function

Function NullToEmpty(ReturnValue)
If (vartype(ReturnValue) = 1) Then
ReturnValue = ""
End If
NullToEmpty = ReturnValue
End Function

Function InsertAllInfo()
SQLString = "SET NOCOUNT ON; INSERT INTO [AllInfo] ([DateTime],[HostName]) Values (GETDATE(),'" & AllInfoDNSHostName & "'); SELECT @@IDENTITY AS [NewID];"
Set SQLRecordSet = SQLConnection.Execute(SQLString)
InsertAllInfo = SQLRecordSet("NewID")
SQLRecordSet.Close
End Function

Function InsertData(TableName,ColumnName,ColumnValue)
ReturnID = 0
SQLString = ""
For k = 0 to ubound(ColumnName)
If (SQLString = "") Then
SQLString = "Select Top 1 [Serial] from [" & TableName & "] where"
else
SQLString = SQLString & " and "
End If
SQLString = SQLString & " [" & ColumnName(k) & "] = '" & replace(ColumnValue(k),"'","''") & "'"
Next
If (SQLString <> "") Then

SQLRecordSet.Open SQLString,SQLConnection
If not SQLRecordSet.EoF Then
ReturnID = CInt(SQLRecordSet.Fields("Serial"))
SQLRecordSet.close
else
SQLRecordSet.close

SQLString = ""
For k = 0 to ubound(ColumnName)
If (SQLString = "") Then
SQLString = "SET NOCOUNT ON; INSERT INTO [" & TableName & "] ("
else
SQLString = SQLString & ","
End If
SQLString = SQLString & "[" & ColumnName(k) & "]"
Next
SQLString = SQLString & ") Values ("
SQLStringADD = ""
For k = 0 to ubound(ColumnName)
If (SQLStringADD <> "") Then
SQLStringADD = SQLStringADD & ","
End If
SQLStringADD =  SQLStringADD & "'" & replace(ColumnValue(k),"'","''") & "'"
Next
SQLStringADD = SQLStringADD & "); SELECT @@IDENTITY AS [NewID];"
SQLString = SQLString & SQLStringADD
Set SQLRecordSet = SQLConnection.Execute(SQLString)
ReturnID = SQLRecordSet("NewID")
SQLRecordSet.Close
End If
End If
InsertData = ReturnID
End Function

===== InfoSearch.vbs 程式結束 =====


===== SoftwareListReport.vbs 程式開始 =====

SQLServer="localhost"
SQLUser="Info"
SQLPassword="password"
SQLDB="Info"
SQLConnectionTimeout = 600

Dim SQLConnString
Dim SQLConnection
Dim SQLString
Dim SQLRecordSet
Dim DateTimeString

Dim SoftwareCountIndex
Dim SoftwareCountValues()
SoftwareCountIndex = -1

DateTimeString = date & " " & time

Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")
SQLConnString = "DRIVER={SQL Server};SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPassword & ";DATABASE=" & SQLDB

SQLConnection.ConnectionTimeout = SQLConnectionTimeout
SQLConnection.Open SQLConnString

SQLString = "SELECT * FROM [Info].[dbo].[ListSoftware]"
Set SQLRecordSet = SQLConnection.Execute(SQLString)

If not SQLRecordSet.EoF Then
SoftwareCountIndex = SoftwareCountIndex + 1
ReDim Preserve SoftwareCountValues(SoftwareCountIndex)
for i = 1 to 100
SQLString = "INSERT INTO [ListSoftwareReport] ([SoftwareSerial],[Count],[DateTime]) Values "
SQLStringADD = ""
for j = 0 to 500
If not SQLRecordSet.EoF Then
SoftwareSerial = SQLRecordSet("Serial")
If (SQLStringADD <> "") Then
SQLStringADD = SQLStringADD & ","
End If
SQLStringADD = SQLStringADD & "(" & SoftwareSerial & "," & SoftwareListCount(SoftwareSerial) & ",'" & DateTimeString & "')"
SQLRecordSet.movenext
Else
Exit For
End If
Next
If(SQLStringADD <> "") Then
SQLString = SQLString & SQLStringADD & ";"
End If
SoftwareCountValues(SoftwareCountIndex) = SQLString
If SQLRecordSet.EoF Then
Exit For
Else
SoftwareCountIndex = SoftwareCountIndex + 1
ReDim Preserve SoftwareCountValues(SoftwareCountIndex)
end If
Next
end If

for i = 0 to SoftwareCountIndex
Set SQLRecordSet = SQLConnection.Execute(SoftwareCountValues(i))
next

SQLConnection.Close

wscript.quit

Function SoftwareListCount(SoftwareSerial)


FSQLString = ""
FSQLString = FSQLString & " Select Count(Distinct [HostName]) as Count"
FSQLString = FSQLString & " From ("
FSQLString = FSQLString & " Select"
FSQLString = FSQLString & " InfoLink.[InfoSerial],"
FSQLString = FSQLString & " SoftwareLink.[InstallDate]"
FSQLString = FSQLString & " From [Info].[dbo].[AllInfoLink]"
FSQLString = FSQLString & " As InfoLink"
FSQLString = FSQLString & " Right Join ("
FSQLString = FSQLString & " Select *"
FSQLString = FSQLString & " From [Info].[dbo].[ListSoftwareLink]"
FSQLString = FSQLString & " Where [SoftwareSerial] =" & SoftwareSerial
FSQLString = FSQLString & " ) As SoftwareLink"
FSQLString = FSQLString & " On InfoLink.[Type] = 'Software'"
FSQLString = FSQLString & " And InfoLink.[TypeSerial] = SoftwareLink.[Serial]"
FSQLString = FSQLString & " ) As Software"
FSQLString = FSQLString & " Right Join ("
FSQLString = FSQLString & " Select MIN(AllInfoX.[Serial]) As [InfoSerial], AllInfoX.[HostName], AllInfoX.DateTime As [InfoDateTime]"
FSQLString = FSQLString & " From [Info].[dbo].[AllInfo]"
FSQLString = FSQLString & " As AllInfoX"
FSQLString = FSQLString & " INNER JOIN ("
FSQLString = FSQLString & " Select [HostName], MAX(DateTime) As [DateTime]"
FSQLString = FSQLString & " From [Info].[dbo].[AllInfo]"
FSQLString = FSQLString & " GROUP BY [HostName]"
FSQLString = FSQLString & " ) As AllInfoY"
FSQLString = FSQLString & " On AllInfoY.[HostName] = AllInfoX.[HostName]"
FSQLString = FSQLString & " And AllInfoY.[DateTime] = AllInfoX.[DateTime]"
FSQLString = FSQLString & " GROUP BY AllInfoX.[HostName], AllInfoX.[DateTime]"
FSQLString = FSQLString & " ) As HostName"
FSQLString = FSQLString & " On Software.[InfoSerial] = HostName.[InfoSerial]"
FSQLString = FSQLString & " Where Software.[InfoSerial] is not null"

Set FSQLRecordSet = SQLConnection.Execute(FSQLString)

if not FSQLRecordSet.EoF then
ReturnValue = FSQLRecordSet("Count")
end if

FSQLRecordSet.Close

SoftwareListCount = ReturnValue

end function

===== SoftwareListReport.vbs 程式結束 =====


===== SQL Table Create 語法開始 =====


USE [Info]

GO


/****** Object:  Table [dbo].[ListCPU]    Script Date: 11/22/2014 12:12:54 ******/
DROP TABLE [dbo].[ListCPU]
GO

/****** Object:  Table [dbo].[ListCPU]    Script Date: 11/22/2014 12:12:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListCPU](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[AddressWidth] [int] NULL,
[Architecture] [int] NULL,
[Caption] [nvarchar](max) NULL,
[DataWidth] [int] NULL,
[DeviceID] [nvarchar](max) NULL,
[ExtClock] [int] NULL,
[Family] [int] NULL,
[Level] [int] NULL,
[Manufacturer] [nvarchar](max) NULL,
[MaxClockSpeed] [int] NULL,
[Name] [nvarchar](max) NULL,
[NumberOfCores] [int] NULL,
[NumberOfLogicalProcessors] [int] NULL,
[ProcessorId] [nvarchar](max) NULL,
[SocketDesignation] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListCPU] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListCDROM]    Script Date: 11/22/2014 12:12:50 ******/
DROP TABLE [dbo].[ListCDROM]
GO

/****** Object:  Table [dbo].[ListCDROM]    Script Date: 11/22/2014 12:12:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListCDROM](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Caption] [nvarchar](max) NULL,
[MediaType] [nvarchar](max) NULL,
[MfrAssignedRevisionLevel] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListCDROM] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[AllInfoLink]    Script Date: 11/22/2014 12:12:42 ******/
DROP TABLE [dbo].[AllInfoLink]
GO

/****** Object:  Table [dbo].[AllInfoLink]    Script Date: 11/22/2014 12:12:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AllInfoLink](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[InfoSerial] [int] NOT NULL,
[Type] [nchar](10) NOT NULL,
[TypeSerial] [int] NOT NULL,
 CONSTRAINT [PK_AllInfoLink] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[AllInfo]    Script Date: 11/22/2014 12:12:39 ******/
DROP TABLE [dbo].[AllInfo]
GO

/****** Object:  Table [dbo].[AllInfo]    Script Date: 11/22/2014 12:12:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AllInfo](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[DateTime] [datetime] NOT NULL,
[HostName] [nvarchar](50) NULL,
 CONSTRAINT [PK_AllInfo] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListDisk]    Script Date: 11/22/2014 12:12:59 ******/
DROP TABLE [dbo].[ListDisk]
GO

/****** Object:  Table [dbo].[ListDisk]    Script Date: 11/22/2014 12:12:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListDisk](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Caption] [nvarchar](max) NULL,
[FirmwareRevision] [nvarchar](max) NULL,
[InterfaceType] [nvarchar](max) NULL,
[MediaType] [nvarchar](max) NULL,
[Model] [nvarchar](max) NULL,
[SerialNumber] [nvarchar](max) NULL,
[Signature] [int] NULL,
[Size] [int] NULL,
 CONSTRAINT [PK_ListDisk] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListMEM]    Script Date: 11/22/2014 12:13:05 ******/
DROP TABLE [dbo].[ListMEM]
GO

/****** Object:  Table [dbo].[ListMEM]    Script Date: 11/22/2014 12:13:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListMEM](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Capacity] [int] NULL,
[Manufacturer] [nvarchar](max) NULL,
[MemoryType] [int] NULL,
[PartNumber] [nvarchar](max) NULL,
[SerialNumber] [nvarchar](max) NULL,
[Speed] [int] NULL,
 CONSTRAINT [PK_ListMEM] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListMB]    Script Date: 11/22/2014 12:13:02 ******/
DROP TABLE [dbo].[ListMB]
GO

/****** Object:  Table [dbo].[ListMB]    Script Date: 11/22/2014 12:13:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListMB](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Manufacturer] [nvarchar](max) NULL,
[Product] [nvarchar](max) NULL,
[SerialNumber] [nvarchar](max) NULL,
[Version] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListMB] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListMEMLink]    Script Date: 11/22/2014 12:13:08 ******/
DROP TABLE [dbo].[ListMEMLink]
GO

/****** Object:  Table [dbo].[ListMEMLink]    Script Date: 11/22/2014 12:13:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListMEMLink](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[MEMSerial] [int] NULL,
[BankLabel] [nvarchar](max) NULL,
[DeviceLocator] [nvarchar](max) NULL,
[Tag] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListMEMLink] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListNIC]    Script Date: 11/22/2014 12:13:11 ******/
DROP TABLE [dbo].[ListNIC]
GO

/****** Object:  Table [dbo].[ListNIC]    Script Date: 11/22/2014 12:13:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListNIC](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Caption] [nvarchar](max) NULL,
[MACAddress] [nvarchar](max) NULL,
[ServiceName] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListNIC] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListNICLink]    Script Date: 11/22/2014 12:13:14 ******/
DROP TABLE [dbo].[ListNICLink]
GO

/****** Object:  Table [dbo].[ListNICLink]    Script Date: 11/22/2014 12:13:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListNICLink](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[NICSerial] [int] NOT NULL,
[DHCPEnabled] [bit] NULL,
[DNSHostName] [nvarchar](50) NULL,
[IPAddress] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListNICLink] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListOffice]    Script Date: 11/22/2014 12:13:18 ******/
DROP TABLE [dbo].[ListOffice]
GO

/****** Object:  Table [dbo].[ListOffice]    Script Date: 11/22/2014 12:13:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListOffice](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[Version] [nchar](10) NULL,
[ProductName] [nvarchar](max) NULL,
[ProductID] [nvarchar](max) NULL,
[Type] [nchar](10) NULL,
[ProductKey] [nvarchar](max) NULL,
[Note] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListOffice] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListOS]    Script Date: 11/22/2014 12:13:22 ******/
DROP TABLE [dbo].[ListOS]
GO

/****** Object:  Table [dbo].[ListOS]    Script Date: 11/22/2014 12:13:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListOS](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[BuildNumber] [nvarchar](max) NULL,
[Caption] [nvarchar](max) NULL,
[CodeSet] [nvarchar](max) NULL,
[CountryCode] [nvarchar](max) NULL,
[CSDVersion] [nvarchar](max) NULL,
[CurrentTimeZone] [int] NULL,
[Locale] [nvarchar](max) NULL,
[Manufacturer] [nvarchar](max) NULL,
[OperatingSystemSKU] [int] NULL,
[OSArchitecture] [nvarchar](max) NULL,
[OSProductSuite] [int] NULL,
[OSLanguage] [int] NULL,
[OSType] [int] NULL,
[OtherTypeDescription] [nvarchar](max) NULL,
[ProductKey] [nvarchar](max) NULL,
[ProductType] [int] NULL,
[Version] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListOS] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListOSLink]    Script Date: 11/22/2014 12:13:25 ******/
DROP TABLE [dbo].[ListOSLink]
GO

/****** Object:  Table [dbo].[ListOSLink]    Script Date: 11/22/2014 12:13:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListOSLink](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[OSSerial] [int] NOT NULL,
[CSName] [nvarchar](50) NULL,
[RegisteredUser] [nvarchar](max) NULL,
[SerialNumber] [nvarchar](max) NULL,
[InstallDate] [datetime] NULL,
[LastBootUpTime] [datetime] NULL,
 CONSTRAINT [PK_ListOSLink] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListSoftware]    Script Date: 11/22/2014 12:13:28 ******/
DROP TABLE [dbo].[ListSoftware]
GO

/****** Object:  Table [dbo].[ListSoftware]    Script Date: 11/22/2014 12:13:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListSoftware](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[DisplayName] [nvarchar](max) NULL,
[DisplayVersion] [nvarchar](max) NULL,
[Publisher] [nvarchar](max) NULL,
 CONSTRAINT [PK_ListSoftware] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListSoftwareLink]    Script Date: 11/22/2014 12:13:31 ******/
DROP TABLE [dbo].[ListSoftwareLink]
GO

/****** Object:  Table [dbo].[ListSoftwareLink]    Script Date: 11/22/2014 12:13:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListSoftwareLink](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[SoftwareSerial] [int] NOT NULL,
[InstallDate] [datetime] NULL,
 CONSTRAINT [PK_ListSoftwareLink] PRIMARY KEY CLUSTERED 
(
[Serial] 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


USE [Info]
GO

/****** Object:  Table [dbo].[ListSoftwareReport]    Script Date: 11/23/2014 20:44:49 ******/
DROP TABLE [dbo].[ListSoftwareReport]
GO

/****** Object:  Table [dbo].[ListSoftwareReport]    Script Date: 11/23/2014 20:44:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ListSoftwareReport](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[SoftwareSerial] [int] NOT NULL,
[Count] [int] NULL,
[DateTime] [datetime] NULL,
 CONSTRAINT [PK_ListSoftwareReport] PRIMARY KEY CLUSTERED 
(
[Serial] 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

===== SQL Table Create 語法結束 =====


===== 一些 Query 資料的語法 開始 =====


Select

MIN(AllInfoX.[Serial]) As [InfoSerial],

AllInfoX.[HostName],
AllInfoX.DateTime As [InfoDateTime]
From [Info].[dbo].[AllInfo]
As AllInfoX
INNER JOIN (
Select [HostName], MAX(DateTime) As [DateTime]
From [Info].[dbo].[AllInfo]
GROUP BY [HostName]
) As AllInfoY
On AllInfoY.[HostName] = AllInfoX.[HostName]
And AllInfoY.[DateTime] = AllInfoX.[DateTime]
GROUP BY AllInfoX.[HostName], AllInfoX.[DateTime]


Select * From (
Select
MB.[Serial] As [MBSerial],
MB.[Manufacturer] As [MBManufacturer],
MB.[Product] As [MBProduct],
MB.[SerialNumber] As [MBSerialNumber],
MB.[Version] As [MBVersion]
From [Info].[dbo].[ListMB] As MB
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'MB' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On MB.[Serial] = AllInfoLink.[TypeSerial]
) As MBTable

Select * From (
Select
CPU.[AddressWidth] As [CPUAddressWidth],
CPU.[Architecture] As [CPUArchitecture],
CPU.[Caption] As [CPUCaption],
CPU.[DataWidth] As [CPUDataWidth],
CPU.[DeviceID] As [CPUDeviceID],
CPU.[ExtClock] As [CPUExtClock],
CPU.[Family] As [CPUFamily],
CPU.[Level] As [CPULevel],
CPU.[Manufacturer] As [CPUManufacturer],
CPU.[MaxClockSpeed] As [CPUMaxClockSpeed],
CPU.[Name] As [CPUName],
CPU.[NumberOfCores] As [CPUNumberOfCores],
CPU.[NumberOfLogicalProcessors] As [CPUNumberOfLogicalProcessors],
CPU.[ProcessorId] As [CPUProcessorId],
CPU.[SocketDesignation] As [CPUSocketDesignation]
From [Info].[dbo].[ListCPU] As CPU
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'CPU' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On CPU.[Serial] = AllInfoLink.[TypeSerial]
) As CPUTable

Select * From (
Select
MEMLink.[Serial] As [MEMSerial],
MEMLink.[BankLabel] As [MEMBankLabel],
MEMLink.[DeviceLocator] As [MEMDeviceLocator],
MEMLink.[Tag] As [MEMTag],
MEM.[Capacity] As [MEMCapacity],
MEM.[Manufacturer] As [MEMManufacturer],
MEM.[MemoryType] As [MEMMemoryType],
MEM.[PartNumber] As [MEMPartNumber],
MEM.[SerialNumber] As [MEMSerialNumber],
MEM.[Speed] As [MEMSpeed]
From [Info].[dbo].[ListMEMLink] As MEMLink
Right Join (
Select * From [Info].[dbo].[ListMEM]
) As MEM
On MEMLink.[MEMSerial] = MEM.[Serial]
) As MEMTable
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'MEM' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On MEMTable.[MEMSerial] = AllInfoLink.[TypeSerial]


Select * From (
Select
NICLink.[Serial] As [NICSerial],
NICLink.[DHCPEnabled] As [NICDHCPEnabled],
NICLink.[DNSHostName] As [NICDNSHostName],
NICLink.[IPAddress] As [NICIPAddress],
NIC.[Caption] As [NICCaption],
NIC.[MACAddress] As [NICMACAddress],
NIC.[ServiceName] As [NICServiceName]
From [Info].[dbo].[ListNICLink] As NICLink
Right Join (
Select * From [Info].[dbo].[ListNIC]
) As NIC
On NICLink.[NICSerial] = NIC.[Serial]
) As NICTable
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'NIC' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On NICTable.[NICSerial] = AllInfoLink.[TypeSerial]


Select * From (
Select
Disk.[Caption] As [DiskCaption],
Disk.[FirmwareRevision] As [DiskFirmwareRevision],
Disk.[InterfaceType] As [DiskInterfaceType],
Disk.[MediaType] As [DiskMediaType],
Disk.[Model] As [DiskModel],
Disk.[SerialNumber] As [DiskSerialNumber],
Disk.[Signature] As [DiskSignature],
Disk.[Size] As [DiskSize]
From [Info].[dbo].[ListDisk] As Disk
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'Disk' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On Disk.[Serial] = AllInfoLink.[TypeSerial]
) As DiskTable

Select * From (
Select
CDROM.[Caption] As [CDROMCaption],
CDROM.[MediaType] As [CDROMMediaType],
CDROM.[MfrAssignedRevisionLevel] As [CDROMMfrAssignedRevisionLevel],
CDROM.[Name] As [CDROMName]
From [Info].[dbo].[ListCDROM] As CDROM
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'CDROM' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On CDROM.[Serial] = AllInfoLink.[TypeSerial]
) As CDROMTable

Select * From (
Select
OSLink.[Serial] As [OSSerial],
OSLink.[CSName] As [OSCSName],
OSLink.[InstallDate] As [OSInstallDate],
OSLink.[LastBootUpTime] As [OSLastBootUpTime],
OS.[BuildNumber] As [OSBuildNumber],
OS.[Caption] As [OSCaption],
OS.[CodeSet] As [OSCodeSet],
OS.[CountryCode] As [OSCountryCode],
OS.[CSDVersion] As [OSCSDVersion],
OS.[CurrentTimeZone] As [OSCurrentTimeZone],
OS.[Locale] As [OSLocale],
OS.[Manufacturer] As [OSManufacturer],
OS.[OperatingSystemSKU] As [OSOperatingSystemSKU],
OS.[OSArchitecture] As [OSOSArchitecture],
OS.[OSProductSuite] As [OSOSProductSuite],
OS.[OSLanguage] As [OSOSLanguage],
OS.[OSType] As [OSOSType],
OS.[OtherTypeDescription] As [OSOtherTypeDescription],
OS.[ProductKey] As [OSProductKey],
OS.[ProductType] As [OSProductType],
OS.[RegisteredUser] As [OSRegisteredUser],
OS.[SerialNumber] As [OSSerialNumber],
OS.[Version] As [OSVersion]
From [Info].[dbo].[ListOSLink] As OSLink
Right Join (
Select * From [Info].[dbo].[ListOS]
) As OS
On OSLink.[OSSerial] = OS.[Serial]
) As OSTable
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'OS' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On OSTable.[OSSerial] = AllInfoLink.[TypeSerial]


Select * From (
Select
SoftwareLink.[Serial] As [SoftwareLinkSerial],
SoftwareLink.[SoftwareSerial] As [SoftwareSerial],
SoftwareLink.[InstallDate] As [SoftwareInstallDate],
Software.[DisplayName] As [SoftwareDisplayName],
Software.[DisplayVersion] As [SoftwareDisplayVersion],
Software.[Publisher] As [SoftwarePublisher]
From [Info].[dbo].[ListSoftwareLink] As SoftwareLink
Right Join (
Select * From [Info].[dbo].[ListSoftware]
) As Software
On SoftwareLink.[SoftwareSerial] = Software.[Serial]
) As SoftwareTable
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'Software' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On SoftwareTable.[SoftwareLinkSerial] = AllInfoLink.[TypeSerial]
Order by [SoftwareDisplayName]

Select * From (
Select
Office.[Version] As [OfficeVersion],
Office.[ProductName] As [OfficeProductName],
Office.[ProductID] As [OfficeProductID],
Office.[Type] As [OfficeType],
Office.[ProductKey] As [OfficeProductKey],
Office.[Note] As [OfficeNote]
From [Info].[dbo].[ListOffice] As Office
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'Office' And [InfoSerial] = 1
Group By [TypeSerial]
) As AllInfoLink
On Office.[Serial] = AllInfoLink.[TypeSerial]
) As OfficeTable

/****** 查詢某個軟體的安裝現況  ******/

Select Distinct
[HostName],
[InstallDate],
[InfoDateTime]
From (
Select
InfoLink.[InfoSerial],
SoftwareLink.[InstallDate]
From [Info].[dbo].[AllInfoLink]
As InfoLink
Right Join (
Select *
From [Info].[dbo].[ListSoftwareLink]
Where [SoftwareSerial] = 5
) As SoftwareLink
On InfoLink.[Type] = 'Software'
And InfoLink.[TypeSerial] = SoftwareLink.[Serial]
) As Software
Right Join (
Select MIN(AllInfoX.[Serial]) As [InfoSerial], AllInfoX.[HostName], AllInfoX.DateTime As [InfoDateTime]
From [Info].[dbo].[AllInfo]
As AllInfoX
INNER JOIN (
Select [HostName], MAX(DateTime) As [DateTime]
From [Info].[dbo].[AllInfo]
GROUP BY [HostName]
) As AllInfoY
On AllInfoY.[HostName] = AllInfoX.[HostName]
And AllInfoY.[DateTime] = AllInfoX.[DateTime]
GROUP BY AllInfoX.[HostName], AllInfoX.[DateTime]
) As HostName
On Software.[InfoSerial] = HostName.[InfoSerial]
Where Software.[InfoSerial] is not null


/****** 查詢 Office 軟體的安裝現況  ******/

Select * From (
Select
AllInfoLink.[InfoSerial] As [InfoSerial],
Office.[Version] As [OfficeVersion],
Office.[ProductName] As [OfficeProductName],
Office.[ProductID] As [OfficeProductID],
Office.[Type] As [OfficeType],
Office.[ProductKey] As [OfficeProductKey],
Office.[Note] As [OfficeNote]
From [Info].[dbo].[ListOffice] As Office
Right Join (
Select [TypeSerial]
From [Info].[dbo].[AllInfoLink]
Where [Type] = 'Office'
Group By [TypeSerial]
) As AllInfoLink
On Office.[Serial] = AllInfoLink.[TypeSerial]
) As OfficeTable
Right Join (
Select
MIN(AllInfoX.[Serial]) As [InfoSerial],
AllInfoX.[HostName],
AllInfoX.DateTime As [InfoDateTime]
From [Info].[dbo].[AllInfo]
As AllInfoX
INNER JOIN (
Select [HostName], MAX(DateTime) As [DateTime]
From [Info].[dbo].[AllInfo]
GROUP BY [HostName]
) As AllInfoY
On AllInfoY.[HostName] = AllInfoX.[HostName]
And AllInfoY.[DateTime] = AllInfoX.[DateTime]
GROUP BY AllInfoX.[HostName], AllInfoX.[DateTime]
) As HostName
On OfficeTable.[InfoSerial] = HostName.[InfoSerial]
Where OfficeTable.[InfoSerial] is not null


/****** 查詢 SoftwareList Report  ******/


Select SoftwareList.[Serial],
SoftwareList.[DisplayName],
SoftwareList.[DisplayVersion],
SoftwareList.[Publisher],
SoftwareListReport.[Count],
SoftwareListReport.[TopDateTime]
From [Info].[dbo].[ListSoftware]
As SoftwareList
Right Join (
Select [SoftwareSerial],[Count],
SoftwareListReportB.[TopDateTime]
FROM [Info].[dbo].[ListSoftwareReport]
as SoftwareListReportA
Right Join (
Select Top 1 [DateTime] as [TopDateTime]
From [Info].[dbo].[ListSoftwareReport]
order by [DateTime] desc
) As SoftwareListReportB
On SoftwareListReportA.[DateTime] = SoftwareListReportB.[TopDateTime]
) As SoftwareListReport
On SoftwareList.[Serial] = SoftwareListReport.[SoftwareSerial]
Order By [DisplayName]

/****** 查詢 MB Report  ******/


Select
MB.*,
InfoTable.[HostName],
InfoTable.[InfoSerial]
FROM [Info].[dbo].[ListMB] as MB
Right Join (
Select 
AllInfoLink.[InfoSerial] as [InfoSerial],
AllInfoLink.[TypeSerial] as [TypeSerial],
HostList.[HostName]
FROM [Info].[dbo].[AllInfoLink] as AllInfoLink
Right Join (
Select
MIN(AllInfoX.[Serial]) As [InfoSerial],
AllInfoX.[HostName],
AllInfoX.DateTime As [InfoDateTime]
From [Info].[dbo].[AllInfo]
As AllInfoX
INNER JOIN (
Select [HostName], MAX(DateTime) As [DateTime]
From [Info].[dbo].[AllInfo]
GROUP BY [HostName]
) As AllInfoY
On AllInfoY.[HostName] = AllInfoX.[HostName]
And AllInfoY.[DateTime] = AllInfoX.[DateTime]
GROUP BY AllInfoX.[HostName], AllInfoX.[DateTime]
) as HostList
On AllInfoLink.[Type] = 'MB' 
and AllInfoLink.[InfoSerial] = HostList.[InfoSerial]
where AllInfoLink.[TypeSerial] is not null
) as InfoTable
on MB.[Serial] = InfoTable.[TypeSerial]
order by [HostName]

===== 一些 Query 資料的語法 結束 =====

沒有留言: