topic Re: Alternative to Join Subsearch to avoid 50k results limit in Splunk Search

Alternative to Join Subsearch to avoid 50k results limit

mjhermansky — Thu, 19 Sep 2019 17:07:08 GMT

I can use the following search to get 1 day worth of data, but anything longer causes the subsearch to hit its limit. Please Help

index=x_default sourcetype="x.alarm.y.norm" device_type=term   description="EQUIP is Inactive" OR description="EQUIP LOS" OR description="EQUIP is inactive" 
| eval event_time= strptime(trigger_time, "%Y-%m-%d %H:%M:%S") 
   | convert timeformat="%Y-%m-%d" ctime(event_time) AS event_date 
| join type=inner equip_serial_number event_date [ search index=sperf_default sourcetype=common.ticket.opened.norm report_type=TR
| eval report_time= strptime(reported_date, "%m/%d/%Y %H:%M:%S") 
   | convert timeformat="%Y-%m-%d" ctime(report_time) AS event_date]
| chart dc(equip_serial_number) dc(report_num) BY event_date

Thank you in advance for your help

Re: Alternative to Join Subsearch to avoid 50k results limit

gcusello — Fri, 20 Sep 2019 07:00:59 GMT

Hi mjhermansky,
the logic is to create a search with both the searches with OT clause, to use the stats command using BY option for the key fields, then you pass as values the fields you need,
in other words, something like this:

(index=x_default sourcetype="x.alarm.y.norm" device_type=term   description="EQUIP is Inactive" OR description="EQUIP LOS" OR description="EQUIP is inactive") OR (index=sperf_default sourcetype=common.ticket.opened.norm report_type=TR)
 | eval event_time= strptime(trigger_time, "%Y-%m-%d %H:%M:%S") 
    | convert timeformat="%Y-%m-%d" ctime(event_time) AS event_date 
 | eval report_time= strptime(reported_date, "%m/%d/%Y %H:%M:%S") 
    | convert timeformat="%Y-%m-%d" ctime(report_time) AS event_date]
| stats values(event_time) AS event_time values(event_date ) AS event_date values(report_time) AS report_time BY equip_serial_number event_date
 | chart dc(equip_serial_number) dc(report_num) BY event_date

Obviously, you have to check if in your stats command you have all fields you need (in this case you can add another values option) and to check the values you have in your stats options: if you have more values and you need only one you can use a different option like earliest, latest or max ...

Bye.
Giuseppe

Re: Alternative to Join Subsearch to avoid 50k results limit

mjhermansky — Wed, 30 Sep 2020 02:18:46 GMT

When I tried this, I got the following error:

Error in 'stats' command: The output field 'event_date' cannot have the same name as a group-by field.

My goal is to find the count of report_num that have the same equip_serial_number and had a reported_date on the same day as the trigger_time.

Re: Alternative to Join Subsearch to avoid 50k results limit

gcusello — Fri, 20 Sep 2019 13:40:23 GMT

Sorry, my repetition error

| stats values(event_time) AS event_time values(report_time) AS report_time BY equip_serial_number event_date

Bye.
Giuseppe