Compare log received from this week with log received from last week across all index all hosts

[samlinsongguo]

I want to produce a table that show as below

hostname ThisWeek LastWeek Different
worksta1 223423 2434234 4323
worksta2 223423 2434234 4323
serve1 223423 2434234 4323

What is the best way to achieve that cross all indexs and all hosts?

In our environment we have about over 100 index and over 25000 workstation/servers so I cant ready do index=* to load everything.
I remember reading something about _internal index has all the information when message coming in is that the case?
What about if I do not have access to _internal index, is there any other way can do it?
I tried use |metadata type=hosts index=* but the problem is | metadate does not take earliest=-7d latest=now() so I cant put it into one search, I am doing something wrong?

Thanks in advance.

[damien_chillet · Accepted solution]

Could you try this:

| tstats count as LastWeek where index=<your_index> earliest=-2w@d latest=-1w@d by host
| join host [ | tstats count as ThisWeek where index=<your_index> earliest=-1w@d latest=-0d@d by host]

[samlinsongguo]

HI Damien
Thank you for your answer, but I think there may have a problem of the consume too much resource to run the query as I want to run across all my 100 indexes, which mean I have to do index=*. I understand tstats is faster then stats but would it still cost too long to finish up the query?
Thanks

[damien_chillet]

Well i think it's the best shot you have.
It depends how big your indexes are i guess?
You could try and just stop the job if it hangs for too long.

[samlinsongguo]

Actually I test your query in our environment it seems working OK with tstats
Thank you very much for your help