How to query for a Week over Week count of hosts reporting to Splunk

[mohankesireddy]

Is there a better way to report the count of hosts reporting to Splunk week over week other than running the query using index=*

I am not looking for the no of forwarders, I am looking distinct count of host value in all the indexes, |metadata type=hosts do not help as it cannot be used for week over week calculation

index=* earliest=-2w@w latest=@w 
| bucket span=1d _time
| stats count by _time host 
| eval marker=if (_time<relative_time(now(),"-w@w"), "last week","this week")
| eval _time=if(marker=="last week", _time + 7*24*60*60, _time)
| timechart count by marker

[sushe10]

I have my query like below, but the problem is it shows the same date on both last weeks and this weeks chart when i mouse on the bar chart.

index = isell sourcetype=iSell_sessionslog Region = EU BusinessServiceName= BsSaveSalesOrderList Success = False earliest=-2w@w latest=@w |bucket span=1d _time | eval marker=if (_time

[woodcock]

Also, for your presentation once you pull the data out, tihink about using timewrap app:
https://splunkbase.splunk.com/app/1645/

[somesoni2 · Accepted solution]

A faster option is to use the tstats which works on tsidx files, metadata fields including _time. Try something like this

| tstats count WHERE index=* AND (host=slc* OR host=phx* OR host=dcg* OR host=ccg*)  earliest=-2w@w latest=@w  by _time host  | eval marker=if (_time<relative_time(now(),"-w@w"), "last week","this week")
 | eval _time=if(marker=="last week", _time + 7*24*60*60, _time)
 | timechart count by marker

[mohankesireddy]

Thanks Somesh