In canvas I have a requirement to display couple of Job status of last 30 days which frequency is daily however on some days 2-3 jobs are not schedule. As expected no timestamp or any entry will be created in elastic for not running jobs. I want to create a last 30 days view to provide the job status it picks the dates when there is an entry, however, not able to highlight the date or an entry which says that job was not executed on that given date. E.g, if a view is created for job execution for last 5 days and considering on Tue it was not executed I will get
Days | Status
05-Jun-2023 | Success
07-Jun-2023 |Success
08-Jun-2023 |Success
09-Jun-2023 | Delay
But my requirement is to display as
Days | Status
05-Jun-2023 | Success
06-Jun-2023 | Not Executed
07-Jun-2023 |Success
08-Jun-2023 |Success
09-Jun-2023 | Delay
I am creating the dates using the CSV and map column so that I have an entry for all the 5 days and then trying to run the query to fetch the status from the elastic
I used below query but got below error
Expression failed with the message:
[mapcolumn] > [switch] > [case] > [eq] > [essql] > Unexpected error from Elasticsearch: ql_illegal_argument_exception - Line 8:22: Comparisons against fields are not (currently) supported; offender [cast( Concat( DATETIME_FORMAT("@timestamp",'YYYY-MM-dd'), 'T05:30:00.000' ) AS DATETIME)] in [<]
Please advise how to fetch status using the query by filtering/comparing against the dates in the table created.
Also is it possible to create a Grid view for below data
Day | Job 1 Status | Job 2status
05-Jun-2023 | Success | Success
06-Jun-2023 | Not Executed | Success
07-Jun-2023 |Success |Delay
08-Jun-2023 |Success |Not Executed
09-Jun-2023 | Delay |Success
//
</>
filters ungrouped=true
| csv
"Day,col, SLA_STAUS
1,86400000,NotExecuted
2,86400000,NotExecuted
3,86400000,NotExecuted
4,86400000,NotExecuted
5,86400000,NotExecuted
6,86400000,NotExecuted
7,86400000,NotExecuted
8,86400000,NotExecuted
9,86400000,NotExecuted
10,86400000,NotExecuted
| alterColumn "Day" type="number"
| alterColumn "col" type="number"
| mathColumn id="DayMilSec" name="DayMilSec" expression="Daycol"
| mapColumn name="formatteddate" expression={date}
| mathColumn id="DateU" name="DateU" expression="formatteddate - DayMilSec"
| mapColumn name="formatteddateU" expression={getcell "DateU" | formatdate format="DD MMMM YYYY"}
|mapcolumn name="SLA_STATUS"
expression={ getcell "formatteddateU" |
switch {case if={eq {filters|essql {string "
Select DATETIME_FORMAT("@timestamp",'DD MMMM YYYY')
FROM "index1"
where "job.status" = 'SUCCESS'
AND "@timestamp" < cast( Concat( DATETIME_FORMAT("@timestamp",'YYYY-MM-dd'), '" {var "METSLATime"} "' ) AS DATETIME)
and "job.name" LIKE 'job/_test' ESCAPE '/' Order by "@timestamp"
"}
}
} then="green"}
default="Red"
}
| table
</>
//