Splunk Search

Enrich event with number of business days till end of the month

SP987541
Explorer

I would like to include an evaluated field to the events returned in the search containing the number of business days between the timestamp of the event and the end of the event month. I have a subsear returning the number of business days untill the end of the month:

eval TtoEOM=[search * Earliest=$eventtime$ latest=+1month@month | timechart count by host span=1d | appendpipe [|stats count |addinfo | eval temp=info_min_time."##".info_max_time | makemv temp delim="##" | mvexpand temp | eval count=0 | eval _time=temp | table _time count] | timechart span=1d sum(count) as count | fillnull | eval dayname = strftime(_time, "%a") | eval target = strftime(_time, "%d%m%y") |search dayname!=Sat dayname!=Sun | stats count as ret | return $ret]

However, I'm unble to get the event time into the Earliest parameter of the subsearch.
Thank you!

1 Solution

Richfez
SplunkTrust
SplunkTrust

Neat search!

I noticed that you are having trouble with earliest, and also notice you've spelled it in your example Earliest. Try changing it to all lower case. Without that change TtoEOM (in my testing) is some massive number, but when I change it TtoEOM returns a very reasonable number.

Like so:

eval TtoEOM=[search * earliest=$eventtime$ latest=+1month@month | timechart count by host span=1d | appendpipe [|stats count |addinfo | eval temp=info_min_time."##".info_max_time | makemv temp delim="##" | mvexpand temp | eval count=0 | eval _time=temp | table _time count] | timechart span=1d sum(count) as count | fillnull | eval dayname = strftime(_time, "%a") | eval target = strftime(_time, "%d%m%y") |search dayname!=Sat dayname!=Sun | stats count as ret | return $ret]

View solution in original post

0 Karma

SP987541
Explorer

Thank you for your valuable inputs! Based on those inputs I've been looking around some more and found this query that does provide me with the result (i.e. enrich my events with an attribute counting the number of business days between event date and the end of the month):

  • | transaction IDT | eval start=relative_time(_time,"+1d@d")| eval end=relative_time(_time,"+1month@month") | eval Date=mvrange(start,end,86400) | convert ctime(Date) timeformat="%+"| eval NoOfBusinessDays=mvcount(mvfilter(NOT match(Date,"(Sun|Sat).*"))) | fields NoOfBusinessDays
0 Karma

woodcock
Esteemed Legend

I spent quite a bit of time on this and cannot figure out a good way to do it. I did, however, figure out a better way to do the math:

| eval WorkdaysUntilEndOfCurrentMonth = [|gentimes
[|noop | stats count AS start | eval start=strftime(now(),                             "%m/%d/%Y")
   | return start=start]
[|noop | stats count AS end   | eval   end=strftime(relative_time(now(), "+1mon@mon"), "%m/%d/%Y")
   | return     end=end]
| search starthuman!="Sat*" AND starthuman!="Sun*" | stats count as WorkdaysUntilEndOfCurrentMonth
   | return $WorkdaysUntilEndOfCurrentMonth]
0 Karma

Richfez
SplunkTrust
SplunkTrust

Neat search!

I noticed that you are having trouble with earliest, and also notice you've spelled it in your example Earliest. Try changing it to all lower case. Without that change TtoEOM (in my testing) is some massive number, but when I change it TtoEOM returns a very reasonable number.

Like so:

eval TtoEOM=[search * earliest=$eventtime$ latest=+1month@month | timechart count by host span=1d | appendpipe [|stats count |addinfo | eval temp=info_min_time."##".info_max_time | makemv temp delim="##" | mvexpand temp | eval count=0 | eval _time=temp | table _time count] | timechart span=1d sum(count) as count | fillnull | eval dayname = strftime(_time, "%a") | eval target = strftime(_time, "%d%m%y") |search dayname!=Sat dayname!=Sun | stats count as ret | return $ret]
0 Karma

woodcock
Esteemed Legend

Show us a run-anywhere example of this actually working end-to-end. I don't see how you can pass outer search fields to a subsearch without using map which defeats the whole purpose.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...