OCS Inventory 匯出資料到 Google 試算表(依電腦設備類型)

OCS Inventory 匯出到 Google 試算表的程式是從網路上其他高手分享出來(參考資料出處)，並依個人需求調整。

如要取用請注意，因程式是用 python 寫的，程式段落可能會移位導致無法正常運作。

下列程式調整SELECT電腦設備類型為Notebook寫入試算表分頁。

程式內容:
#coding=utf-8
#-*- coding: utf-8 -*-
import MySQLdb as mysqldb
import sys
import gspread
import string

def DB_return(exec_cmd):
    connection = mysqldb.connect('localhost', 'ocs', 'ocspasswd', 'ocsdb',charset='utf8');
    with connection:
        cursor = connection.cursor()
        cursor.execute(exec_cmd)
        rows_info = cursor.fetchall()
        return rows_info

def GSheet_write(GSpreadSheet,GDriveJSON,tab_data,data,worksheet_name):

    a_list=[]
    for i, element in enumerate(tab_data):
        a_list.append(tab_data[i])
    a_tup = tuple(a_list)

    start_row = 1
    start_letter = 'A'
    end_len = len(data[0]) - 1
    if end_len > 26:
        len_info = end_len - 26
        len_a='A'
    else:
        len_info = end_len
        len_a=''
    end_letter = string.uppercase[len_info]
    end_letter = len_a+end_letter
    end_row = len(data)
    range = "%s%d:%s%d" % (start_letter, start_row+1 , end_letter, end_row+1)
    range_tab =  "%s%d:%s%d" % (start_letter, start_row , end_letter ,start_row)

    from oauth2client.service_account import ServiceAccountCredentials as SAC
    try:
        scope = ['https://spreadsheets.google.com/feeds']
        key = SAC.from_json_keyfile_name(GDriveJSON, scope)
        gc = gspread.authorize(key)
        workbook = gc.open(GSpreadSheet)

        if worksheet_name in [sheet.title for sheet in workbook.worksheets()]:
          workbook.del_worksheet(workbook.worksheet(worksheet_name))
          wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
        else:
          wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
    except Exception as ex:
        print('connect google fail ', ex)
        sys.exit(1)

    cell_list = wks.range(range)

    try:
        idx = 0
        for (start_row , rowlist) in enumerate(data):
            for (colnum, value) in enumerate(rowlist):
                cell_list[idx].value = value
                idx += 1
                if idx >= len(cell_list):
                    break
        wks.update_cells(cell_list)
    except:
        print "Exception"


    cell_list_tab = wks.range(range_tab)

    try:
        idx = 0
        for (start_row , value) in enumerate(a_tup):
            cell_list_tab[idx].value = value
            idx += 1
            if idx >= len(cell_list_tab):
                break
        wks.update_cells(cell_list_tab)
    except:
        print "Exception"



GDriveJSON = 'GoogleAuthKey.json'
GSpreadSheet = '試算表檔名'
TYPE = 'Notebook'

rows_item = ['HardwareID', 'UserName', 'Computer', 'Type', 'CPU', 'RAM(MB)', 'Disk(MB)', 'Monitor']
rows_accest = DB_return("SELECT DISTINCT  A.HARDWARE_ID,A.TAG,H.NAME,B.TYPE,H.PROCESSORT,H.MEMORY,S.DISKSIZE,M.CAPTION \
from \
accountinfo as A \
join hardware as H \
join monitors as M \
join storages as S \
join softwares as SF \
join bios as B \
where \
A.HARDWARE_ID=H.ID \
and A.HARDWARE_ID=S.HARDWARE_ID \
and A.HARDWARE_ID=B.HARDWARE_ID \
and S.TYPE='Fixed hard disk media' \
and A.HARDWARE_ID=M.HARDWARE_ID \
and A.HARDWARE_ID=SF.HARDWARE_ID \
and A.HARDWARE_ID=H.ID \
and A.TAG not like '%報廢%' \
and A.TAG not like '%出售%' \
and B.TYPE='" + TYPE + "' GROUP BY A.TAG")

GSheet_write(GSpreadSheet,GDriveJSON,rows_item,rows_accest,TYPE)

OCS Inventory 匯出資料到 Google 試算表(依TAG名稱寫入試算表不同分頁)

OCS Inventory 匯出到 Google 試算表的程式是從網路上其他高手分享出來(參考資料出處)，並依個人需求調整。

如要取用請注意，因程式是用 python 寫的，程式段落可能會移位導致無法正常運作。

