topic Re: Help with search for week event analysis in Splunk Search

Help with search for week event analysis

uagraw01 — Wed, 26 Oct 2022 14:08:44 GMT

Hello Team,

I have used to ask the same question in my previous ask :
https://community.splunk.com/t5/Splunk-Search/How-to-write-a-search-to-compare-two-weeks-errors-and-highlight/m-p/617827#M214708

I am not having the correct results while using the suggested workaround in SPL. So I have modified my SPL as below. Here my release is for 14 days and I need to compare the events with "Current_release_error" & "Last_release_error". If any new error only present in current release then I want to call out those results. Pease suggest some value workarounds.

index="ABC" source="/abc.log" ("ERROR" OR "EXCEPTION") earliest=-14d latest=now() | rex "Error\s(?<Message>.+)MulesoftAdyenNotification" | rex "fetchSeoContent\(\)\s(?<Exception>.+)" | rex "Error:(?<Error2>.+)" | rex "(?<ErrorM>Error in template script)+" | rex "(?ms)^(?:[^\\|\\n]*\\|){3}(?P<Component>[^\\|]+)" | rex "service=(?<Service>[A-Za-z._]+)" | rex "Sites-(?<Country>[A-Z]{2})" | eval Error_Exception= coalesce(Message,Error2,Exception,ErrorM) | eval Week=case(_time<relative_time(now(),"-14d@d"),"Current_release_error",_time>relative_time(now(),"-28d@d-14d@d"),"Last_release_error") | stats dc(Week) AS Week_count values(Week) AS Week BY Error_Exception | eval Week=if(Week_count=2,"Present in Previous Release",Week) | where Week_count=1

Re: Help with search for week event analysis

yuanliu — Mon, 07 Nov 2022 01:00:33 GMT

You really need to explain what "not having the correct results" really means with illustration of your data (anonymize as needed), and the output you get plus the SPL attempted. In the previous post, @gcusello gave the correct answer for discerning new errors by week in a two-week period. Here, you are asking for new errors by release, where release is defined as a two-week period. But your search window is limited to two weeks (earliest=-14d). Could this be the reason why you cannot find new errors? Effectively, this eval

| eval Week=case(_time<relative_time(now(),"-14d@d"),"Current_release_error",_time>relative_time(now(),"-28d@d-14d@d"),"Last_release_error")

will always return "Current_release_error" because you only have 14 days of data to work with due to earliest=-14d. You may get more sensible result if your first search command

index="ABC" source="/abc.log" ("ERROR" OR "EXCEPTION") earliest=-28d@d latest=-0d@d

Even so, your last where command is not restrictive enough. In gcuello's code, there is an additional condition,

| where Week == "Last_release_error" AND Week_count == 1

This said, you must have also realized that working with "release" is fundamentally different from working with calendar week, and working with calendar week is different from working with 7-day periods. Release is a local business construct that is not merely a 14-day period. It starts and ends at fixed calendar dates. In other words, events that happened less than 14 days ago could belong to the previous release. (Unless you only run this search at the end of each release.) When I work with such a local construct, I resort to lookup. I.e., enter the start and end dates of each release into a lookup table, then calculate based on lookup output.

If you must calculate "release" without lookup, use absolute dates such as earliest="10/12/2022:00:00:00", and calculate that "Week" based on absolute dates, too.

Re: Help with search for week event analysis

bowesmana — Mon, 07 Nov 2022 04:37:38 GMT

Your search is for only 14 days, so your Week=case... statement is meaningless, do you mean

index="ABC" source="/abc.log" ("ERROR" OR "EXCEPTION") earliest=-28d latest=now() | rex "Error\s(?<Message>.+)MulesoftAdyenNotification" | rex "fetchSeoContent\(\)\s(?<Exception>.+)" | rex "Error:(?<Error2>.+)" | rex "(?<ErrorM>Error in template script)+" | rex "(?ms)^(?:[^\\|\\n]*\\|){3}(?P<Component>[^\\|]+)" | rex "service=(?<Service>[A-Za-z._]+)" | rex "Sites-(?<Country>[A-Z]{2})" | eval Error_Exception= coalesce(Message,Error2,Exception,ErrorM) | eval Week=if(_time<relative_time(now(),"-14d@d"), "Last_release_error", "Current_release_error") | stats dc(Week) AS Week_count values(Week) AS Week BY Error_Exception | eval Week=if(Week_count=2,"Present in Previous Release",Week) | where Week_count=1

