How to create a query as below, but I would like to get the latest data for a field within span?

[vgiri8]

Latest data within a time span. I have a query as below, but I would like to get the latest data for a field within span of 1w.

index=my_index | timechart span=1w estdc(host) by site

I would like to get the latest data for field "encrypted=false" within the span=1w for all host by site

Edit: encrypted=false changed from true

Edit 2:
Summary of What I am trying to get as clearly articulated by @ITWhisperer 
"So my guess was right - this is what the search is basically doing

For each week, it gets the latest encryption state for each host on each site

Then keeps only those statistics where the state is false

Then counts to events (one for each host with encryption false for that week) by week and site"

Finally, it reorganises the data into chart format.

[ITWhisperer]

Do you mean something like this?

index=my_index | where encrypted="true" | timechart span=1w estdc(host) by site

[vgiri8]

Hi @ITWhisperer ,

I am searching for encrypted=false. So changing the below example to reflect that ( Sorry for the confusion in the Original post) 

If I add.    // where encrypted="false". //    early in the query, search will only look for encrypted=false and not the  latest results for a given host, which could change within the span time frame(span=1w) and then I would get the count of the host which have encrypted= false, but that is not correct count. I mean I would get the results for all encrypted=false within the span but not the latest state within the time frame. 

[ITWhisperer]

Do you mean something like this?

index=my_index 
| bin _time as week span=1w
| stats latest(encrypted) as encrypted by week host site

[vgiri8]

Hi @ITWhisperer ,

I am still looking for trend chart to show, thats why I am using timechart with the count of host per site per week

[vgiri8]

index=my_index encrypted=false | timechart span=1w estdc(host) by site

How do I get the latest encrypted data which is false , removing encrypted=false from the main search. 

[ITWhisperer]

I am still not sure what it is you are trying to show, but here is my next guess

index=my_index 
| bin _time as week span=1w
| stats latest(encrypted) as encrypted by week host site
| where encrypted="false"
| stats count by week site
| xyseries week site count

[vgiri8]

Yes you are correct.

I have one last question, how do I change the week to more readable format from current in the screenshot. I can do eval, but where do I add it in the query?

[ITWhisperer · Accepted solution]

index=my_index 
| bin _time as week span=1w
| stats latest(encrypted) as encrypted by week host site
| rename week as _time
| where encrypted="false"
| stats count by _time site
| xyseries _time site count

[vgiri8]

Thank you so much @ITWhisperer 

[vgiri8]

Hi @ITWhisperer ,

Finally seeing data as required. I had few questions,

I feel to do this, so that I only get single count per host, since I could have multiple entries in a week for the same host, if I use "count by _time"

| stats count by _time site

replace it with 

| stats dc(host) by site

your thoughts

[ITWhisperer]

stats dc(host) by site gives you the count of distinct hosts per site

stats count by host site gives a statistics event per unique site - effectively this is a distinct event for each host so by counting the statistics events from the first stats you get the same as the dc(host).

[vgiri8]

I am trying to get a trend chart per site per week, for all host which had encryption=false within a given week. 

Basically trying to see the trend and check if we are improving week by week in encryption=false, basically needs to reduce.

I am sorry If I was not clear before. 

[ITWhisperer]

So my guess was right - this is what the search is basically doing

For each week, it gets the latest encryption state for each host on each site

Then keeps only those statistics where the state is false

Then counts to events (one for each host with encryption false for that week) by week and site

Finally, it reorganises the data into chart format.

[vgiri8]

I currently use this to get the data for list of host, this query only gives me the latest data of host.

index=my_index | stats latest(encrypted) AS Encrypted BY host | where Encrypted="false"