source=IN1 STATUS=SUCCESS OR STATUS=FAILED earliest=-2d@d+14h latest=-1d@d+14h APP=DEV
| stats count(APP) as "numberof_actuals"
| appendcols
[| inputlookup Indus_bim_ocf_lookup.csv
| stats count(eval(APP=DEV)) as "numberof_lkup"
]
| eval difference=(numberof_lkup - numberof_actuals)
| eval status=if(difference>=1, "MISSING", "")
| chart sum(difference) -----> i would like to use timechart but its not working, can you help?
Give this a try
UPdated - both searches*
source=IN1 STATUS=SUCCESS OR STATUS=FAILED earliest=-8d@d+14h latest=-1d@d+14h APP=DEV
| timechart span=1d count as "numberof_actuals"
| eval "numberof_lkup"=[| inputlookup Indus_bim_ocf_lookup.csv | stats count(eval(APP="DEV")) as search]
| eval difference=(numberof_lkup - numberof_actuals)
| timechart span=1d sum(difference)
OR (if you need to do a 14h adjustment in your data)
source=IN1 STATUS=SUCCESS OR STATUS=FAILED earliest=-8d@d+14h latest=-1d@d+14h APP=DEV
| eval _time=relative_time(_time,"-14h")
| timechart span=1d count as "numberof_actuals"
| eval "numberof_lkup"=[| inputlookup Indus_bim_ocf_lookup.csv | stats count(eval(APP="DEV")) as search]
| eval difference=(numberof_lkup - numberof_actuals)
| timechart span=1d sum(difference)
This also works for timechart, but the value expected is not correct.
The 7 day trend that I need is for "batch runs from 2pm to <2pm daily".
When I changed the earliest to -7d or -8d, the numbers are not correct anymore, is there a way I can subquery the above and get the -7day trend?
Hello,
seems that below is not working,
| eval "numberof_lkup"=[| inputlookup Indus_bim_ocf_lookup.csv | stats count(eval(APP=DEV)) as search]
I believe the updated version would work. (double quotes were missing, in your question too)
@phoebepascual Firstly your earliest and latest values don't give 7 days of data, you may need to change it to -7d if you looking for 7 day.
source=IN1 STATUS=SUCCESS OR STATUS=FAILED earliest=-7d@d+14h latest=-1d@d+14h APP=DEV
|bin span=1d _time| stats count(APP) as "numberof_actuals" by _time| appendcols [| inputlookup Indus_bim_ocf_lookup.csv
| stats count(eval(APP=DEV)) as "numberof_lkup"
]
| eval difference=(numberof_lkup - numberof_actuals)
| eval status=if(difference>=1, "MISSING", "")
| timechart sum(difference)
You need to use timechart command and also in your first stats
Thank you Vijeta! Yes it works! but.. the 7 day trend that I need is for "batch runs from 2pm to <2pm daily".
When I changed the earliest to -7d, the numbers are not correct anymore, is there a way I can subquery the above and get the -7day trend?
@phoebepascual Can you please specify the output you are getting and the output you are looking for?