Build query for Delayed Forwarder for Phone-home and Not Sending Data

[Nraj87]

Hi All,

I'm build below query for Delayed Forwarder for Phone home for 2 hour and Not Sending Data to indexes more than 15 min through append command as single correlation search.

However, query is not working with append command where calculating time duration of data sent and last phone connection.  Kindly suggest if any change in query can fix the calculation.

index=_internal host=index1 source=*metrics.log* component=Metrics group=tcpin_connections kb>1
| eval os=os+" "+arch
| eval ip=sourceIp
| eval type="Datasent"
| stats max(_time) as _time values(hostname) as hostname values(fwdType) as fwdType values(version) as version values(os) as os by sourceIp
| append [ search index=_internal source="/opt/splunk/var/log/splunk/splunkd_access.log" "/services/broker/phonehome/connection"
|rex field=uri "_(?<fwd_name>[^_]+)_(?<fwd_id>[-0-9A-Z]+)$"
| eval type="Deployment"
| dedup fwd_name
| stats max(_time) as lastPhoneHomeTime values(fwd_name) as hostname values(useragent) as fwdType values(version) as version values(type) as types by clientip
| convert ctime(lastPhoneHomeTime)
| table clientip lastPhoneHomeTime hostname fwdType version]
| stats dc(type) as num_types values(type) as types values(hostname) as hostname values(fwdType) as fwdType values(version) as version
values(os) as os max(_time) as most_recent_data values(lastPhoneHomeTime) as most_recent_settings by ip
| eval data_minutes_ago=round((now()-most_recent_data)/60, 1), settings_minutes_ago=round((now()-most_recent_settings)/60, 1)
| search settings_minutes_ago>120 OR data_minutes_ago>15
| convert ctime(most_recent_data) ctime(most_recent_settings)
| sort types data_minutes_ago settings_minutes_ago
| stats max(_time) as lastPhoneHomeTime values(fwd_name) as hostname values(useragent) as fwdType values(version) as version values(type) as types by clientip
| convert ctime(lastPhoneHomeTime)
| table clientip lastPhoneHomeTime hostname fwdType version]
| stats dc(type) as num_types values(type) as types values(hostname) as hostname values(fwdType) as fwdType values(version) as version
values(os) as os max(_time) as most_recent_data values(lastPhoneHomeTime) as most_recent_settings by ip
| eval data_minutes_ago=round((now()-most_recent_data)/60, 1), settings_minutes_ago=round((now()-most_recent_settings)/60, 1)
| search settings_minutes_ago>120 OR data_minutes_ago>15
| convert ctime(most_recent_data) ctime(most_recent_settings)
| sort types data_minutes_ago settings_minutes_ago

[gcusello]

Hi @Nraj87 ,

what do you want to discover: missing forwarders?

if this is your requirement, you could follow two ways:

use a lookup containing the perimeter of the hosts to monitor (called e.g. perimeter.csv and containing at least one field: host) and running a search like the following:

| tstats count WHERE index=* BY host
| append [ | inputlookup perimeter.csv | eval count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

or, if you don't want to manually manage the perimeter.csv lookup, you could search e.g. for the hosts that sent logs in the last 30 days and didn't sent logs in the last hour:

| tstats latest(_time) AS _time count WHERE index=* earliest=-30d latest=now BY host
| eval period=if(_time<now()-3600,"Previous","Latest")
| stats dc(period) AS period_count values(period) AS period BY host
| where period_count=1 AND period="Previous"

I prefer the first solution because gives you more control, but it requires more job to manage.

Ciao.

Giuseppe