How to edit my search to get the status of a log script?

[sunitakesam]

log file:testscripts.log

Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script started
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=calling wget without post parameter
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=wget command exit code: 0
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=data invoked
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=HTTP code from server:0
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Status will be updated in test.log
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script exit normal


Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script started
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=calling wget without post parameter
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=wget command exit code: 0
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=data invoked
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=HTTP code from server:0
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Status will be updated in test.log
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script terminated

i need data with fields with id,script,status,duration,start time and end time and it should be group by id..
i don't understand how to modify below search to get status based on last line log with script terminated or script exit normal.

id script status host=d*  script=test*  
| stats min(_time) as start, max(_time) as end  by id , script
| eval duration=end-start | eval start=strftime(start, "%Y/%m/%d %T.%3Q") 
| eval end=strftime(end, "%Y/%m/%d %T.%3Q") 
| sort by start desc join id [  search script in (test*)  | eval status=if(log=='Script exit normal', 'success', 'failed')]

[gokadroid · Accepted solution]

How about trying this and see if it works out for you:

id script status host=d*  script=test* "log=Script " 
| rex field=_raw "log=Script\s*(?<statusString>[\S]+)"
| eval status=case(statusString=="started", "started", statusString=="exit", "success",  statusString=="terminated", "failed",  1=1, "unknown")
| eventstats min(_time) as start, max(_time) as end  by id , script
| search status="exit" OR status="terminated"
| table id, script, status, start, end
| eval duration=end-start 
| eval start=strftime(start, "%Y/%m/%d %T.%3Q") 
| eval end=strftime(end, "%Y/%m/%d %T.%3Q") 

[sunitakesam]

how can I get in-progress value when job is running and just has below lines without terminated or exit parameter..

Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script started
12. Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=calling wget without post parameter
13. Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=wget command exit code: 0
14. Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=data invoked

[gokadroid]

Those will be the script Ids which will not have either exit or terminated in status. Since above SPL starts with searching for `log=Script hence for such scripts only one line will be there. So above SPL shall change something like this to filter pending ones only:

id script status host=d*  script=test* "log=Script " 
 | rex field=_raw "log=Script\s*(?<statusString>[\S]+)"
 | eval status=case(statusString=="started", "started", statusString=="exit", "success",  statusString=="terminated", "failed",  1=1, "unknown")
 | stats count by id
 | where count < 2

[sunitakesam]

Thanks a lot but I need either in-progress, success or terminated. could not ale to get it through above query. can you please help me in writing complete query..

[sunitakesam]

"log=Script " is this for in line 1 and ai am always getting status as 'unknown'

[gokadroid]

Since all the lines of yours which has status have the keyword "log=Script " hence it's used to filter only those lines. For example see these:

Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script started
:
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script exit normal
:
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script started
:
Date = 02/10/17 14:15:00,script = testscript, id = 29251, log=Script terminated