How to find max(_time) for sourcetypes & host by sourcetype host?

[sjringo]

I found the following search to identify Missing / New sourcetypes and made a few changes.
I am getting data and my next enhancement is to add the latest date/time a sourcetype was 'seen'.

Here is the search I am starting with:

index=anIndex earliest=-4d latest=now
| eval recent=if(_time>(now()-129600 ),1,0) ```<--- No Logs in 1.5 Days ```
| stats count(eval(recent=1)) AS CurrentCount count(eval(recent=0)) AS HistoricalCount BY sourcetype host
| where ( (CurrentCount < 1 AND HistoricalCount > 0) OR ( CurrentCount > 0 AND HistoricalCount < 1)) ```<--- Missing & New```
| eval status=case(CurrentCount > 0 AND HistoricalCount > 0, "OK",
CurrentCount < 1 AND HistoricalCount > 0, "MISSING",
CurrentCount > 0 AND HistoricalCount < 1, "NEW",
1=1,"Unknown" )
| sort sourcetype
| table status host sourcetype CurrentCount HistoricalCount

I think this is returning the last time a sourcetype was seen:

index=anIndex earliest=-2d latest=now
| stats max(_time) as last_searched by sourcetype host
| eval lastTime=strftime(last_searched, "%m/%d/%y %H:%M:%S")
| sort sourcetype host
| table host sourcetype lastTime

But, when I try to add these two lines into my original query I do not get any data ?

| stats max(_time) as last_searched by sourcetype host
| eval lastTime=strftime(last_searched, "%m/%d/%y %H:%M:%S")

I have tried placing it in several different places but always get 'No Results found...'
What am I missing ?

[yeahnah]

Hi @sjringo 

In the first query the _time field is not a result field, so you obviously cannot use it.  Here's a modified query that also returns the max time for each host sourcetype.  I've also demonstrated how to use the tstats command to make the query x100 times quicker and more efficient, but you can revert back to scanning _raw events if you prefer.

| tstats max(_time) AS _time WHERE index=anIndex earliest=-4d latest=now BY sourcetype host 
| eval recent=if(_time>(now()-129600 ),1,0) ```<--- No Logs in 1.5 Days ```
| stats max(_time) AS max_time count(eval(recent=1)) AS CurrentCount count(eval(recent=0)) AS HistoricalCount BY sourcetype host
| where ( (CurrentCount < 1 AND HistoricalCount > 0) OR ( CurrentCount > 0 AND HistoricalCount < 1)) ```<--- Missing & New```
| eval status=case(CurrentCount > 0 AND HistoricalCount > 0, "OK",
    CurrentCount < 1 AND HistoricalCount > 0, "MISSING",
    CurrentCount > 0 AND HistoricalCount < 1, "NEW",
    1=1,"Unknown" )
| sort sourcetype
| table status host sourcetype CurrentCount HistoricalCount max_time
| stats max(max_time) as last_searched by sourcetype host
| eval lastTime=strftime(last_searched, "%m/%d/%y %H:%M:%S")

  Hope that helps

[sjringo]

Ok, I had to massage your posting somewhat to work with what I was looking for.

I am aware of TSTATS but struggling to get it to work for the query that I am starting with ?

Here is what I have working:

index=anIndex earliest=-4d latest=now

| eval recent=if(_time>(now()-129600 ),1,0) ```<--- No Logs in 1.5 Days ```

| stats max(_time) AS max_time count(eval(recent=1)) AS CurrentCount count(eval(recent=0)) AS HistoricalCount BY sourcetype host

| where ( (CurrentCount < 1 AND HistoricalCount > 0) OR ( CurrentCount > 0 AND HistoricalCount < 1)) ```<--- Missing & New```

| eval status=case(CurrentCount > 0 AND HistoricalCount > 0, "OK", CurrentCount < 1 AND HistoricalCount > 0, "MISSING", CurrentCount > 0 AND HistoricalCount < 1, "NEW", 1=1,"Unknown" )

| eval lastTime=strftime(max_time, "%m/%d/%y %H:%M:%S")

| sort sourcetype

| rename status AS Status, host AS Host, sourcetype AS "Source Type", CurrentCount AS "Log Entries in 1.5 Days", HistoricalCount AS "Log Entries over 1.5 Days", lastTime AS "Last Log Entry"

| table Status Host "Source Type" "Log Entries in 1.5 Days" "Log Entries over 1.5 Days" "Last Log Entry"

Any help in getting it to work with TSTATS would be appreciated...

[yeahnah · Accepted solution]

Hi @sjringo 

Sorry, I had not accounted for you count logic.  You can use tstats like this and then apply some logic to the results.

| tstats
    max(_time) AS max_current_time
    count AS CurrentCount
  WHERE index=anIndex earliest=-129600 latest=now
  BY sourcetype host
| append [| tstats
    max(_time) AS max_historical_time
    count AS HistoricalCount
  WHERE index=anIndex earliest=-4d latest=-129600
  BY sourcetype host ]
| stats values(*) AS * BY sourcetype host
| foreach *_time [ eval <<FIELD>>_hm=strftime('<<FIELD>>', "%c") ]  ``` not really needed ```
| eval _time=if(max_current_time > max_historical_time OR isnull(max_historical_time), max_current_time, max_historical_time)
| eval status=case(CurrentCount > 0 AND HistoricalCount > 0, "OK", isnull(CurrentCount), "MISSING", isnull(HistoricalCount), "NEW", true(), "Unknown" )
| sort sourcetype
| rename status AS Status, host AS Host, sourcetype AS "Source Type", CurrentCount AS "Log Entries in 1.5 Days", HistoricalCount AS "Log Entries over 1.5 Days"
| table Status Host "Source Type" "Log Entries in 1.5 Days" "Log Entries over 1.5 Days" _time

 It might not be perfect but will hopefully get you going

[sjringo]

Getting results using your updated TSTATS query.  I am going to need a little time to compare data from both queries and mash up the results just to figure out if both results are returning the same results, etc...

But the speed of the query results is better.  a few seconds compared to 15 minutes !!!

TSTATS is still a little bit fuzzy but having something that works will help me going forward...

Thanks for your help ...