Compare 1st search result with 2nd search result and display only 1st searchfield value which didn't match with the 2nd?

[Jagadeesh2022]

Hi Friends,

My requirement: I want to trigger SNOW ticket from Splunk alert. Before trigger I want to check any open ticket already available for that host. If already open ticket available alert shouldn't trigger. If there is no open ticket then we need to trigger alert and create SNOW ticket. 

My First query: 

index="pg_idx_whse_prod_events" sourcetype IN ("cpu_mpstat") host="adlg*"
| streamstats time_window=15m avg(cpu_idle) as Idle count by host
| eval Idle = if(count < 30,null,round(Idle, 2))
| WHERE(Idle >= 90)

| table host Idle

my 2nd query: 

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
| rex field=dv_short_description "^[^\-]+\:(?<extracted_field>[^\-]+)"
| rename Host as host
|table host incident_state_name | where incident_state_name!="Closed"

Now I want to validate 1st result with 2nd result and display only which host don't have open ticket.

Could you please help me how to achieve this?

Thanks in advance.

[PickleRick]

Firstly, your second search is highly suboptimal.

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
| rex field=dv_short_description "^[^\-]+\:(?<extracted_field>[^\-]+)"
| rename Host as host
|table host incident_state_name | where incident_state_name!="Closed"

1. As a general rule - don't use "table" except as the last command for data presentation.

2. Don't make Splunk extract useless fields (you're doing rex for "extracted_field" then discard it completely with "table".

3. Filter as early as possible - instead of your "where" at the end you should look only for non-closed incidents in the first place. As a side note - you are aware that A!=B is not the same as NOT A=B, aren't you?

Having said that - there are at least three different approaches that could be employed here.

1. Use the second search (possibly improved according to the remarks above) to return a list of hosts - put it in a subsearch and use as a filter to the first search. Not a very pretty solution, prone to typical problems with subsearches (time limit, number of results limit).

2. Append one search to another, do stats values() over hosts and filter by the incident_state_name (or by (not)existence of this field). Should be more efficient than solution 1, prone to subsearch problems with appended search.

3. Do a search over both indexes and sourcetypes and do stats values() over hosts. That one is tricky because you're doing some fancy streamstats in the first search so that could need some fancy sorting in order for the streamstats to work correctly. And maybe creating some fields dynamically based on index or sourcetype.

[Jagadeesh2022]

HI @PickleRick ,

Thank you so much for your prompt reply and valuable suggestion. 

I'm new to Splunk tool. I'm learning from my errors.  I have accepted your points and corrected my 2nd query. 

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/"
| where incident_state_name!="Closed"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
|table Host incident_state_name

Could you please help me how to implement in my query  your 2nd suggestion:

2. Append one search to another, do stats values() over hosts and filter by the incident_state_name (or by (not)existence of this field). Should be more efficient than solution 1, prone to subsearch problems with appended search.

Thanks in advance. 

[Jagadeesh2022]

Hi @PickleRick ,

I have applied your suggestion like below:

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/"
| where incident_state_name!="Closed"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
| rex field=dv_short_description "^[^\-]+\:(?<extracted_field>[^\-]+)"
| rename Host as host
|table host incident_state_name
|append
[|search index="pg_idx_whse_prod_events" sourcetype IN ("cpu_mpstat") host="adlg*" earliest=-15m
| streamstats time_window=15m avg(cpu_idle) as Idle count by host
| eval Idle = if(count < 30,null,round(Idle, 2))
| WHERE(Idle >= 90)
| table host ]
| stats values(host) by incident_state_name

But I got result like below:

ncident_state_name  host

New                                     host1

                                               host2

                                                host3

But this is not my expected result. 

My expect result is search1 have host1 host2 host3  & search 2 have host1 host2 then my result is only host3. 

Kindly assist on this.

@richgalloway  @gcusello 

[PickleRick · Accepted solution]

Ha! You're getting close.

One small remark - don't do search | where if you can avoid (and in this case you can). Splunk might be able to optimize it out but not necessarily so just add it as a condition to the initial search.

Your idea was pretty good.

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/"  incident_state_name!="Closed"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
| rename Host as host
|table host incident_state_name
|append
[|search index="pg_idx_whse_prod_events" sourcetype IN ("cpu_mpstat") host="adlg*" earliest=-15m
| streamstats time_window=15m avg(cpu_idle) as Idle count by host
| eval Idle = if(count < 30,null,round(Idle, 2))
| WHERE(Idle >= 90)
| table host ]

But you need something to distinguish the hosts from the first search from the hosts from the second search so you might - for example - add artificial static field but in your case you have the Idle field so we can use it. So your appended searches look like that:

index=pg_idx_whse_snow sourcetype="snow:incident" source="https://pgglobalenterpriseuat.service-now.com/" incident_state_name!="Closed"
| rex field=dv_short_description "^[^\-]+\-(?<Host>[^\-]+)"
| rename Host as host
|table host incident_state_name
|append
[|search index="pg_idx_whse_prod_events" sourcetype IN ("cpu_mpstat") host="adlg*" earliest=-15m
| streamstats time_window=15m avg(cpu_idle) as Idle count by host
| eval Idle = if(count < 30,null,round(Idle, 2))
| WHERE(Idle >= 90)
| table host Idle ]

Pretty close to your idea, huh? 🙂

Now for the final bit - you thought a bit another way around. We don't want the list of hosts per incident_state_name. We want to know for each host whether we have incident_state_name and Idle value. So we append

| stats values(Idle) as Idle values(incident_state_name) as incident_state_name by host

Now if you can only list some of them. For example, only those that do not have incident_state_name (weren't included in the first search)

| where isnull(incident_state_name)

or those, which do not have Idle value (weren't included in the second search)

| where isnull(Idle)

[Jagadeesh2022]

Hi @PickleRick 

Thank you so much for your detailed explanation. It works for me.