How to create search for list of servers sending data in last 14 days from the lookup and not sending in last 7 days

[karishmajain]

Hi Team,

Could you help me with the complete splunk query for  list of servers which are sending data in last 14 days from the lookup and not sending in last 7 days

if we write.... 

| eval day=if(_time<relative_time(now(),"-7d@d"),"sentdatalastweek","didnotsenddatainthelast7days")

what does that mean?

Regards

[ITWhisperer]

It depends what is in the _time field for your events.

On the face of it, it looks like, if the value of _time is prior to the beginning of the day 7 days ago, you will get "sentdatalastweek", otherwise you will get "didnotsenddatainthelast7days" - this seems to be the wrong way round (assuming _time holds the time the data was sent)

[gcusello]

Hi @karishmajain,

what do you mean when you say: "from the lookup"? are your data in an index or in one lookup?

Anyway, if your data is in one index, you can use the timestamp (_time) and use a check like the one you used:

index=your_index earliest=-14d latest=now
| eval week=if(_time<604800,"previousweek","lastweek")
| stats values(week) AS week dc(week) AS dc_week earliest(_time) AS earliest latest(_time) AS latest BY host
| where dc_check=1 AND week="previousweek"
| eval earliest=strftime(earliest,"%Y-%m-%d %H:%M:%S"), latest=strftime(latest,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[karishmajain]

Got the required result by if (now()- _time<604800....

Thanks 

[karishmajain]

Thanks for your input. I mean I have a csv file which has the list of servers ....from that inputlookup table I need to get the list of servers sending data in last 14 days but not in 7 days..could you please help me in that..

Thanks

[gcusello]

Hi @karishmajain,

this means that you have to monitor not all the servers, but only the ones in the lookup.

If the lookup is called "perimeter.csv" and it contain only one column called "host", you could try something like this:

index=your_index earliest=-14d latest=now [ | inputlookup perimeter.csv | fields host ]
| eval week=if(_time<604800,"previousweek","lastweek")
| stats values(week) AS week dc(week) AS dc_week earliest(_time) AS earliest latest(_time) AS latest BY host
| where dc_check=1 AND week="previousweek"
| eval earliest=strftime(earliest,"%Y-%m-%d %H:%M:%S"), latest=strftime(latest,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[karishmajain]

Thanks so much for your help and input...not getting any result may be the case there is no such server(host)....thanks again for your help and time.

[gcusello]

Hi @karishmajain,

what's the field name of the host in the lookup?

if it's different than host, you have to rename it on the square parenthesis, e.g. if it's called hostname, you have to run:

index=your_index earliest=-14d latest=now [ | inputlookup perimeter.csv | rename hostname AS host | fields host ]
| eval week=if(_time<604800,"previousweek","lastweek")
| stats values(week) AS week dc(week) AS dc_week earliest(_time) AS earliest latest(_time) AS latest BY host
| where dc_check=1 AND week="previousweek"
| eval earliest=strftime(earliest,"%Y-%m-%d %H:%M:%S"), latest=strftime(latest,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[karishmajain]

Yes I renamed the field previously ..still no results ...

It's absolutely fine .

[karishmajain]

is above query will give the list of all transmitted in 2 weeks .....need only those which are not triggering in last 7 days but triggering in 15 days.... 

[gcusello]

Hi @karishmajain,

the condition 

| where dc_week=1 AND week="previousweek"

filters results to give you only the hosts that sent logs in the first week but not in the second.

ciao.

Giuseppe

[gcusello]

Hi @karishmajain,

sorry, my error: dc_week not dc_check in the where condition!

index=your_index earliest=-14d latest=now [ | inputlookup perimeter.csv | rename hostname AS host | fields host ]
| eval week=if(_time<604800,"previousweek","lastweek")
| stats values(week) AS week dc(week) AS dc_week earliest(_time) AS earliest latest(_time) AS latest BY host
| where dc_week=1 AND week="previousweek"
| eval earliest=strftime(earliest,"%Y-%m-%d %H:%M:%S"), latest=strftime(latest,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe