Hi,
I have a comparatively very long search scheduled to run on the 1st of every month. This includes 2 subsearches: one with join
and the other using append
. The search runs well when I run it manually, but when I schedule it, I am missing some data in rows (they are showing blank) which is not allowing me to rely on the scheduled report.
Can someone help me address this issue?
Thanks,
Navanitha G.
The "known issue" is the subsearch limit that is at most 50K. Plus, they are slow.
There is even a more serious issue with the "free" (but always wrong) date_*
fields. Do not use them; calculate your own.
Try this improved starter search:
index=ABC "|057|" (Authenticate_Text="authenticate" OR Authenticate_Text="defaultend")
| eval date_hourmin = tonumber(strftime(_time, "%H%M"))
| search date_hourmin>=1000 AND date_hourmin<=1200
| bucket _time span=1h
| multireport
[ stats count(Authenticate_Text) as Count by _time ]
[ timechart span=1h avg(time) by Authenticate_Text | addtotals]
| stats values(*) AS * by _time
|eval DATE=_time
| convert timeformat="%Y-%m-%d %Hh" ctime(DATE)
| table DATE, Count, "authenticate", "defaultend" Total
| appendpipe [
| head 1 | foreach * [eval <<FIELD>>=""]]
| addcoltotals labelfield=DATE label="count btwn 10h and 12h"
| appendpipe [
| head 1 | foreach * [eval <<FIELD>>=""]]
| append [ search
index=ABC "|057|" (Authenticate_Text="authenticate" OR (Authenticate_Text="Requestend"))
|stats count as "Total count"
| eval id=1
| untable id " " "Total Count "
| fields - id ]
Give this a try
index=ABC "|057|" (Authenticate_Text="authenticate" OR Authenticate_Text="defaultend") (date_hour>=10 AND date_hour<=12)
| bucket _time span=1h | eval authenticate=if(Authenticate_Text="authenticate",time,null())
| eval defaultend=if(Authenticate_Text="defaultend",time,null())
| stats count(Authenticate_Text) as Count avg(authenticate) as authenticate avg(defaultend) as defaultend by _time
| eval Date=strftime(_time,"%Y-%m-%d %Hh") | eval Total=authenticate+defaultend
| appendpipe [| head 1 | foreach * [eval <<FIELD>>=""]]
| addcoltotals labelfield=DATE label="count btwn 10h and 12h"
| appendpipe [| head 1 | foreach * [eval <>=""]]
| append[search index=ABC "|057|" (Authenticate_Text="authenticate" OR (Authenticate_Text="Requestend") )|stats count as "Total count" | eval ""="Total Count"]
The "known issue" is the subsearch limit that is at most 50K. Plus, they are slow.
There is even a more serious issue with the "free" (but always wrong) date_*
fields. Do not use them; calculate your own.
Try this improved starter search:
index=ABC "|057|" (Authenticate_Text="authenticate" OR Authenticate_Text="defaultend")
| eval date_hourmin = tonumber(strftime(_time, "%H%M"))
| search date_hourmin>=1000 AND date_hourmin<=1200
| bucket _time span=1h
| multireport
[ stats count(Authenticate_Text) as Count by _time ]
[ timechart span=1h avg(time) by Authenticate_Text | addtotals]
| stats values(*) AS * by _time
|eval DATE=_time
| convert timeformat="%Y-%m-%d %Hh" ctime(DATE)
| table DATE, Count, "authenticate", "defaultend" Total
| appendpipe [
| head 1 | foreach * [eval <<FIELD>>=""]]
| addcoltotals labelfield=DATE label="count btwn 10h and 12h"
| appendpipe [
| head 1 | foreach * [eval <<FIELD>>=""]]
| append [ search
index=ABC "|057|" (Authenticate_Text="authenticate" OR (Authenticate_Text="Requestend"))
|stats count as "Total count"
| eval id=1
| untable id " " "Total Count "
| fields - id ]
Thanks Woodcock,
It worked, I only need to make some minor changes like removing the null values and date formatting.
Why did you not post the search? How in the world do you expect anyone to help you? My best answer with the lack of detail you have given is DO NOT use either join
or append
. There are usually better ways to do whatever you are trying to do (but what that is, only you can detail).
well, my apologies for giving insufficient information. I was under the impression that there is already a known issue with 'join' and 'append' commands. Anyway, below is my query -
index=ABC "|057|" (Authenticate_Text="authenticate" OR Authenticate_Text="defaultend") date_hour>=10 AND date_hour<=12
| bucket _time span=1h
| stats count(Authenticate_Text) as Count by _time
| JOIN _time [search index=ABC "|057|" (Authenticate_Text="authenticate" OR Authenticate_Text="defaultend") (date_hour>=10 AND date_hour<=12)
| bucket _time span=1h
| timechart span=1h avg(time) by Authenticate_Text
| addtotals] |eval DATE=_time | convert timeformat="%Y-%m-%d %Hh" ctime(DATE) | table DATE, Count, "authenticate", "defaultend" Total | appendpipe [| head 1 | foreach * [eval <>=""]] | addcoltotals labelfield=DATE label="count btwn 10h and 12h" | appendpipe [| head 1 | foreach * [eval <>=""]] | append [ search index=ABC "|057|" (Authenticate_Text="authenticate" OR (Authenticate_Text="Requestend") )|stats count as "Total count" | eval id=1 | untable id " " "Total Count "| fields - id ]
Your foreach
code got clipped. Please login and click edit
and fix them.