How to combine my two searches to alert on duplicate GUIDs for universal forwarder installations?

[quihong]

Hello,

We recently deployed Splunk in our environment and recently discovered that our engineering teams are cloning systems without clearing out the universal forwarder GUID and related logs prior to cloning the machine.

I'm trying to set up a search and email alert to identify these problematic systems.

I have the following search that I can run on my Deployment Server which will give me back duplicate UF GUIDs and count.

| rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname| stats count by name | where count > 1

I also have this search that returns all my UF installations from my deployment server.

| rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname| rename name as clientName

I need help tying these two searches together.

...search... | rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname| stats count by name | where count > 1) WHERE GUID IN (| rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname| stats count by name | where count > 1)

I'm familiar with SQL, but still learning SPL so I'm not sure how to link the two separate searches together with a equivalent SQL IN clause.

Lastly, I want to schedule this search and email me a report of machines with duplicate GUIDs (but not email me an empty report).

Any help is appreciated. Thank you.

[quihong · Accepted solution]

Thanks for the reply. I figured it out using a join and alert.

Here it is. I joined by "name" which is the "Client Name" (aka GUID).

| rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname
| join name [| rest /services/deployment/server/clients count=0 splunk_server=local| fields hostname name ip dns utsname| stats count by name | where count > 1 |fields  - count] |sort name | rename name as clientGUID

[snoobzilla]

Not clear what you are trying to join on.

If you are trying to do an SPL join where the subsearch is part of search restrictions...

index=w x y [ search find_z's | stats count by z | table z ]  | massage data

Above primary search would be restricted to values of z found by search in brackets.

I question whether above is possible with search with leading |

Alternatively...

index=w x y z
| stats count by x y z
| join type=left z [ another search returning z and a b c | stats count AS count2 by a b c z ]

Would yield x y z count a b c count2