How do I write a search for devices not reporting to Splunk?

[nuro]

I need to get a report on devices that are not reporting to SPLUNK.
When I try with:

| metadata type=hosts | rename lastTime as "Last Event"| fieldformat "Last Event"=strftime('Last Event', "%c") 

I am getting only IP address. When I try with:

metatype = sourcetypes | rename lastTime as "Last Event"| fieldformat "Last Event"=strftime('Last Event', "%c") 

I am getting only source types.

What I need is sourcetype, IPs, and last event.

How can I create this search?

[steveyz]

metadata stores only information segregated by source, sourcetype, or host individually, so it will not have information necessary divided by both sourcetype and host. For that, you could use the metrics.log, or you can use tstats to query what is stored in the indexes.

| tstats max(_time) WHERE index=* GROUPBY sourcetype host

[somesoni2]

There is a very nice app called "Splunk Health Overview" available in app base which does provides lot of similar reports. Have a look at the same. https://splunkbase.splunk.com/app/1919/

[hagjos43]

This query might help. Assuming you are talking about devices that were once reporting into Splunk and have since stopped:

index="_internal" source="*metrics.lo*" group=tcpin_connections NOT eventType=* | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | eval connectionType=case(fwdType=="uf","universal forwarder", fwdType=="lwf", "lightweight forwarder",fwdType=="full", "heavy forwarder", connectionType=="cooked" or connectionType=="cookedSSL","Splunk forwarder", connectionType=="raw" or connectionType=="rawSSL","legacy forwarder")| eval build=if(isnull(build),"n/a",build) | eval version=if(isnull(version),"pre 4.2",version) | eval os=if(isnull(os),"n/a",os)| eval arch=if(isnull(arch),"n/a",arch) | eval lastReceived = case(kb>0, _time) | eval seconds=30 | eval event_count= tcp_eps * seconds | bin _time span=10m | stats first(connectionType) as connectionType first(version) as version max(_time) as lastConnected max(lastReceived) as lastReceived sum(kb) as kbTotal  sum(event_count) as events sum(seconds) as secs by sourceHost _time | stats first(connectionType) as connectionType first(version) as version max(lastConnected) as lastConnected max(lastReceived) as lastReceived by sourceHost | eval LastReceived=strftime(lastReceived, "%Y/%m/%d %H:%M:%S") | fields sourceHost, connectionType, LastReceived | sort + LastReceived

I got this from here gosplunk.com