Splunk Search

Why is my scheduled search that includes join and append missing data for some rows in the report?

Navanitha
Path Finder

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.

0 Karma
1 Solution

woodcock
Esteemed Legend

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 ]

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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"]
0 Karma

woodcock
Esteemed Legend

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 ]
0 Karma

Navanitha
Path Finder

Thanks Woodcock,

It worked, I only need to make some minor changes like removing the null values and date formatting.

0 Karma

woodcock
Esteemed Legend

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).

0 Karma

Navanitha
Path Finder

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 ]
0 Karma

woodcock
Esteemed Legend

Your foreach code got clipped. Please login and click edit and fix them.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...