' --------------------------------------------------------------------------------------------------       
' 2020-08-11
'
' uses    Windows Management Instrumentation
'         WbemScripting.SWbemLocator
' demos access to some local information
' and how to write that information to a SQLite database
' --------------------------------------------------------------------------------------------------       

Newscript

IF  dbopen then
ELSE
   dbopen ("memory")
END IF

dim objLocator = CreateObject("WbemScripting.SWbemLocator")
dim objService = objLocator.ConnectServer(".", "root\cimv2")

' --------------------------------------------------------------------------------------------------
' impersonate, or impersonationLevel=3, is the default process security level

objService.Security_.ImpersonationLevel = 3

' --------------------------------------------------------------------------------------------------
dim objResults

dim tblName

' Select the information that you want here
dim exampleId = 1 


' here we  get the data that we want, use    exampleId   to decide which query is to be executed!
' The results will be written in to a SQLite database table, with the name given by     tblName

IF exampleId ==  1 then
   objResults = objService .InstancesOf("Win32_OperatingSystem")
   tblName = "Win32_OperatingSystem"
ELSEIF exampleId ==  2 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_Environment WHERE Name='TEMP'" , "WQL", 48)
   tblName = "Win32_Environment"
ELSEIF exampleId ==  3 then
   objResults = objService.ExecQuery ("Select * from Win32_NetworkAdapterConfiguration")
   tblName = "Win32_NetworkAdapterConfiguration"
ELSEIF exampleId ==  4 then
   objResults = objService.ExecQuery ("Select * from Win32_ComputerSystem")
   tblName = "Win32_ComputerSystem"
ELSEIF exampleId ==  5 then
   objResults = objService.ExecQuery ("Select * from Win32_Share")
   tblName = "Win32_Share"   
ELSEIF exampleId ==  6 then
   objResults = objService.ExecQuery ("Select * From Win32_Printer  ")
   tblName = "Win32_Printer"   
ELSEIF exampleId ==  7 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_ScheduledJob")
   tblName = "Win32_ScheduledJob"
ELSEIF exampleId ==  8 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_DesktopMonitor")
   tblName = "Win32_DesktopMonitor"
ELSEIF exampleId ==  9 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_Process")
   tblName = "Win32_Process"
ELSEIF exampleId ==  10 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_Service")
   tblName = "Win32_Service"   
ELSEIF exampleId ==  11 then
   objResults = objService.ExecQuery ("SELECT * FROM CIM_LogicalDisk")
   tblName = "CIM_LogicalDisk"   
ELSEIF exampleId ==  12 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_LocalTime")
   tblName = "Win32_LocalTime"   
ELSEIF exampleId ==  13 then
   objResults = objService.ExecQuery ("SELECT * FROM Win32_TimeZone")
   tblName = "Win32_TimeZone"    
ELSE
  stop
END IF

' --------------------------------------------------------------------------------------------------

sql = "drop table if exists " & sqlIdentifier (tblname ) 

' --------------------------------------------------------------------------------------------------

dim enumObj , propObj, index = 0

FOR each enumObj in objResults

   ' if this is the first row of data, then we need to create the appropriate TABLE first.
   ' So build a   Create Table xyz ( "col1", "col2",...)
   ' Note: SQLite does not strictly need a type specification for a column!
   '
   IF  index == 0 then
      freestring = "create table " & sqlIdentifier (tblname ) &  " ( "
      FOR each propObj in  enumObj .properties_  ' important need a space afer the underscore

         IF  index <> 0 then
            freestring := freestring  &  " , "
         END if
         index = 1
         freestring := freestring  &  sqlidentifier (  propObj.name )  & "  " 

      NEXT   propObj
      freestring := freestring  &  "  )"
      
      ' do the CREATE command
      sql = freestring

      index = 1
   END if

   
   ' now build an INSERT INTO xyz ( 'value1', 'value2' ,.... ) command
   index = 0
   freestring = "insert into  "  &  sqlIdentifier (tblname )      &     " values ( "
   FOR each propObj in  enumObj .properties_  ' important need a space afer the underscore

      IF  index <> 0 then
         freestring := freestring  &  " , "
      END if
      index = 1   

      IF runon  freestring :=  freestring  &  sqlstring ( propObj.value )  then
         freestring := freestring  &  " '?'"
      END If

   NEXT   propObj
   freestring := freestring  &  "  )"
   
   ' do the insert command
   sql = freestring
   
NEXT enumObj