但是撈出來的資料很亂, 如今改寫過, 可以正確地抓出 [新增移除程式] 裡面的清單
加上硬體資訊, 然後寫入 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 資料的語法 結束 =====
沒有留言:
張貼留言