topic Re: Comparing last 2 weeks average against yesterday's events in Splunk Search

Comparing last 2 weeks average against yesterday's events

dpatiladobe — Wed, 15 Nov 2017 08:07:46 GMT

I am trying to get last 2 weeks data and avg over week day's and compare that against event count of yesterday to detect any issue.

index=xxxxx  host="xxxxx" earliest=-2w@w latest=@w date_wday!=saturday date_wday!=sunday| bin span=1d _time | eval marker=if (_time<relative_time(now(),"-w@w"), "LastWeek_Weekdays","ThisWeek_Weekdays") | eval _time=if(marker=="LastWeek_Weekdays", _time + 7*24*60*60, _time) | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId   |  chart count  by TriggerId limit=0 marker |  eval  LastWeek_Weekdays_Avg=round(LastWeek_Weekdays/5,0) | append [ search host="xxxx" earliest=-1d@d latest=@d | bin span=1d _time  | eval marker=if (_time<relative_time(now(),"-1d@d"), "Yesterday","Today") | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId |  chart count  by TriggerId, marker  ] | eval diff_In_Percentage=((Yesterday-LastWeek_Weekdays_Avg)/LastWeek_Weekdays_Avg)*100

And O/p should look like

    TriggerId   LastWeek_Weekdays   LastWeek_Weekdays_Avg   ThisWeek_Weekdays Yesterday diff_inPercentage
xyz 32301   6460    26118 xx xx%
abc 2146    429 1876 xx xx%
abc123  4   1   5 xx xx%

Re: Comparing last 2 weeks average against yesterday's events

anjambha — Wed, 15 Nov 2017 09:23:15 GMT

Hi.. you can try append OR join splunk command as per your requirement..

refer below link

http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Append

http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Join

Re: Comparing last 2 weeks average against yesterday's events

dpatiladobe — Wed, 15 Nov 2017 17:27:12 GMT

I have updated the question.

Re: Comparing last 2 weeks average against yesterday's events

somesoni2 — Wed, 15 Nov 2017 18:56:56 GMT

Give this a try

index=xxxxx  host="xxxxx" earliest=-2w@w latest=@w date_wday!=saturday date_wday!=sunday| bin span=1d _time | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId   | stats count by TriggerId _time |  stats avg(count) as count LastWeek_Weekdays_Avg by TriggerId |  eval  LastWeek_Weekdays_Avg=round(LastWeek_Weekdays/5,0) | append [ search host="xxxx" earliest=-1d@d latest=@d | bin span=1d _time  | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId |  stats count as Yesterday by TriggerId ] | stats values(LastWeek_Weekdays_Avg) as LastWeek_Weekdays_Avg values(Yesterday) as Yesterday by TriggerId | eval diff_In_Percentage=((Yesterday-LastWeek_Weekdays_Avg)/LastWeek_Weekdays_Avg)*100

Re: Comparing last 2 weeks average against yesterday's events

dpatiladobe — Wed, 15 Nov 2017 22:22:23 GMT

I was able to get work around with below query but if TriggerId not present in last 2 week and present in yesterday data then it mess up all the calculation

index=xxx  host="xxxx" source="xxx*.log*"  earliest=-2w@w latest=@w date_wday!=saturday date_wday!=sunday| bin span=1d _time | eval marker=if (_time<relative_time(now(),"-w@w"), "Last2Week_Weekdays","LastWeek_Weekdays") | eval _time=if(marker=="Last2Week_Weekdays", _time + 7*24*60*60, _time) | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId   |  chart count  by TriggerId limit=0 marker |  eval  Last2Week_Weekdays_Avg=round(Last2Week_Weekdays/5,0) | eval  LastWeek_Weekdays_Avg=round(LastWeek_Weekdays/5,0)| appendcols [ search host="xxxx" source="xxx*.log*" earliest=-1d@d latest=@d   date_wday!=saturday date_wday!=sunday| bin span=1d _time  | eval marker=if (_time<relative_time(now(),"-0d@d"), "Yesterday","ThisWeek_Weekdays") |eval _time=if(marker=="Yesterday", _time + 1*24*60*60, _time) | rex "Current Partition:(?<PartitionNumber>\d+),Offset:(?<Offset>\d+),triggerID:(?<TriggerId>\S+),Outputsystem:\d+,IsprodTrigger:\S+,triggerTimeStamp:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartTime:\d+-\d+-\d+\W+\d+:\d+:\d+.\d+,StartDelay:(?<DelayLag>\d+),batchCount:(?<batchCount>\d+),timeGT:(?<createtime>\d+-\d+-\d+\W+\d+:\d+:\d+.\d+)" |dedup PartitionNumber , Offset ,TriggerId |  chart count by TriggerId ,marker ] | eval diff_In_Percentage_Week_2=((Yesterday-Last2Week_Weekdays_Avg)/Last2Week_Weekdays_Avg)*100 | eval diff_In_Percentage_Week_Last=((Yesterday-LastWeek_Weekdays_Avg)/LastWeek_Weekdays_Avg)*100

Re: Comparing last 2 weeks average against yesterday's events

somesoni2 — Tue, 29 Sep 2020 16:48:38 GMT

You can assign a default values, say 0 to LastWeek_Weekdays_Avg field if it's null (TriggerId present yesterday but not Last 2 weeks) and vice versa. OR you can just put NA where any one of Yesterday or LastWeek_Weekdays_Avg field is null, so that no calculation will be done.

Re: Comparing last 2 weeks average against yesterday's events

dpatiladobe — Thu, 16 Nov 2017 04:36:03 GMT

Thanks . instead of append i used joined and it works perfect.