I have a little Problem and hopefully somebody who knows the solution for it.
Here's my query:
index=XXX
| eval watchdog_time=_time
| stats count by watchdog_time,date_hour
| convert timeformat="%Y-%m-%d %H:%M" ctime(watchdog_time)
| eval watchdog=if(count=1 AND date_hour<"8","OK","NOK")
| eval Date=now() | convert timeformat="%Y-%m-%d %H:%M" ctime(Date) | table Date, watchdog, watchdog_time
| outputlookup slaamlt.csv append=true
The eval whatchdog=if(Count..... Looks if the entry comes before 8am, but unfortunately this query don't shows me if there isn't an entry coming! I want to have also a "NOK" status, even when there is no entry in the lookup table. How i can solve this Problem? Thanks for your effort.
hey @krispost
Try this:
index=XXX
| eval watchdog_time=_time
| stats count by watchdog_time,date_hour
| convert timeformat="%Y-%m-%d %H:%M" ctime(watchdog_time)
| eval watchdog=if(count=1 AND date_hour<8,"OK","NOK")
| eval Date=now()
| convert timeformat="%Y-%m-%d %H:%M" ctime(Date)
| table Date, watchdog, watchdog_time
| outputlookup slaamlt.csv append=true
Let me know if this helps!
sry guys, have another solution that works:
|eval watchdog_time=_time
| stats count by watchdog_time,date_hour
| convert timeformat="%Y-%m-%d %H:%M" ctime(watchdog_time)
| eval watchdog_value=if(date_hour<"8","OK","NOK")
| eval watchdog=if(isnull(watchdog_time),"NOK1",watchdog_value)
| eval Date=now() | convert timeformat="%Y-%m-%d %H:%M" ctime(Date) | table Date, watchdog, watchdog_time
| outputlookup slaamlt.csv append=true
@krispost, I dont think you have defined your criteria for OK and NOK. In your question seemed like count=1
was also required condition which is missing in your current query.
Also date_hour<"8"
performs string comparison which will treat values greater than 10 smaller than 8. So the suggestions above were to compare with numeric 8
rather than string "8"
.
Also while query may eventually work by adding a lot of pipes, you should consider performance of your query as well. Check my query above compared to your current solution for performance in Job Inspector.
hey @krispost
Try this:
index=XXX
| eval watchdog_time=_time
| stats count by watchdog_time,date_hour
| convert timeformat="%Y-%m-%d %H:%M" ctime(watchdog_time)
| eval watchdog=if(count=1 AND date_hour<8,"OK","NOK")
| eval Date=now()
| convert timeformat="%Y-%m-%d %H:%M" ctime(Date)
| table Date, watchdog, watchdog_time
| outputlookup slaamlt.csv append=true
Let me know if this helps!
I would try something like the following:
index=XXX
| stats count by _time, date_hour
| eval watchdog_time=strftime(_time,"%Y-%m-%d %H:%M")
| eval watchdog=if(count=1 AND date_hour<8,"OK","NOK")
| eval Date=strftime(now(), "%Y-%m-%d %H:%M")
| table Date, watchdog, watchdog_time
| outputlookup slaamlt.csv append=true
Stats should be performed first on _time and then _time should be converted to watchdog_time as per use case.