Search Query to show the number of counts and then show their difference

[aditsss]

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.

[thambisetty]

The below search works only if your time range is 2 complete days. 

index="ABC" sourcetype=XYZ Timeout 
| bin _time span=1d 
| stats count by URL _time
| addinfo
| fields URL,_time,count,info_max_time,info_min_time
| convert ctime(_time) ctime(*time) timeformat="%d/%m/%Y"
| eval label=if(_time=info_min_time,"previous","current")
| chart values(count) as count over URL by label
| eval diff=previous-current
| table URL previous current diff

[aditsss]

Hi ITWhisperer,

Thanks for your guidance Your solutions always works for me. Its showing the count. Just one issue I am facing.

Suppose I select date Range as 15th to 16th September  and I am selecting one URL as "/lightning/page/home" .

Now for 15th Sepetember the count of URL is "593" and for 16th September the count of URL is "442" .

Now the difference is 151.

I Just want 151 showed show because that is correct count and only one URL is there.

But one extra URL is also coming  with 593 and 1 values and the difference is _592.These is increasing my total counts as well for my two days data.

But I am able to see two values like this:

PAGE_URL count1 count2 diff _time

/lightning/page/home	593	1	-592	2020-09-15
/lightning/page/home	442	593	151	2020-09-16

Search Query

index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| autoregress count
| where isnotnull(count_p1)
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff|sort diff|where PAGE_URL = "/lightning/page/home"

[ITWhisperer]

You need to remove the first result for each URL

index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| autoregress count
| eventstats first(_time) as firsttime by PAGE_URL
| where _time != firsttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff|sort diff|where PAGE_URL = "/lightning/page/home"

[aditsss]

Hi ITWhisperer,

Thank you ITWhisperer once again for your wonderful suggestion.

Its removing the first URL that's correct.

But its only showing the URL's which is present on both days 15th sep and 16th sep.

There are some URL's which is present on 15th September  but they are not present on 16th September or vice versa.

I want like if the particular URL is present on 15th September and 16th September then it should show the count on 15th sep say 120 and 16th sep it should be "0" or blank . so the difference will be 120 only.

Can you guide me how can I achieve this

index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| autoregress count
| eventstats first(_time) as firsttime by PAGE_URL
| where _time != firsttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff|sort diff

[ITWhisperer]

It is hard to keep up with these ever-changing requirements!

However, rather than removing the first occurrence for the URL, how about setting the count_p1 to zero

index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| autoregress count
| eventstats first(_time) as firsttime by PAGE_URL
| eval count_p1=if(_time = firsttime,0,count_p1)
| eval diff=count - count_p1
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff|sort diff

[aditsss]

Hi ITWhisperer,

Sorry for all the changing requirements .

I dont want the count_p1 to zero . My requirement is like somewhat like this:

With the below query I am able to display the correct difference between the counts . The only issue here is its only displaying the URL's which is present in both the days .

There are some URL's which are present on 15th sep but not 16th of sep or vice versa.

I want to show them as well like suppose there is one URL "/page/view" whose count is 2 and its present only on 15th september but its not present on 16th then I want to display like this.Either we can keep 16th september count as blank or we can keep 0 there so the difference will be 2 only.

URL                      count 15th sep        count 16thsep    diff

/page/view              2                                                                  2

The below Script was working correctly but its only showing the URL's which are present on both days.I want to show all the URL's.

index="ABC" sourcetype=XYZTimeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| autoregress count
| eventstats first(_time) as firsttime by PAGE_URL
| where _time != firsttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff|sort diff

[ITWhisperer]

OK This is getting complicated. The idea is that for each day we add an extra day with count as zero. We then sum the counts by day, and take only the last day of the original time period

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

[aditsss]

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.

</input>
<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| 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 count p=1
| eventstats first(_time) as firsttime by PAGE_URL
| eval count_p1=if(_time = firsttime,0,count_p1)
| stats sum(count) as count sum(count_p1) as count_p1 by PAGE_URL, _time
| addinfo
| where _time=relative_time(info_max_time-1,"@d")
| eval diff=count_p1 -count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

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.

</input>
<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| 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 count p=1
| eventstats first(_time) as firsttime by PAGE_URL
| eval count_p1=if(_time = firsttime,0,count_p1)
| stats sum(count) as count sum(count_p1) as count_p1 by PAGE_URL, _time
| addinfo
| where _time=relative_time(info_max_time-1,"@d")
| eval diff=count_p1 -count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[ITWhisperer]

