topic Re: How to join two queries and show not contain value in Splunk Search

How to join two queries and show not contain value

JandrevdM — Fri, 02 Aug 2024 13:53:11 GMT

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?

Re: How to join two queries and show not contain value

JandrevdM — Fri, 02 Aug 2024 09:45:18 GMT

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

Re: How to join two queries and show not contain value

ITWhisperer — Fri, 02 Aug 2024 11:10:26 GMT

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

Re: How to join two queries and show not contain value

JandrevdM — Fri, 02 Aug 2024 13:52:38 GMT

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 😞

Re: How to join two queries and show not contain value

ITWhisperer — Fri, 02 Aug 2024 14:07:01 GMT

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