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!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...