WatchDirectory home page
WatchDirectory Startseite (Deutsche Version)
Site WatchDirectory (Français)
  Welcome, Guest. Please Login or Register
YaBB - Yet another Bulletin Board
   
  HomeHelpSearchLoginRegister  
 
Page Index Toggle Pages: 1
Log of which tasks are running and when (Read 603 times)
TonyF
YaBB Newbies
*
Offline



Posts: 37
Log of which tasks are running and when
Sep 27th, 2016 at 2:01pm
 
We have multiple tasks that lock down files on datashares by changing the security properties of the files using the ICACLS function.

I anticipate that a future data integrity auditor will ask me to demonstrate that a particular task(s) were active during a specific time period.

I need to create a log of which tasks are running and when. I need to log when the task(s) start and stop and whether the task was stopped by the user.

I understand that you can get this information from the History files by running SQLlite queries. If possible, I would like to store this information in a dedicated SQLlite database.

I would like to store the task name, run id, when the task started, when it finished and the last two messages in the run.

Any thoughts on how this could be achieved in an efficient manner?

Many thanks,

Tony
Back to top
 
 
IP Logged
 
Gert
YaBB Administrator
*****
Offline



Posts: 2225
The Netherlands
Re: Log of which tasks are running and when
Reply #1 - Sep 28th, 2016 at 8:35am
 
Use the sqlite3.exe commandline program from www.sqlite.org - https://www.sqlite.org/cli.html

Run the following sql script
Code:
ATTACH DATABASE "C:\ProgramData\WatchDirectory\MyTask\History.db" AS DB;
INSERT OR REPLACE INTO RUNS (TASK, ID, STARTT, ENDT)
    SELECT "MyTask", DB.ID, DB.DTSTART, DB.DTEND
    FROM DB.WDRUNS;
DETACH DATABASE DB;
 


This assumes you have a database open with the following table
Code:
CREATE TABLE RUNS
(TASK VARCHAR(20) NOT NULL
, ID INTEGER NOT NULL
, STARTT TIMESTAMP NOT NULL
, ENDT TIMESTAMP
, CONSTRAINT PK_RUNS PRIMARY KEY (TASK, ID)
);
 



All the above is not tested.

Alternatively you could use the .dump sqlite3 command.
Back to top
 

Gert Rijs - gert (at) gdpsoftware (dot) com
Blog: http://blog-en.gdpsoftware.com/
End Alzheimer's: http://www.alz.org&&...
WWW WWW GdPSoftware  
IP Logged
 
TonyF
YaBB Newbies
*
Offline



Posts: 37
Re: Log of which tasks are running and when
Reply #2 - Oct 5th, 2016 at 12:23pm
 
Hi Gert,

Thank you for your prompt response.

I have worked with our local dba to create my own script as I needed to capture more information than just start and stop times.

First I created a table WDRUNS in the SQLite database MonRuns.db3 using the script:

CREATE TABLE WDRUNS
(
     
     TASK                  varchar(20)  null       
,      MESSAGE                  varchar(200) null
,      MONDIRECTORY            varchar(200) null
,      DTMESSAGE            timestamp null on conflict ignore
,      RUN_ID                  integer not null on conflict ignore
,      ID                  integer not null on conflict ignore
,      LVL                  char(1) not null on conflict ignore
,      DTDETECTED            timestamp null
,      primary key (DTMESSAGE, RUN_ID, MESSAGE, LVL, ID) on conflict ignore
)

I then add records to this table via the batch file:

REM Path to sqlite3.exe (no "quotes")
SET SQL=C:\Documents and Settings\All Users\Application Data\watchDirectory\Batch Files\sqlite3.exe

REM Extract monitored directory (MONDIR) from contents of Task.config file (text following the string directoryToMonitor=) - need to copy file due to access issues.

copy "%WD_DIR%\%WD_RELDIR%.config" "%WD_TASKDIR%\%WD_RELDIR%.txt"

for /F "tokens=2 delims=^=" %%V in ('Findstr /B "directoryToMonitor=" "%WD_TASKDIR%\%WD_RELDIR%.txt" ') do set MONDIR=%%V

del "%WD_TASKDIR%\%WD_RELDIR%.txt"

REM ==== no need to change anything below here ====

SET TFBASE=%WD_TASKDIR%\F1%WD_CUREVT%

REM create the query (look for key messages and LVL codes (W and E))

EcHO Attach DATABASE "%WD_TASKDIR%\MonRuns.db3" as MonRuns; >> "%TFBASE%.SQL"

ECHO Insert into MonRuns.WDRUNS (TASK, MONDIRECTORY, DTDETECTED, id, run_id, dtmessage, lvl, message) select "%WD_RELDIR%", "%MONDIR%", "%WD_DTTM%", id, run_id, dtmessage, Lvl, message from [wdrunmsg] where message = 'Starting to monitor the directory'; >> "%TFBASE%.SQL"

ECHO Insert into MonRuns.WDRUNS (TASK, MONDIRECTORY, DTDETECTED, id, run_id, dtmessage, lvl, message) select "%WD_RELDIR%", "%MONDIR%", "%WD_DTTM%", id, run_id, dtmessage, Lvl, message from [wdrunmsg] where message = 'User requested this run to stop.'; >> "%TFBASE%.SQL"

ECHO Insert into MonRuns.WDRUNS (TASK, MONDIRECTORY, DTDETECTED, id, run_id, dtmessage, lvl, message) select "%WD_RELDIR%", "%MONDIR%", "%WD_DTTM%", id, run_id, dtmessage, Lvl, message from [wdrunmsg] where message = 'shutting down'; >> "%TFBASE%.SQL"

ECHO Insert into MonRuns.WDRUNS (TASK, MONDIRECTORY, DTDETECTED, id, run_id, dtmessage, lvl, message) select "%WD_RELDIR%", "%MONDIR%", "%WD_DTTM%", id, run_id, dtmessage, Lvl, message from [wdrunmsg] where lvl = 'W'; >> "%TFBASE%.SQL"

ECHO Insert into MonRuns.WDRUNS (TASK, MONDIRECTORY, DTDETECTED, id, run_id, dtmessage, lvl, message) select "%WD_RELDIR%", "%MONDIR%", "%WD_DTTM%", id, run_id, dtmessage, Lvl, message from [wdrunmsg] where lvl = 'E'; >> "%TFBASE%.SQL"

ECHO Detach DATABASE MonRuns;>> "%TFBASE%.SQL"

REM Start the query

type "%TFBASE%.SQL" | "%SQL%" "%WD_FILE%"

del "%TFBASE%.SQL"

I plan to run this task every 24 hours.

This seems to work fine but it occurred to me that it would be great if there was an option for each installation of WatchDirectory to do this kind of logging 'out of the box'. Additional information that could be captured would be the server that the task is running on and a version number of the task (i.e. if the configuration of the task is edited, the version would be incremented).

As an inside to this topic, It would be good if there was an option to tag a task to say that the task should always be running. Then, if the task is not running, a future version of the WxRemote console could flag that a task is not running but should be. This is important in a regulated environment where there are many tasks running on multiple servers.

Back to top
 
 
IP Logged
 
Page Index Toggle Pages: 1