下列程式調整為依電腦設備寫入同算表不同分頁，過濾掉電腦上所安裝不想列出的系統修補軟體。

程式內容:
#coding=utf-8
#-*- coding: utf-8 -*-
import MySQLdb as mysqldb
import sys
import gspread
import string

def DB_return(exec_cmd):
    connection = mysqldb.connect('localhost', 'ocs', 'ocspasswd', 'ocsdb',charset='utf8');
    with connection:
        cursor = connection.cursor()
        cursor.execute(exec_cmd)
        rows_info = cursor.fetchall()
        return rows_info

def GSheet_write(GSpreadSheet,GDriveJSON,tab_data,data,worksheet_name):
    a_list=[]
    for i, element in enumerate(tab_data):
        a_list.append(tab_data[i])
    a_tup = tuple(a_list)

    start_row = 1
    start_letter = 'A'
    end_len = len(data[0]) - 1
    if end_len > 26:
        len_info = end_len - 26
        len_a='A'
    else:
        len_info = end_len
        len_a=''
    end_letter = string.uppercase[len_info]
    end_letter = len_a+end_letter
    end_row = len(data)
    range = "%s%d:%s%d" % (start_letter, start_row+1 , end_letter, end_row+1)
    range_tab =  "%s%d:%s%d" % (start_letter, start_row , end_letter ,start_row)

    from oauth2client.service_account import ServiceAccountCredentials as SAC
    try:
        scope = ['https://spreadsheets.google.com/feeds']
        key = SAC.from_json_keyfile_name(GDriveJSON, scope)
        gc = gspread.authorize(key)
        workbook = gc.open(GSpreadSheet)
        if worksheet_name in [sheet.title for sheet in workbook.worksheets()]:
          workbook.del_worksheet(workbook.worksheet(worksheet_name))
          wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
        else:
          wks = workbook.add_worksheet(worksheet_name,end_row+1,end_len+1)
    except Exception as ex:
        print('connect google fail ', ex)
        sys.exit(1)

    cell_list = wks.range(range)

    try:
        idx = 0
        for (start_row , rowlist) in enumerate(data):
            for (colnum, value) in enumerate(rowlist):
                cell_list[idx].value = value
                idx += 1
                if idx >= len(cell_list):
                    break
        wks.update_cells(cell_list)
    except:
        print "Exception"


    cell_list_tab = wks.range(range_tab)

    try:
        idx = 0
        for (start_row , value) in enumerate(a_tup):
            cell_list_tab[idx].value = value
            idx += 1
            if idx >= len(cell_list_tab):
                break
        wks.update_cells(cell_list_tab)
    except:
        print "Exception"



GDriveJSON = 'GoogleAuthKey.json'
GSpreadSheet = '試算表檔名'

rows_item = ['HardwareID', 'UserName', 'Computer', 'CPU', 'RAM(MB)', 'HD_Model', 'HD_Size(MB)', 'Monitor', 'Software']

connection = mysqldb.connect('localhost', 'ocs', 'ocspasswd', 'ocsdb',charset='utf8');
with connection:
    cursor = connection.cursor()
    cursor.execute("select * from accountinfo where TAG not like '%出售%' and TAG not like '%報廢%'")
    numrows = int(cursor.rowcount)

    for i in range(numrows):
        row = cursor.fetchone()
        GsheetName = row[1]

        reload(sys)
        sys.setdefaultencoding('utf-8')

        rows_accest = DB_return("SELECT DISTINCT \
A.HARDWARE_ID,A.TAG,H.NAME,H.PROCESSORT,H.MEMORY,S.NAME,S.DISKSIZE,M.CAPTION,SF.NAME \
from accountinfo as A \
join hardware as H \
join monitors as M \
join storages as S \
join softwares as SF \
where \
A.HARDWARE_ID=H.ID \
and A.HARDWARE_ID=S.HARDWARE_ID \
and S.TYPE='Fixed hard disk media' \
and A.HARDWARE_ID=M.HARDWARE_ID \
and A.HARDWARE_ID=SF.HARDWARE_ID \
and A.HARDWARE_ID=H.ID \
and SF.NAME not like '%Hotfix%' \
and SF.NAME not like '%Update%' \
and SF.NAME not like '%安全性更新%' \
and SF.NAME not like '%更新%' \
and SF.NAME not like '%驅動程式%' \
and A.TAG like '" + GsheetName + "' \
ORDER BY H.NAME")

        GSheet_write(GSpreadSheet,GDriveJSON,rows_item,rows_accest,GsheetName)