All Apps and Add-ons

Union and extract of 4 queries

tahasefiani
Explorer

Hello everybody,
Today, I have Four queries that each return a result that I use on excel;here is an example

    Query 1 : 

    | loadjob savedsearch="mysavedsearch"
    | where MESSAGE="sent"
    | fields client

    Query 2 : 

    | loadjob savedsearch="mysavedsearch"
    | where MESSAGE="opened"
    | fields client

    Query 3 : 

    | loadjob savedsearch="mysavedsearch"
    | where MESSAGE="answered"
    | fields client

    Query 4 : 

    | loadjob savedsearch="mysavedsearch"
    | where MESSAGE="deleted"
    | fields client

In excel I copy the results to extract the list of customers without actions

alt text

Can someone give me ideas? knowing that in my version I can’t use "IN"

0 Karma
1 Solution

damann
Communicator

You can chain your savedsearches with subsearches an exclude the results by using NOT

| makeresults 
    `comment("This is your basesearch with all your clients")` 
| eval clients="1,2,3,4,5,6,7,8,9" 
| eval clients=split(clients,",") 
| mvexpand clients 
| search NOT 
    `comment("Here you begin filtering. This can be your 'opened' savedsearch for example")` 
    [| makeresults 
    | eval clients="1,2,3" 
    | eval clients=split(clients,",") 
    | mvexpand clients 
    | fields clients] 
| search NOT 
    `comment("2nd filter. This can be your 'deleted' savedsearch for example")` 
    [| makeresults 
    | eval clients="5" 
    | eval clients=split(clients,",") 
    | mvexpand clients 
    | fields clients]
    `comment("At the end you will see your 'untouched' events")`

I didnt test it with a savedsearch but it should work in this way:

| loadjob savedsearch="mysavedsearch" 
| where MESSAGE="sent" 
| fields client 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="opened" 
    | fields client] 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="answered" 
    | fields client
        ] 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="deleted" 
    | fields client]

View solution in original post

damann
Communicator

You can chain your savedsearches with subsearches an exclude the results by using NOT

| makeresults 
    `comment("This is your basesearch with all your clients")` 
| eval clients="1,2,3,4,5,6,7,8,9" 
| eval clients=split(clients,",") 
| mvexpand clients 
| search NOT 
    `comment("Here you begin filtering. This can be your 'opened' savedsearch for example")` 
    [| makeresults 
    | eval clients="1,2,3" 
    | eval clients=split(clients,",") 
    | mvexpand clients 
    | fields clients] 
| search NOT 
    `comment("2nd filter. This can be your 'deleted' savedsearch for example")` 
    [| makeresults 
    | eval clients="5" 
    | eval clients=split(clients,",") 
    | mvexpand clients 
    | fields clients]
    `comment("At the end you will see your 'untouched' events")`

I didnt test it with a savedsearch but it should work in this way:

| loadjob savedsearch="mysavedsearch" 
| where MESSAGE="sent" 
| fields client 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="opened" 
    | fields client] 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="answered" 
    | fields client
        ] 
| search NOT 
    [| loadjob savedsearch="mysavedsearch" 
    | where MESSAGE="deleted" 
    | fields client]

tahasefiani
Explorer

it's work perfectly,thank you

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...