import datetime
import subprocess
from sqlalchemy import Column, Integer, String, UniqueConstraint, Index
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.orm import declarative_base
Base = declarative_base()
# filename ="cat /var/log/cron"
# # def log_format(filename):
# log_format = filename
cmd = 'cat /var/log/cron'
log = subprocess.getoutput(cmd=cmd)
out = log.split('\n')
hostname = subprocess.getoutput(cmd='hostname')
class corn_log(Base):
__tablename__ = "corn_log"
id = Column(Integer, primary_key=True)
name = Column(String(100))
hostname = Column(String(20))
# date = Column(String(20))
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'),
Index('ix_n_ex', 'name', 'id', ),
)
def __repr__(self):
return f"User(id={self.id!r}, name={self.name!r},hostname={self.hostname!r})"
#链接数据库创建表
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@172.17.106.2:3306/test?charset=utf8", echo=True, future=True)
Base.metadata.create_all(engine)
# with Session(engine) as session:
# for i in out:
# sels = [corn_log(name=i)]
# # newsel = f'< hostname=hostname,sel={i}>'
# session.add_all(sels)
# print(session.add_all(sels))
# session.commit()
user1 = corn_log(name="user1")
user2 = corn_log(name="user2")
with Session(engine) as session:
# session.add(user1)
# session.add(user2)
for i in out:
user3 = corn_log(name=f'{i}',hostname=f'{hostname}')
session.add_all([user3])
print('start insert:',session.add_all([user3,user2,user1]))
session.commit() # write changes to the database
python 读取ipmi sel日志并写入数据库
(因为虚拟机,所以用写入文本代替 cat )
from sqlalchemy import Column, Integer, String, UniqueConstraint, Index
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.orm import declarative_base
Base = declarative_base()
#链接数据库创建表
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@172.17.106.2:3306/test?charset=utf8", echo=True, future=True)
Base.metadata.create_all(engine)
"""
9b | 05/27/2024 | 10:38:12 | Power Supply PSU0_Supply | Presence detected | Asserted
9c | 05/27/2024 | 10:38:12 | Power Supply PSU1_Supply | Presence detected | Asserted
"""
#定义数据表
class SEL(Base):
__tablename__ = "ipmi_sel"
id = Column(Integer, primary_key=True)
flag = Column(String(30))
hostname = Column(String(30))
date = Column(String(100))
time = Column(String(20))
msg = Column(String(30))
log = Column(String(30))
status = Column(String(30))
__table_args__ = (
UniqueConstraint('id', 'date', name='uix_id_name'),
Index('ix_n_ex', 'date', 'id', ),
)
def __repr__(self):
return f"User(id={self.id!r}, name={self.date!r},hostname={self.msg!r})"
#create table
Base.metadata.create_all(engine)
sel = """
9b | 05/27/2023 | 13:32:12 | Power Supply PSU0_Supply | Presence detected | Asserted
9c | 05/27/2023 | 13:33:22 | Power Supply PSU1_Supply | Presence detected | Asserted
"""
hostname = 'k8s'
def get_host_sel(host):
import subprocess
from sqlalchemy.orm import Session
cmd = f"ssh {host} cat /root/sel"
log = subprocess.getoutput(cmd=cmd)
out = log.split('\n')
# print(out)
with Session(engine) as session:
for i in out:
flag, date, time, msg, log, status = i.split('|')
# print(te)
sels = SEL(hostname=f'{hostname}',flag=f'{flag}',date=f'{date}',time=f'{time}',msg=f'{msg}',log=f'{log}',status=f'{status}')
session.add_all([sels])
print('start insert:',session.add_all([sels]))
session.commit() # write changes to the database
get_host_sel('k8s')