https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609321#M211885 <P>stats dc(host) by site gives you the count of distinct hosts per site</P><P>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).</P> Fri, 12 Aug 2022 12:29:29 GMT ITWhisperer 2022-08-12T12:29:29Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609275#M211864 <P>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.</P> <P>&nbsp;</P> <P>index=my_index | timechart span=1w estdc(host) by site</P> <P>&nbsp;</P> <P>I would like to get the latest data for field "encrypted=false" within the span=1w for all host by site<BR /><BR />Edit: encrypted=false changed from true<BR /><BR /></P> <P>Edit 2:<BR />Summary of What I am trying to get as clearly articulated by <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/225168">@ITWhisperer</a>&nbsp;<BR />"So my guess was right - this is what the search is basically doing</P> <P>For each week, it gets the latest encryption state for each host on each site</P> <P>Then keeps only those statistics where the state is false</P> <P>Then counts to events (one for each host with encryption false for that week) by week and site"</P> <P>Finally, it reorganises the data into chart format.</P> Fri, 12 Aug 2022 15:19:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609275#M211864 vgiri8 2022-08-12T15:19:37Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609285#M211869 <P>Do you mean something like this?</P><LI-CODE lang="markup">index=my_index | where encrypted="true" | timechart span=1w estdc(host) by site</LI-CODE> Fri, 12 Aug 2022 09:08:24 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609285#M211869 ITWhisperer 2022-08-12T09:08:24Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609293#M211871 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/225168">@ITWhisperer</a>&nbsp;,</P><P>I am searching for encrypted=false. So changing the below example to reflect that ( Sorry for the confusion in the Original post)&nbsp;</P><P>If I add.&nbsp; &nbsp; // where encrypted="false". //&nbsp; &nbsp; early in the query, search will only look for encrypted=false and not the&nbsp; 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.&nbsp;</P> Fri, 12 Aug 2022 09:38:09 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609293#M211871 vgiri8 2022-08-12T09:38:09Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609295#M211872 <P>I currently use this to get the data for list of host, this query only gives me the latest data of host.<BR /><BR />index=my_index | stats latest(encrypted) AS Encrypted BY host | where Encrypted="false"</P> Fri, 12 Aug 2022 09:42:52 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609295#M211872 vgiri8 2022-08-12T09:42:52Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609297#M211873 <P>Do you mean something like this?</P><LI-CODE lang="markup">index=my_index | bin _time as week span=1w | stats latest(encrypted) as encrypted by week host site</LI-CODE> Fri, 12 Aug 2022 09:46:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609297#M211873 ITWhisperer 2022-08-12T09:46:44Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609300#M211874 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/225168">@ITWhisperer</a>&nbsp;,</P><P>&nbsp;</P><P>I am still looking for trend chart to show, thats why I am using timechart with the count of host per site per week</P> Fri, 12 Aug 2022 09:56:35 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609300#M211874 vgiri8 2022-08-12T09:56:35Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609302#M211875 <P>I am still not sure what it is you are trying to show, but here is my next guess</P><LI-CODE lang="markup">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</LI-CODE> Fri, 12 Aug 2022 10:04:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609302#M211875 ITWhisperer 2022-08-12T10:04:07Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609303#M211876 <P>index=my_index encrypted=false | timechart span=1w estdc(host) by site</P><P>How do I get the latest encrypted data which is false , removing encrypted=false from the main search.&nbsp;</P><P>&nbsp;</P> Fri, 12 Aug 2022 10:04:39 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609303#M211876 vgiri8 2022-08-12T10:04:39Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609305#M211877 <P>I am trying to get a trend chart per site per week, for all host which had encryption=false within a given week.&nbsp;<BR /><BR />Basically trying to see the trend and check if we are improving week by week in encryption=false, basically needs to reduce.<BR /><BR />I am sorry If I was not clear before.&nbsp;</P> Fri, 12 Aug 2022 10:06:56 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609305#M211877 vgiri8 2022-08-12T10:06:56Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609306#M211878 <P>So my guess was right - this is what the search is basically doing</P><P>For each week, it gets the latest encryption state for each host on each site</P><P>Then keeps only those statistics where the state is false</P><P>Then counts to events (one for each host with encryption false for that week) by week and site</P><P>Finally, it reorganises the data into chart format.</P> Fri, 12 Aug 2022 10:16:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609306#M211878 ITWhisperer 2022-08-12T10:16:28Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609307#M211879 <P>Yes you are correct.<BR /><BR />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?</P> Fri, 12 Aug 2022 10:22:57 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609307#M211879 vgiri8 2022-08-12T10:22:57Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609309#M211880 <LI-CODE lang="markup">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</LI-CODE> Fri, 12 Aug 2022 10:34:14 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609309#M211880 ITWhisperer 2022-08-12T10:34:14Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609310#M211881 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/225168">@ITWhisperer</a>&nbsp;,<BR /><BR />Finally seeing data as required. I had few questions,<BR /><BR />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"</P><P>| stats count by _time site</P><P>replace it with&nbsp;</P><P>| stats dc(host) by site<BR /><BR />your thoughts</P> Fri, 12 Aug 2022 10:46:15 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609310#M211881 vgiri8 2022-08-12T10:46:15Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609321#M211885 <P>stats dc(host) by site gives you the count of distinct hosts per site</P><P>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).</P> Fri, 12 Aug 2022 12:29:29 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609321#M211885 ITWhisperer 2022-08-12T12:29:29Z https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609332#M211890 <P>Thank you so much&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/225168">@ITWhisperer</a>&nbsp;</P> Fri, 12 Aug 2022 14:35:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-create-a-query-as-below-but-I-would-like-to-get-the/m-p/609332#M211890 vgiri8 2022-08-12T14:35:36Z