topic Re: Search running slowly in Splunk Search

Search running slowly

shazbot79 — Wed, 13 Jan 2021 18:29:47 GMT

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

Re: Search running slowly

to4kawa — Sat, 16 Jan 2021 00:41:15 GMT

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.

Re: Search running slowly

bowesmana — Sun, 17 Jan 2021 23:56:35 GMT

@shazbot79

Several things stand out

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

Re: Search running slowly

shazbot79 — Thu, 28 Jan 2021 18:12:27 GMT

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

Re: Search running slowly

to4kawa — Fri, 29 Jan 2021 21:27:20 GMT

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

I think this should use csv to solve.