i.e. I updated the earliest range to be 28d and the Week/case is changed.

Note that your first time test for _time LESS THAN relative_time(now(), "-14d@d" was set as "Current" week, but if it is LESS than, it would be OLDER, so previous release.

Not sure if this will fix your issues though

Re: Help with search for week event analysis

uagraw01 — Wed, 09 Nov 2022 04:16:05 GMT

@yuanliu @bowesmana let me concise and avoid the confusion.

Take an example :

I need two panel , in first panel i want to all the errors in last release ( 14 days is the release period ). And new release start just after the last release end. So in second panel which is current release, i want to highlight the new error by comparing the last release errors. So how i compare two panel results and highlights the new errors.

Re: Help with search for week event analysis

yuanliu — Wed, 09 Nov 2022 08:56:00 GMT

First thing you need to consider is how to define release boundary. As I examined previously, your best bet is to use a lookup to determine which data belongs to which release because it is not easy to specify on search line. (You can also hard code "release" in a macro. But that's a lot more work than lookup.) In most cases, number of days or weeks will not give you the correct boundaries.

Here, I'll use hard coded definition

| eval release_0_start = strptime("2022-10-16", "%F"), release_1_start = strptime("2022-10-30", "%F"), release_2_start = strftime("2022-11-13", "%F") | eval release = case(release_0_start < _time AND _time <= release_1_start, "release 1", release_1_start < _time AND _time <= release_2_start, "release_2", true(), "other")

Given today's date, "release_0" is equivalent to "previous release", "release_1" is equivalent to "current release", and "release_2" will be the next release.

Using these definitions, your first panel would be something like

index="ABC" source="/abc.log" ("ERROR" OR "EXCEPTION") earliest="10/16/2022:00:00:00" latest="10/30/2022:00:00:00" | extraction, reporting, etc...

The second panel, new errors in release_1, can be derived from

index="ABC" source="/abc.log" ("ERROR" OR "EXCEPTION") earliest="10/16/2022:00:00:00" latest=now() | rex "Error\s(?<Message>.+)MulesoftAdyenNotification" | rex "fetchSeoContent\(\)\s(?<Exception>.+)" | rex "Error:(?<Error2>.+)" | rex "(?<ErrorM>Error in template script)+" | rex "(?ms)^(?:[^\\|\\n]*\\|){3}(?P<Component>[^\\|]+)" | rex "service=(?<Service>[A-Za-z._]+)" | rex "Sites-(?<Country>[A-Z]{2})" | eval Error_Exception= coalesce(Message,Error2,Exception,ErrorM) | eval release_0_start = strptime("2022-10-16", "%F"), release_1_start = strptime("2022-10-30", "%F"), release_2_start = strftime("2022-11-13", "%F") | eval release = case(release_0_start < _time AND _time <= release_1_start, "release 1", release_1_start < _time AND _time <= release_2_start, "release_2", true(), "other") ``` by this line, each event will have the artificially marked release ``` | stats count by Error_Exception release | eventstats values(release) by Error_Exception | where mvcount('values(release)') == 1 AND release == "release_1" | fields count Error_Exception ``` this lists errors in release_1 only ```

Re: Help with search for week event analysis

uagraw01 — Fri, 11 Nov 2022 04:40:32 GMT

@yuanliu Yes You are true we can't harcode the baseline time for Post and Pre release date. So to overcome from this issue I have used two different panel. 1st panel is the Pre release & 2nd is the post release. As I need to compare both panels events and identify any new errors occur in Post release. Please Provide your expert suggestion.

Note: I have used below codes but it is taking so much time to populate the results and its impacts on my search environment in Splunk.

Re: Help with search for week event analysis

uagraw01 — Fri, 11 Nov 2022 04:57:43 GMT

@yuanliu Sorry forgot to attach the screenshot. With continue my comment.