Union and extract of 4 queries

[tahasefiani]

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

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

[damann · Accepted solution]

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]

it's work perfectly,thank you