I have the actual list of indexes in a lookup file. I ran below query to find the list of indexes with the latest ingestion time. how to find is there any index which is listed in the lookup, but not listed from the below query.
index=index*
| stats latest(_time) as latestTime by index
| eval latestTime=strftime(latestTime,"%x %X")
Can you please help
the join is not working
What are the field names in your lookup. I assumed that your list of indexes was in a field called index.
yes..the lookup column names are index and count
My mistake, it should be max(_time). I've fixed it in the other reply.
Start with your lookup as the base, then join on the the search data. Also, use tstats for something like this.
| inputlookup index_list
| join type=left index
[|tstats max(_time) as latestTime where index=* by index
| eval latestTime=strftime(latestTime,"%x %X")]
| where isnull(latestTime)
|tstats latest(_time) as latestTime where index=* by index
| eval latestTime=strftime(latestTime,"%Y/%m/%d %H:%M:%S")
| eval status=if(strftime(latestTime, "%Y/%m/%d") != strftime(now(), "%Y/%m/%d"), "not updated", "updated")
i'm using this query to find the index which is not indexed till today..For all the indexes i'm getting updated, eventhough there are indexes which are not updated a week.
IF you don't need to compare to a lookup with specific indexes, and all you want to return is a list of indexes with no logs in the last 7 days, try run this search over a 30 day window:
| tstats latest(_time) as latestTime where index=* by index
| where latestTime < relative_time(now(), "-7d@d")
| convert timeformat="%Y/%m/%d" ctime(latestTime)
It will only show indexes that have logged in the past 30 days but have stopped more than 7 days ago.
without using lookup..atleast i need in updated or notupdated fields