topic Re: How to exclude invoices with ID = 350 in Splunk Search

How to exclude invoices with ID = 350

vita86 — Mon, 20 Apr 2020 20:00:47 GMT

Hello,

I'm training on splunk, I need help.

I have an invoice list, extracted via this query :

sourcetype="*_invoice" 
| where in (id,350,128,307)
| table id invoice ProductType

Result :
350 261313851 phone
128 261313851 screen
307 538601320 aquarium
.....

But I have to exclude invoice number 261313851 because it contains id = 350.

How can I do please ? foreach and condition if ?

| Foreach invoice [eval status_invoice=if(id!=350, "ok", "ko")] 
| where status_invoice= "ok"?

Thank you in advance for your help.

Regards,
vita86

Re: How to exclude invoices with ID = 350

richgalloway — Mon, 20 Apr 2020 20:25:15 GMT

If you take 350 out of the where clause then those IDs will not be included.

The foreach command iterates over the fields in a single event. Otherwise, commands iterate over each event returned by the previous command.

Re: How to exclude invoices with ID = 350

vita86 — Tue, 21 Apr 2020 07:32:22 GMT

Hello richgalloway,

thanks for your answer.

if i remove 350 in the where clause, i will have this :
128 261313851 screen
307 538601320 aquarium

but this 261313851 invoice is not correct for me because it's contains too id = 350 so i want just :
307 538601320 aquarium
.......

How can i do please ?

Thank you very much for your help and your advice.

Re: How to exclude invoices with ID = 350

richgalloway — Tue, 21 Apr 2020 13:13:52 GMT

Thanks for clarifying the problem. See if this helps. It groups the events by invoice then filters out those invoices that have id=350. Then the group is broken up and the results displayed.

sourcetype="*_invoice" (id=350 OR id=128 OR id=307)
| stats values(*) as * by invoice
`comment("mvfind returns NULL if '350' is not found")`
| where isnull(mvfind(id, "350"))
| mvexpand id
| table id invoice ProductType

Re: How to exclude invoices with ID = 350

vita86 — Wed, 22 Apr 2020 14:12:33 GMT

Thank you very much for your help and your explanation.

Re: How to exclude invoices with ID = 350

richgalloway — Thu, 23 Apr 2020 12:05:54 GMT

If your problem is resolved then please accept the answer to help future readers.