How to join two queries and show not contain value

[JandrevdM]

Good day, I am pretty new to Splunk and want a way to join two queries together.

Query 1 - Gives me all of my assets

| tstats count where index=_internal OR index=* BY host

Query 2 - Give me all of my devices that ingest into the forwarder

index="_internal" source="*metrics.log*" group=tcpin_connections
| dedup hostname
| table date_hour, date_minute, date_mday, date_month, date_year, hostname, sourceIp, fwdType ,guid ,version ,build ,os ,arch
| stats count

How can I join this to create a query that will find all my devices (query1) and check if they have the forwarder installed(query2) and show me the results of devices that are not in query 2?

[ITWhisperer]

You could try something like this

| tstats count where index=_internal OR index=* NOT [search index="_internal" source="*metrics.log*" group=tcpin_connections
| stats count by hostname | rename hostname as host | table host] BY host

[JandrevdM]

Thanks!

I tried different ways but am unable to get this, if I want to add a line to check if the device is an azure VM how would I do this?

| tstats count where index=_internal OR index=* NOT [search index="_internal" source="*metrics.log*" group=tcpin_connections
| stats count by hostname | rename hostname as host | table host] BY host

AND

[ search index=db_cloud sourcetype="azure:compute:vm:instanceView"
| rename host as host_changed
| table host_changed] BY host

I tried this but it does not work 😞

[ITWhisperer · Accepted solution]

Have you tried it this way:

| tstats count where index=_internal OR index=* [ search index=db_cloud sourcetype="azure:compute:vm:instanceView"
| stats count by host
| table host ] NOT [search index="_internal" source="*metrics.log*" group=tcpin_connections
| stats count by hostname | rename hostname as host | table host] BY host

[JandrevdM]

The count is just used in my dashboard view and will be removed in initial query.