Get list of servers not sending logs

[walia_sapient]

i want to get list of servers from the csv which are not sending any logs to splunk like for past 48 hours with time when it stopped ingesting. i am trying below query but no success.

| metadata type=hosts index=*
| where lastTime < relative_time(now(),"-24h") AND totalCount > 0
| convert ctime(lastTime) as "Time when stopped" ctime(firstTime) as "Time when Started"
| table host "Time when stopped"
| search
[| inputlookup xyz.csv |fields hostname] | table host "Time when stopped"

[Luthufudheen]

Please refer below to get list of host which are not sending logs without using a lookup.

https://www.splunk.com/en_us/blog/tips-and-tricks/how-to-determine-when-a-host-stops-sending-logs-to...

[sanjeev543]

Please try below query

| tstats latest(_time) as l earliest(_time) as e where index=*
[| inputlookup x.csv
| rename hostname as host
| table host] by host
| eval diff=now()-l
| eval diff_in_days = round(diff/86400,2)
| convert ctime(l) as lasttime ctime(e) as first
| where diff_in_days>1

[walia_sapient]

shows error in the query

[sanjeev543]

@walia_sapient Could you please give the error message ?

[walia_sapient]

okay there was a extra dot in the query mistakenly , errors removed but the query is not returning any results though there are few servers from csv which are not sending logs

[sanjeev543]

For me it did work, however there is otherway

| tstats latest(_time) as l earliest(_time) as e where index=* host IN (x,y,z,a,.......) by host | eval diff=now()-l | eval diff_in_days = round(diff/86400,2) | convert ctime(l) as lasttime ctime(e) as first | where diff_in_days>1

place the host names from your CSV the above query for host IN by separating them with ,

[somesoni2]

Try this

List the hosts who have not sent any data in last 48 hours.

| tstats count WHERE index=* earliest=-48h [| inputlookup xyz.csv |table hostname | rename hostname as host ] by host 
| append  [| inputlookup xyz.csv |table hostname | rename hostname as host | eval count=0 ] 
| stats max(count) as count by host | where count=0 

[walia_sapient]

It didn't work giving false results

[gaurav_maniar]

Hi,

If your are just looking for sever list, below query will do the work, (select time range for 48 hours)

| metadata type=hosts index=*
| lookup xyz.csv hostname as host OUTPUTNEW other_field as other_field
| where isnull(other_field)
| table host

other_field - select any field from the lookup

happy splunking .....!!!!