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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...