Finding forwarders that have not sent data

[sverdhan]

Hello , 

Can anyone please provide me a query which lists out  all forwarders that have not send data over the last 30 days?

Thank you

[livehybrid]

Hi @sverdhan 

I would avoid looking at things like index=* because this is very resource intensive and also may include hosts which are not forwarders!

Instead you can utilise the _metrics index which is very fast and efficient, you could try something like this:

|mstats latest_time(_value) as latest_time WHERE earliest=-31d latest=now index=_metrics metric_name="spl.mlog.tcpin_connections._tcp_eps" source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections by hostname 
| eval notSeenFor30Days=IF(latest_time<now()-(60*60*24*30),"NotSeen","Seen")
| eval lastSeen=tostring(now()-latest_time,"duration")

I would usually recommend having a lookup of "known forwarders" for this task and then update it with when it was last seen, that way you wouldnt need to look back 30 days each time.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[LAME-Creations]

@livehybrid recommended using a lookup to track your Forwarders.  I have to say that this is a really valuable tool, because if you keep track of your forwarders using a lookup, you can see what systems have not reported easily but you can also see any new forwarders that are sending logs to your system that you didn't know about.  

Below is a youtube video tutorial on using the lookup to track systems no longer sending logs.  
https://youtu.be/lo4_MIfTJzI?si=WfHxtBzTHLxmhQpe

All of the posts are good ideas.  The lookup is just one way to do it that is quick and easy, but there are many ways to do things in splunk, this is just my favorite way.  

[gcusello]

Hi @sverdhan ,

see in the Monitoring Console the "DMC Alert - Missing forwarders" alert

| inputlookup dmc_forwarder_assets
| search status="missing" 
| rename hostname as Instance

otherwise, if you want to know the clients that were connected in the last 30 days but not in the last hour, you could run something like this:

| tstats latest(_time) AS _time count where index=_internal BY host
| eval period=if(_time>now()-3600,"Last hour","Previous")
| stats 
     dc(period) AS period_count 
     values(period) AS period 
     latest(_time) AS _time
     BY host
| where period_count=1 AND period="Last hour"
| table host _time

Ciao.

Giuseppe

[kiran_panchavat]

@sverdhan 

| metadata type=hosts index=* earliest=-30d@d
| eval age = now() - lastTime
| eval last_seen = strftime(lastTime, "%Y-%m-%d %H:%M:%S")
| where age > 30*24*60*60
| eval age_days = round(age/(24*60*60), 2)
| table host, last_seen, age_days
| rename host as "Forwarder", last_seen as "Last Data Received", age_days as "Days Since Last Data"

[kiran_panchavat]

@sverdhan 

| tstats latest(_time) as lastTime where index=* by host 
| eval age=now()-lastTime 
| where age > 2592000 
| convert ctime(lastTime)
| rename host as "Forwarder Host", lastTime as "Last Data Received Time", age as "Age (in seconds)"
| sort - "Age (in seconds)"