1.需求:对电脑,办公设备,耗材等做资产盘点和整理,并对库存预警。
2.选型:snipeit
3.部署
#!/bin/bash
docker run -d -p 80:80 --name="snipeit" --env-file=snipeit.env --mount source=snipe-vol,dst=/var/lib/snipeit --privileged snipe/snipe-it
snipeit.env:
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
HOSTNAME=f3dee7edd3f7
TERM=xterm
MYSQL_PORT=tcp://mysql.sit.xxx.com:3306
MYSQL_PORT_3306_TCP=tcp://mysql.sit.xxx.com:3306
MYSQL_PORT_3306_TCP_ADDR=mysql.sit.xxx.com
MYSQL_PORT_3306_TCP_PORT=3306
MYSQL_PORT_3306_TCP_PROTO=tcp
#MYSQL_NAME=/snipeit/mysql
MYSQL_ENV_MYSQL_ROOT_PASSWORD=xxx
MYSQL_ENV_MYSQL_DATABASE=snipeit
MYSQL_ENV_MYSQL_USER=snipeit
MYSQL_ENV_MYSQL_PASSWORD=xxx
MYSQL_ENV_APP_URL=http://192.168.3.231:80
MYSQL_ENV_APP_TIMEZONE=Asia/Shanghai
MYSQL_ENV_APP_LOCALE=zh-CN
MYSQL_ENV_GOSU_VERSION=1.12
MYSQL_ENV_MYSQL_MAJOR=5.6
MYSQL_ENV_MYSQL_VERSION=5.6.51-1debian9
MYSQL_ROOT_PASSWORD=xxx
MYSQL_DATABASE=snipeit
MYSQL_USER=snipeit
MYSQL_PASSWORD=yuj1Iqu4Eef
MAIL_PORT_587_TCP_ADDR=c2.xxx.net
MAIL_PORT_587_TCP_PORT=465
MAIL_ENV_FROM_ADDR=it_xxx@xxx.com
MAIL_ENV_FROM_NAME=snipeit
MAIL_ENV_ENCRYPTION=tcp
MAIL_ENV_USERNAME=it_xxx@xx.com
MAIL_ENV_PASSWORD=xxx
SNIPEIT_SMTP_ENCRYPTION=ssl
MAIL_DRIVER=smtp
MAIL_HOST=c2.xxx.net
MAIL_PORT=465
MAIL_FROM_ADDR=it_xxx@xxx.com
MAIL_FROM_NAME=snipeit
MAIL_ENCRYPTION=tcp
MAIL_USERNAME=it_xxx@xxx.com
MAIL_PASSWORD=xxx
MAIL_ENCRYPTION=ssl
APP_ENV=production
APP_DEBUG=false
APP_KEY=base64:uNRDxRe96zAcZkkchW3byQf0ukPZtaMhUI3lrC2KS/g=
APP_URL=http://192.168.3.231
APP_TIMEZONE=Asia/Shanghai
APP_LOCALE=zh-CN
HOME=/root
SN_LDAP_ENABLED=true
SN_LDAP_SERVER=ldap://ldap.xxx.com:389
SN_LDAP_BIND_DN="cn=auth,dc=xxx,dc=com"
SN_LDAP_BIND_PASSWORD=xxx
SN_LDAP_USER_BASE_DN="dc=xxx,dc=com"
SN_LDAP_USER_FILTER="(&(|(memberOf=cn=it,dc=xxx,dc=com))(&(objectClass=person)(cn=%(user)s)))"
4.告警(python编码)
import pymysql
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
# MySQL database configuration
db_host = 'mysql.sit.xxx.com'
db_port = 3306
db_user = 'snipeit'
db_password = 'xxx'
db_name = 'snipeit'
# Email configuration
# Email configuration
smtp_server = 'c2.xxx.net'
smtp_port = 465
smtp_username = 'it_support@xxx.com'
smtp_password = 'xxx'
recipient_emails1 = ['baofeng@xxx.com']
recipient_emails2 = ['chenjiajun@xxx.com']
cc_emails = ['xingxing@xxx.com','yandongmei@xxx.com']
subject1 = "固定资产库存不足告警--->>>>(上海)所有可分配数量过低请及时采购,以免影响入职!"
subject2 = "固定资产库存不足告警--->>>>(广州)所有可分配数量过低请及时采购,以免影响入职!"
# SQL query to execute
#query = "SELECT COUNT(*) AS '所有可分配资产数量' FROM assets WHERE status_id=5 AND deleted_at is NULL AND assigned_to is NULL;"
query1="SELECT COUNT(*) AS '上海所有可分配资产数量' FROM assets WHERE status_id=5 AND deleted_at is NULL AND assigned_to is NULL AND company_id=1;"
query2="SELECT COUNT(*) AS '广州所有可分配资产数量' FROM assets WHERE status_id=5 AND deleted_at is NULL AND assigned_to is NULL AND company_id=2;"
# Connect to the MySQL database and execute the query
conn = pymysql.connect(host=db_host, port=db_port, user=db_user, password=db_password, db=db_name)
cursor = conn.cursor()
cursor.execute(query1)
result1 = cursor.fetchone()[0]
print(result1)
cursor.execute(query2)
result2 = cursor.fetchone()[0]
print(result2)
if result1 < 10:
# Send an email alert
msg = MIMEText('上海所有可分配资产数量为: {} 个,请及时补充以免影响入职员工使用。\n \n库存详情请访问:http://it.yintaerp.com/hardware?status=RTD 只读账号:read read@123456'.format(result1))
msg['From'] = smtp_username
msg['To'] = ', '.join(recipient_emails1)
msg['Cc'] = ', '.join(cc_emails)
msg['Subject'] = subject1
server = smtplib.SMTP_SSL(smtp_server, smtp_port)
server.login(smtp_username, smtp_password)
server.sendmail(smtp_username, recipient_emails1 + cc_emails, msg.as_string())
server.quit()
else:
print("上海库存足够,目前还剩余 {} 个固定资产".format(result))
if result2 < 10:
# Send an email alert
msg = MIMEText('广州所有可分配资产数量为: {} 个,请及时补充以免影响入职员工使用。\n \n库存详情请访问:http://it.yintaerp.com/hardware?status=RTD 只读账号:read read@123456'.format(result2))
msg['From'] = smtp_username
msg['To'] = ', '.join(recipient_emails2)
msg['Cc'] = ', '.join(cc_emails)
msg['Subject'] = subject2
server = smtplib.SMTP_SSL(smtp_server, smtp_port)
server.login(smtp_username, smtp_password)
server.sendmail(smtp_username, recipient_emails2 + cc_emails, msg.as_string())
server.quit()
else:
print("广州库存足够,目前还剩余 {} 个固定资产".format(result2))
# Close the database connection
cursor.close()
conn.close()
2023-05-11 10:30:09 it_support@xxxxx.com 邮件展示:
上海所有可分配资产数量为: 1 个,请及时补充以免影响入职员工使用。
库存详情请访问:http://it.xxxxx.com/hardware?status=RTD 只读账号:read read