In a single value trend interval, I am trying to compare number of certs issued "from start of the day to current time" to "start of the day to same time yesterday". Currently, it compares with yesterday, and in the morning when were certs issued for today, there are very few and it doesn't make any sense.
Try this.
sourcetype=WinEventLog:Security (EventCode=4886 OR EventCode=4887 OR EventCode=4888 ) earliest=-1d@d | transaction RequestID | fields _time Msg | where like(Msg,\"%received a certificate request%\") | where strftime(now(), "%H")>strftime(_time, "%H") | bin span=1d _time | stats count AS Requests by _time
I use this method to compare
index=_internal metrics main earliest=-2d@d latest=@d
| eval yesterday_count=if(_time>relative_time(now(),"-2d@d") AND _time<relative_time(now(),"-1d@d"), 1, 0)
| eval today_count=if(_time>relative_time(now(),"-1d@d") AND _time<relative_time(now(),"@d"), 1, 0)
| stats sum(yesterday_count) as yesterday_total sum(today_count) as today_total
Thanks. But I want to fit this with the Single Value trend interval. How it compares is with date/time modifiers earliest and latest in the element setting trendInterval.
Like it says: 44 certs today down by 68 as it compares certs issued today with total certs issued yesterday.
If you don't mind, if you could post a sample of the data, and the query you are using, and the actual output, and the desired output, then I could help you more.
Well the search I am using is:
sourcetype=WinEventLog:Security (EventCode=4886 OR EventCode=4887 OR EventCode=4888 ) | transaction RequestID | table _time,Msg | where like(Msg,\"%received a certificate request%\") | eval date = tostring(strftime(_time,\"%Y%m%d%H%M\")) | eval _time=strptime(date, \"%Y%m%d%H%M\") | timechart count(Msg) AS Requests span=24h"
And single value element settings are:
var element1 = new SingleElement({
"id": "element1",
"showSparkline": "1",
"numberPrecision": "0",
"rangeValues": "[0]",
"underLabel": "compared to previous 24 hours",
"trendInterval": "-24h",
"trendDisplayMode": "absolute",
"colorBy": "value",
"drilldown": "all",
"linkView": "search",
"colorMode": "block",
"useColors": "1",
"showTrendIndicator": "1",
"rangeColors": "[\"0xFF0000\",\"0x008000\"]",
"height": "160",
"unitPosition": "after",
"useThousandSeparators": "1",
"trendColorInterpretation": "standard",
"managerid": "search1",
"el": $('#element1')
}, {tokens: true, tokenNamespace: "submitted"}).render();
this compares certs issued today (up to current time) with certs issued yesterday the whole day.
I just need is to compare with the interval mid-night yesterday to current time with mid-night the previous day to same yesterday.