Hi, I wonder whether someone may be able to help me please.
I have two saved searches as below:
Form Submission
index=main auditSource=for-frontend auditType=FormSubmission detail.referenceNumber="$refno$"
| append [search index=main "\" auditSource\":\"for-frontend\"" "\"auditType\":\"FormSubmission\"" NOT auditSource=*
| spath
| search "auditType"="FormSubmission"]
| eval stime=strptime('detail.submitted', "%Y-%m-%dT%H:%M:%S.%3N")
| convert timeformat="%d/%b/%Y %H:%M:%S" ctime(stime)
| rename stime as "Submission Date & Time", auditType as "Audit Type", detail.referenceNumber as "Ref No."
| table "Submission Date & Time", "Ref No.", "Audit Type"
and
Request Received
index=main auditSource=for-hod-adapter auditType=RequestReceived
| append [search index=main "\" auditSource\":\"for-hod-adapter\"" "\"auditType\":\"RequestReceived\"" NOT auditSource=*
| spath
| search "auditType"="RequestReceived"]
| eval refno=substr('detail.referenceNumber',1,7)
| rex field=detail.input "\/(?<refno>\d+)$"
| rex field=detail.input "for\/(?<refnopt1>[^\/]+)\/(?<refnopt2>[^\/]+)\/"
| STRCAT refnopt1 refnopt2 compref
| eval referencenumber=coalesce(refno, compref)
| search referencenumber="$refno$"
| eval stime=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S.%3N")
| convert timeformat="%d/%b/%Y %H:%M:%S" ctime(stime)
| rename stime as "Submission Date & Time", auditType as "Audit Type", detail.responseMessage as "Reason for Failure", referencenumber as "Ref No."
| table "Submission Date & Time", "Ref No.", "Audit Type", "Reason for Failure"
| sort +"Submission Date & Time"
NB. The ="$refno$" rows. This so I can link this to a token on my dashboard.
Both of these saved searches have a start date of 01/01/16 and an end date of "now", i.e. current date
The dashboard that they run in contains an input box (hence the token) and a 'timepicker'. Independently they run fine irrespective of the timerange selected in the 'timepicker'.
In a final panel, I pull both saved searches together using the following search:
| savedsearch "Form Submission" refno="$refno$"]
| append [| savedsearch "Request Received" refno="$refno$"]
| fields "Submission Date & Time", "Ref No.", "Audit Type", "Submission ID", "Message Received", "Satisfaction Rating", "Feedback Comments", "Error Message", "Reason for Failure"
| sort +"Submission Date & Time"
This is where I have the issue:
If I select the 'timepicker' range on my dashboard from 01 March to Current Date all of the expected results are returned.
If I select the 'timepicker' range on my dashboard from 01 February to Current Date all of the expected results are returned.
However, if I select the the 'timepicker' range from 01 January to Current Date there are no results returned.
I have to admit this has left me baffled, because all I'm doing is extending the time range.
I just wondered whether someone could possibly look at this please and let me know where I've gone wrong.
Many thanks and kind regards
Chriss
Avoid using append
whenever possible because it is governed by the 10K subsearch limit. Try using appendpipe
instead of append
and also try using multisearch
which does not use append
:
multisearch
[search index=main auditSource=for-frontend auditType=FormSubmission detail.referenceNumber="$refno$"]
[search index=main "\" auditSource\":\"for-frontend\"" "\"auditType\":\"FormSubmission\"" NOT auditSource=*
| spath
| search "auditType"="FormSubmission"]
| eval stime=strptime('detail.submitted', "%Y-%m-%dT%H:%M:%S.%3N")
| convert timeformat="%d/%b/%Y %H:%M:%S" ctime(stime)
| rename stime as "Submission Date & Time", auditType as "Audit Type", detail.referenceNumber as "Ref No."
| table "Submission Date & Time", "Ref No.", "Audit Type"
Avoid using append
whenever possible because it is governed by the 10K subsearch limit. Try using appendpipe
instead of append
and also try using multisearch
which does not use append
:
multisearch
[search index=main auditSource=for-frontend auditType=FormSubmission detail.referenceNumber="$refno$"]
[search index=main "\" auditSource\":\"for-frontend\"" "\"auditType\":\"FormSubmission\"" NOT auditSource=*
| spath
| search "auditType"="FormSubmission"]
| eval stime=strptime('detail.submitted', "%Y-%m-%dT%H:%M:%S.%3N")
| convert timeformat="%d/%b/%Y %H:%M:%S" ctime(stime)
| rename stime as "Submission Date & Time", auditType as "Audit Type", detail.referenceNumber as "Ref No."
| table "Submission Date & Time", "Ref No.", "Audit Type"
Hi @woodcock, I just wanted to let you know that I went away and re-worked this and got the final joined up query to work:
The solution was:
| multisearch [ savedsearch "Saved For Later" refno="$refno$"]
[ savedsearch "Form Submission" refno="$refno$"]
[ savedsearch "Submissions Accepted @ HOD" refno="$refno$"]
[ savedsearch "Submissions Rejected @ HOD" refno="$refno$"]
[ savedsearch "Satisfaction Ratings" refno="$refno$"]
[ savedsearch "Satisfaction Comments" refno="$refno$"]
[ savedsearch "Request Received" refno="$refno$"]
[ savedsearch "Internal Server Error" refno="$refno$"]
| table
Hi @woodcock, thank you for coming back to me with this, it is really very interesting.
I've used your suggestion on my sub searches and it works great, but when I try to use this method for the query will pulls all the subsearches together, my first subsearch contains a 'non streaming event', so unfortunately it doesn't solve my initial problem.
If you hard code a value for $refno$ do you experience the same error?
You should use the job inspector and look at search.log, etc. to see what error if any are occurring. Hopefully there is something and you can post that here.
umm... sorry i guess there isnt a job inspector on dashboard 🙂
How about index=_internal log_level=ERROR OR log_level=WARN?