[PYTHON] Day 4 of creating an operation log shaping tool

Days 1 and 2 of creating operation log formatting tool Day 3 of creating operation log formatting tool

I got an error when I read the CSV

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x83 in position 0: invalid start byte

CSV is like this (masked)

oplog20201112.csv


User ID,client name,Windows login ID,Terminal ID,IP Address,MAC Address,Domain name,Login time,Logout time,Login status,action,Function name,Executable file name(Shell),argument(Command line),Execution time,Execution state
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","Job monitoring","Job.exe","-context:*****","2020/11/12 13:19:23","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","Main menu","Companyxx.exe","-cfg","2020/11/12 13:18:56","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","System setting","Maintenance.exe","-context:*****","2020/11/12 13:19:19","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Batch job","mst13","svc.sh","userid/password","2020/11/12 13:19:32","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Batch job","mst13","test.sh","userid/password 0 0","2020/11/12 13:19:29","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Batch job","mst13","test.sh","userid/password 0 0","2020/11/12 13:19:30","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Batch job","mst13","out.sh","userid/password %JAVA% 0","2020/11/12 13:19:31","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","Job management","quevw.exe","-context:*****","2020/11/12 13:19:20","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","batchjob jobid:498298","test.sh","userid/password 0 0","2020/11/12 13:19:56","success"
"all","client-name","works","client-name","xx.xx.xx.xx","xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx     xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx  xx-xx-xx-xx-xx-xx xx-xx-xx-xx-xx-xx ","123-456","2020/11/12 13:18:56","2020/11/12 13:23:38","success","Program","batchjob jobid:498301","svc.sh","userid/password","2020/11/12 13:21:39","success"

Well, Japanese was included. Encoding required.

https://techacademy.jp/magazine/21128

It doesn't matter at all

Process data with pandas based on the csv file of the new Python corona

That's why it looks interesting.

Once the text was changed to UTF-8 with an editor for confirmation, the reading was successful. Oh, would you like to sort by date and time column as it is?

firstpandas.py


import pandas as pd

df = pd.read_csv('oplog20201112.csv')
print(df)

df_s = df.sort_values('Execution time')
print(df_s)

As a result, they sorted by execution time quite normally. So that's it. Let's save the CSV with a different name.

Return to this page. https://note.nkmk.me/python-pandas-to-csv/

firstpandas.py


import pandas as pd

df = pd.read_csv('oplog20201112.csv')
# print(df)

df_s = df.sort_values('Execution time')
# print(df_s)

df_s.to_csv('out.csv')

The encoding problem mentioned earlier is dealt with below. https://note.sngklab.jp/?p=435

firstpandas.py


import pandas as pd

df = pd.read_csv('oplog20201112.csv',encoding="SHIFT-JIS")
print(df)

df_s = df.sort_values('Execution time')
print(df_s)

df_s.to_csv('out.csv')

I want to bring the execution time column to the far left

Then how to move the column. https://note.nkmk.me/python-pandas-reindex/

As you can see, it looks like that.

firstpandas.py


import pandas as pd

df = pd.read_csv('oplog20201112.csv',encoding="SHIFT-JIS")
# print(df)
df_s = df.sort_values('Execution time')
# print(df_s)
df_s = df_s.reindex(columns=['Execution time', 
                             'Function name', 
                             'User ID', 
                             'client name', 
                             'Windows login ID', 
                             'Terminal ID', 
                             'Login time', 
                             'Logout time'])
df_s.to_csv('out.csv')

Tool distribution

I also want to see how to make it easier to use. There is something called pyinstaller.

[pyInstaller # Let's actually create it](https://qiita.com/hirohiro77/items/466e411fa41f144c8b2a#%E5%AE%9F%E9%9A%9B%E3%81%AB%E4%BD%9C% E6% 88% 90% E3% 81% 97% E3% 81% A6% E3% 81% BF% E3% 82% 88% E3% 81% 86)

set path=C:\Users\works\AppData\Local\Programs\Python\Python39\Scripts;%path%

Please note that is required first. At the command prompt

C:\workspaces\playground>pyinstaller firstpandas.py --onefile
67 INFO: PyInstaller: 4.0
67 INFO: Python: 3.9.0
69 INFO: Platform: Windows-10-10.0.19041-SP0
70 INFO: wrote C:\workspaces\playground\firstpandas.spec
(Omission)

Then, Exe was successfully completed.

image.png

https://news.mynavi.jp/article/python-28/

Recommended Posts

Day 4 of creating an operation log shaping tool
Day 3 of creating an operation log shaping tool