topic How to merge two diff queries, but display only if the patching has happened? in Splunk Search

How to merge two diff queries, but display only if the patching has happened?

haripotu — Tue, 06 Jun 2023 21:11:21 GMT

Index = prod-x7 host IN ( 12345678) sourcetype=“Wineventlog” Eventcode=“19”

|eval patching = if(eventcode =“19”, “ok”, “not ok”)

If events are found then search server availability i.e.,

index= server_123 host in (12345678) uri_stem IN (http/hltchck)

| status count eval( status=100) as success, count as total by _time

|eval Percent = round((Success/total)*100,2)

| table Percent

how to merge this two diff querys. But display only if the patching has happened

Re: How to merge two diff queries, but display only if the patching has happened?

gcusello — Wed, 07 Jun 2023 06:28:35 GMT

Hi @haripotu,

at first, in the first search, if you put the condition EventCode=19, the second row is useless because you have only ok events.

Then, if you want to use _time in a stats command, you have to group timestamps using the bin, command, e.g. for one hour.

so you can merge the two searches in this way:

index= server_123 host IN (12345678) uri_stem IN (http/hltchck) [ search index=prod-x7 host IN (12345678) sourcetype=“Wineventlog” Eventcode=“19” | fields host ] | bin span=1h _time | stats count eval(status=100) AS success count AS total BY _time |eval Percent=round((Success/total)*100,2)

Ciao.

Giuseppe

Re: How to merge two diff queries, but display only if the patching has happened?

haripotu — Thu, 08 Jun 2023 03:40:53 GMT

But i need the second percent search (percent query) only if we find events for event code =19

Re: How to merge two diff queries, but display only if the patching has happened?

gcusello — Thu, 08 Jun 2023 06:22:01 GMT

Hi @haripotu,

if you want the percent of EventCode=19 over all the events, you could run something like this:

index= server_123 host IN (12345678) uri_stem IN (http/hltchck)fields host | bin span=1h _time | stats count(eval(status=100) AS success count AS total BY _time host | search [ search index=prod-x7 host IN (12345678) sourcetype=“Wineventlog” Eventcode=“19” | fields host ] | stats sum(count) AS Success values(total) AS total BY _time | eval Percent=round((Success/total)*100,2)

Ciao.

Giuseppe