"In short I want First count and last count and their difference whatever time range I select." Why didn't you explain it like this from the start?

How does this work?

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[ITWhisperer]

"In short I want First count and last count and their difference whatever time range I select." Why didn't you explain it like this from the start?

How does this work?

[ITWhisperer]

"In short I want First count and last count and their difference whatever time range I select." Why didn't you explain it like this from the start?

How does this work?

[aditsss]

Hi ITWhisperer,

This solution is awesome . 

Its calculating the 1st and last count and their difference . Just need your one more guidance.

The issue I am facing is when I select suppose15th Sep to 17th Sep from date Range drop-down.

Its also displaying the URL's that are present on 16th Sep as well .Also if URL's are not present on 17th Sep  and its present on 16th Sep and 15th Sep then its calculating the difference of 16th Sep and 15th Sep . 

I don't want 16th Sep URL's to display nor any difference of 16th Sep and 15th Sep.

If I select say15th Sep to 17th Sep then it should take all the URL's of 15th Sep and 17th Sep (1st day count and last day count and their difference)not the 16th Sep URL's.

If the URL's are present on both 15th Sep and 17th Sep then in that case it should be like this:

URL                                                 count1(15thSep)                 count2(17thSep)     diff

"/lightning/page/home"                60                                           40                                    -20            

If the URL is only present on 15th Sep and not on 17th Sep then in that case it should be like this:

URL                                                 count1(15thSep)                 count2(17thSep)           diff

"/lightning/page"                            60                                           0                                           -60

If the URL is only present on 17th Sep and not on 15th Sep then in that case it should be like this:

URL                                                 count1(15thSep)                 count2(17thSep)            diff

"/lightning/page"                            0                                                          60                            60

If I select from 15th September to 17th September then it should show all the URL's of 15th September and 17th September not of 16th September.

If I select from 14th September to 19th September then it should only show URL's  counts of 14th Sep and 19th Sep and their difference(Not any other day in between like 15thSep,16thSep etc).

I want the first day count and last day count and their difference whatever date 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 of 16th Sep and 21st Sep(the one which contains data).

URL                                                 count1(16thSep)                 count2(21stSep)        diff

"/lightning/page"                           80                                                      90                            10

Can you guide me on this.

Thanks in advance

My current XML Code:

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[ITWhisperer]

It is not possible due to your conflicting requirements.

If you have data for 15th and not 17th you want 17th (the last day in this range) to be zero, yet if you have data for 16th and not 22nd (the last day in this range) you want 16th and 21st to be used. This is not consistent.

[aditsss]

Hi ITWhisperer,

Lets just take the example if I select date range as 15th to 17th Sep. I just want 15th and 17th URL's count and not 16th Sep URL's . 

 I don't want 16th Sep URL's . Count1 should be15th Sep and Count2 should be 17th Sep and then their difference.

I don't want any 16th Sep URL's nor the difference of 15th And 16th Sep.

Is their any way to achieve this.

Something like below:

URL                                                 count1(15thSep)                 count2(17thSep)     diff

"/lightning/page/home"                60                                           40                                    -20            

Can you guide me how to achieve this

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

Hi ITWhisperer,

Can you please guide me on below. I need to complete this ASAP.

It would be a great help from your side as your suggestions always works for me.

Lets just take the example if I select date range from 15th to 17th Sep. I just want 15th and 17th URL's count and not 16th Sep URL's .

I don't want 16th Sep URL's . Count1 should be15th Sep and Count2 should be 17th Sep and then their difference.

I don't want any 16th Sep URL's nor the difference of 15th And 16th Sep.

Is their any way to achieve this.

Something like below:

URL                                            count1(15thSep)                      count2(17thSep)        diff

"/lightning/page/home"    60                                                      40                                     -20

Can you guide me how to achieve this

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

Hi ITWhisperer,

Can you please guide me on this. I need to complete this ASAP.

It would be a great help from your side.

Your suggestions always work for me please provide your guidance here.

Lets just take the example if I select date range from 15th to 17th Sep. I just want 15th Sep and 17th Sep URL's count and not 16th Sep URL's .

I don't want 16th Sep URL's . Count1 should be15th Sep and Count2 should be 17th Sep and then their difference.

I don't want any 16th Sep URL's nor the difference of 15th And 16th Sep URLS' count.

I want first day count and last day count and their difference not any other day in between.

Is their any way to achieve this.

Something like below:

URL                                            count1(15thSep)                      count2(17thSep)        diff

