Hi Everyone,
I have one requirement.
I have one dashboard which several URL's and its showing the count as total.
Like I select 12th to 13th sep then its showing like this:
URL Count
/light/page/jk 1184
/light/o/Case/home 110
I have one date drop-down.I want when I select say 12th to 13th sep then instead of showing total count it should show individual counts and then their difference.
URL Count1 Count2 Difference
/light/page/jk 45 35 10
My serach query:
index="ABC" sourcetype=XYZ Timeout |stats count by URL | sort -count
Can someone guide me How Can I achieve this.
Thanks in advance.
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| streamstats count as row by PAGE_URL
| addinfo
| eval count=if(row = 1, mvappend(count,count,count),count)
| mvexpand count
| streamstats count as row by PAGE_URL
| eval count=if(row < 3,0,count)
| eval _time=if(row=1,info_min_time,_time)
| eval _time=if(row=2,relative_time(info_max_time-1,"@d"),_time)
| eval _time=relative_time(_time,"@d")
| stats sum(count) as count by PAGE_URL _time
| eventstats earliest(_time) as earliest latest(_time) as latest by PAGE_URL
| where _time = earliest OR _time = latest
| autoregress count p=1
| where _time = latest
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
index="ABC" sourcetype=XYZ Timeout
| bin span=1d _time
| stats count by URL _time
| autoregress count
| where isnotnull(count_p1)
| eval diff=count - count_p1
| rename count as count1, count_p1 as count2
| fields URL count1 count2 diff
Hi ITWhisperer,
The below script is working correctly but only for the last and previous day.
Means when I select two days say 15th Sep and 16th Sep then its showing correct counts and their difference so this is fine.
But suppose I select 16th Sep to 19th Sep then its taking count of 18th Sep and 19th Sep means the last day and previous day .
I want to calculate the 1st count and the last count and their difference whatever time range I select. So suppose I select 16th Sep to 19th Sep then in this case it should show the count of 16th Sep and count of 19th Sep and their difference .
So suppose I select last 7 days from date picker that is 16th Sep to 22nd Sep then it should show the count of 16th Sep and 22nd Sep and their difference.
In short I want First count and last count and their difference whatever time range I select.
Also it should be non-zero count means if I select last 7 days that is 16th Sep to 22nd Sep and there is no data for 22nd Sep (0 data) then in that case the Counts will be 16th Sep and 21st Sep(the one which contains data).
Can you please suggest me on this. Thanks in advance.
index="ABC" sourcetype=XYZTimeout*
| bin span=1d _time
| stats count by PAGE_URL _time
/* create an event for following day with count = 0 */
| eval tomorrow=_time+(60*60*24)
| eval time=mvappend(_time,tomorrow)
| mvexpand time
| eval count=if(time=tomorrow,0,count)
| eval _time=time
| fields - time tomorrow
/* autoregress to get previous day's count */
| autoregress count p=1
/* set previous day's count to 0 for the first event for the PAGE_URL */
| eventstats first(_time) as firsttime by PAGE_URL
| eval count_p1=if(_time = firsttime,0,count_p1)
/* join the daily counts together */
| stats sum(count) as count sum(count_p1) as count_p1 by URL, _time
/* only keep the event for the last day of the query time */
| addinfo
| where _time=relative_time(info_max_time-1,"@d")
/* calculate the difference between the counts for the last day and the previous day */
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff
Hi ITWHisperer,
Is that possible . Can you please guide me on this.
Thanks in advance.