Reporting

Why am I getting no results from my saved search with append when I extend the range on the dashboard time picker?

Motivator

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

0 Karma
1 Solution

Esteemed Legend

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"

View solution in original post

Esteemed Legend

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"

View solution in original post

Motivator

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

0 Karma

Motivator

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.

0 Karma

Influencer

If you hard code a value for $refno$ do you experience the same error?

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!