"/lightning/page/home"    60                                                      40                                     -20

Can you guide me how to achieve this

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

Hi ITWhisperer,

Can you please guide me on this. I need to complete this ASAP.

It would be a great help from your side.

Your suggestions always work for me please provide your guidance here.

Lets just take the example if I select date range from 15th to 17th Sep. I just want 15th Sep and 17th Sep URL's count and not 16th Sep URL's .

I don't want 16th Sep URL's . Count1 should be15th Sep and Count2 should be 17th Sep and then their difference.

I don't want any 16th Sep URL's nor the difference of 15th And 16th Sep URLS' count.

I want first day count and last day count and their difference not any other day in between.

Is their any way to achieve this.

Something like below:

URL                                            count1(15thSep)                      count2(17thSep)        diff

"/lightning/page/home"    60                                                      40                                     -20

Can you guide me how to achieve this

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

@ITWhisperer 

Can you please guide me on this. I need to complete this ASAP.

It would be a great help from your side.

Your suggestions always work for me please provide your guidance here.

Lets just take the example if I select date range from 15th Sep to 17th Sep. I just want 15th Sep and 17th Sep URL's count  and their difference and not 16th Sep URL's count.

I don't want any 16th Sep URL's  count nor the difference of 15th Sep And 16th Sep URLS' count.

I want to display the counts of all URL's that are present on 15th Sep and 17th Sep  and their difference.

I want first day count and last day count and their difference not any other day in between.

In this example I want Count1 should be15th Sep and Count2 should be 17th Sep and then their difference.

Is their any way to achieve this.

Something like below:

URL                                            count1(15thSep)                      count2(17thSep)        diff

"/lightning/page/home"    60                                                      40                                     -20

Can you guide me how to achieve this

<input type="time" token="field1" searchWhenChanged="true">
<label>Date/Time</label>
<default>
<earliest>-7d@d</earliest>
<latest>@d</latest>
</default>
</input>

<table>
<title>Timeout Exceptions</title>
<search>
<query>index="ABC" sourcetype=XYZ Timeout*
| bin span=1d _time
| stats count by PAGE_URL _time
| eventstats first(_time) as firsttime last(_time) as lasttime by PAGE_URL
| where _time = firsttime OR _time = lasttime
| autoregress count p=1
| eval count_p1=if(_time = firsttime,0,count_p1)
| where _time = lasttime
| eval diff=count_p1 - count
| rename count as count1, count_p1 as count2
| fields PAGE_URL count1 count2 diff
| sort diff</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="count">100</option>
<option name="refresh.display">progressbar</option>
</table>

[aditsss]

Hi ITWHisperer,

Is that possible . Can you please guide me on this.

Thanks in advance.

[ITWhisperer]

Please complete the following table

Dates with counts	Search time	Date to compare
16 and 17	15 - 18
16 and 17	15 - 17
16 and 17	16 - 18
16	15 - 17
16	16 - 17
16	15 - 16

[aditsss]

@ITWhisperer 

Thanks you for all your guidance. Your suggestions always worked.

In the first scenario if we select 15th Sep to 18th Sep from date range . Then it would be something like this:

URL                                                 count1(15thSep)                 count2(18thSep)     diff

"/lightning/page/home"                60                                           40                                    -20   

Only the first day count and the last day count and their difference no other day in between.

Dates with counts	Search time	Date to compare
16 and 17	15 - 18	15th and 18th counts and their difference(No  other day counts in between)
16 and 17	15 - 17	15th and 17th counts
16 and 17	16 - 18	16th and 18th counts
16	15 - 17	15th and 17th counts
16	16 - 17	16th and 17th counts
16	15 - 16	15th and 16th counts.

[aditsss]

@ITWhisperer 

Can you please guide me on below. I need to complete this ASAP.

In the first scenario if we select 15th Sep to 18th Sep from date range . Then it would be something like this:

URL                                                 count1(15thSep)                 count2(18thSep)     diff

"/lightning/page/home"                60                                           40                                    -20   

Only the first day count and the last day count and their difference no other day in between.

How can we calculate first day count and last day count and their difference.

Dates with counts	Search time	Date to compare
16 and 17	15 - 18	15th and 18th counts and their difference(No  other day counts in between)
16 and 17	15 - 17	15th and 17th counts
16 and 17	16 - 18	16th and 18th counts
16	15 - 17	15th and 17th counts
16	16 - 17	16th and 17th counts
16	15 - 16	15th and 16th counts.

[ITWhisperer · Accepted solution]

<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>