Search running slowly

[shazbot79]

Hi, can anyone make any suggestions as to how I can make this search more efficient?

index=prod_service_now sourcetype=snow:incident number=INC* |  fields opened_at dv_assignment_group sys_id | dedup sys_id 
|search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"
| eval now = now()
| eval now = relative_time(now,"@w1")
| eval now = relative_time(now,"-52w")
| eval earliest = relative_time(now,"-52w") 
| eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S") 
| where _time >= earliest AND _time < now 
| eval new_time = relative_time(strptime(opened_at,"%Y-%m-%d%H:%M:%S"), "+52w") 
| eval _time = relative_time(new_time,"@w1") 
| eval ReportKey = "LASTYEAR"
| append [ search index=prod_service_now sourcetype=snow:incident number=INC* |  fields opened_at dv_assignment_group sys_id | dedup sys_id 
| search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"
| eval now = now()
| eval now = relative_time(now,"@w1")
| eval earliest= relative_time(now, "-52w") 
| eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S") 
| where _time >= earliest AND _time < now 
| eval _time = relative_time(strptime(opened_at,"%Y-%m-%d%H:%M:%S"), "@w1")
| eval ReportKey = "CURRENTYEAR"]
| chart count by _time, ReportKey

Thanks

[bowesmana]

@shazbot79 

Several things stand out

index=prod_service_now sourcetype=snow:incident number=INC* 
| fields opened_at dv_assignment_group sys_id
| dedup sys_id 
| search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"

You are searching data, running a dedup and then running more searching on that data.

First put your search criteria in the original search 

index=prod_service_now sourcetype=snow:incident number=INC* dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"
| fields opened_at dv_assignment_group sys_id
| dedup sys_id 

Secondly, you are doing an append and a subsearch that is exactly the same as the primary search. 

Thirdly, you are not showing the time range earliest/latest values for your search but with all the time range filtering you are doing IN_SEARCH, it would indicate that you are searching a load of data then filtering out the data falling outside your desired time range with the where clause.

How does _time in the ORIGINAL event data related to the time of the 'opened_at' value? You are basically trying to find last 52 weeks (to @w1) and 52 weeks prior to that 

Splunk has a time range for a search which you should use and to cover the window you are after, then you just need the single search with an if statement indicating the year, something like

index=prod_service_now sourcetype=snow:incident number=INC* dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"
| fields opened_at dv_assignment_group sys_id 
| dedup sys_id 
| eval current_year_end = relative_time(now(),"@w1")
| eval current_year_start = relative_time(current_year_end, "-52w") 
| eval last_year_end = current_year_start
| eval last_year_start = relative_time(last_year_end,"-52w")
| eval opened_at_time = strptime(opened_at,"%Y-%m-%d%H:%M:%S") 
| where opened_at_time >= last_year_start AND opened_at_time < current_year_end 
| eval ReportKey = if(opened_at_time<last_year_end, "LASTYEAR", "CURRENTYEAR")
| eval time = if(opened_at_time<last_year_end,relative_time(opened_at_time, "+52w@w1"),relative_time(opened_at_time, "@w1"))
| chart count by _time, ReportKey

This is calculating the monday-52w and monday-104w and then filtering out those outside that range, then depending on where the opened_at_time sits, either last or current year, will set the key and then shift last year's date onto current year's date.

append and subsearches are poor performers, so avoid wherever possible - and using the if() construct above is a very simple way to avoid them.

Hope this helps. I cannot validate this search, but it should work 🙂

Hope this helps

[to4kawa]

1. what is the unit of time in the final table? (Days? Month?
2. what do you want to show for last year and this year at the same time?

sample:

| tstats count where index=_internal  earliest=-1month by _time span=1d
| eval Report=if(_time <=relative_time(now(),"-1w@d"),"last_week","current_week")
| where _time > relative_time(now(),"-2w@d")
| eval weekday=strftime(_time,"%A")
| stats values(_time) as time sum(count) as count by  weekday Report
| eventstats max(time) as _time by weekday
| xyseries _time Report time

After searching in bulk, it is fast to label with eval and aggregate the results.

[shazbot79]

Hi, thanks for this. One of the difficult things with Service Now is every time a record in service now is updated, a new event is imported into Splunk. This means I can't filter on the base search for any field that could change during the life of an incident record. I need to do the dedup first. I also need to use the opened_at or resolved_at field rather than the default updated_at field that Splunk assigns to _time. If I filter using _time (using the time picker or earliest/latest) I risk missing records which were opened outside of the window. Does that make sense?

The unit of time for the final table is weeks. I want to show the number of records opened each week.

Thanks

[to4kawa]

> I risk missing records which were opened outside of the window. 

I think this should use csv to solve.