topic Re: How to compare two search results to produce unique results within a table in Splunk Search

How to compare two search results to produce unique results within a table

TDR57 — Fri, 02 Feb 2018 05:25:16 GMT

I run a search which produces a list of users that have logged an incident ticket within our fault system. I then run another search and append this to the first search which produces users that incident tickets have been closed as a user error. I then generate a table that has both results in it..

Example

Username good tickets Bad tickets
Joe 5

Harry 6 2
John 4 1
Mary 6

Expected Output would only have both Joe and Mary in final table

Example
Username good tickets Bad tickets
Joe 5

Mary 6

can someone please help

thank you

Re: How to compare two search results to produce unique results within a table

493669 — Fri, 02 Feb 2018 05:32:38 GMT

try like this:

<last table containing Username, good tickets Bad tickets>|fillnull|where 'Bad tickets' = 0

Re: How to compare two search results to produce unique results within a table

TDR57 — Fri, 02 Feb 2018 05:37:26 GMT

Sorry I should have mentioned that they are on two different lines

Username good tickets Bad tickets
Joe 5
Harry 6
Harry 2

John 4
John 1

Mary 6

Re: How to compare two search results to produce unique results within a table

493669 — Fri, 02 Feb 2018 05:43:03 GMT

not sure why you have used append instead you can use join to get on same line..
if you could share your query with output (mask any important data) will help to understand better...

Re: How to compare two search results to produce unique results within a table

TDR57 — Tue, 29 Sep 2020 17:56:47 GMT

index=itam sourcetype=itam_inc_xml
| search (product_name="prod name" OR product_name="prod name2") AND service_type="User*" AND status="Closed" OR status="Resolved"
| stats latest(product_name) as product_name, latest(login_id) as login_id, latest(resolution_category_tier_2) as All_res_cat_2 by incident_number
| lookup userid_lookup.csv impacted_Userid as login_id OUTPUTNEW impacted_Name impacted_Company impacted_street_address
| stats count(incident_number) as ALL_count by impacted_Name, impacted_Company, impacted_street_address

| join impacted_Name type=outer [search (product_name="prod name" OR product_name="prod name2") AND service_type="User*" AND status="Closed" OR status="Resolved" AND resolution_category_tier_2="Process" OR resolution_category_tier_2="Training"

| stats latest(product_name) as product_name, latest(login_id) as login_id, latest(resolution_category_tier_2) as PT_res_cat_2 by incident_number
| lookup userid_lookup.csv impacted_Userid as login_id OUTPUTNEW impacted_Name impacted_Company impacted_street_address

| stats count(incident_number) as PT_count by impacted_Name, impacted_Company, impacted_street_address ]

| table impacted_Name ALL_count PT_count

Re: How to compare two search results to produce unique results within a table

niketn — Fri, 02 Feb 2018 05:54:19 GMT

@TDR57, can you give your current query and also sample of some of the raw events?

Re: How to compare two search results to produce unique results within a table

493669 — Tue, 29 Sep 2020 17:54:02 GMT

if I understood it correctly i.e. you have three columns namely impacted_Name, ALL_count, PT_count
and you want impacted _name who have null PT_count...if so then try like this

...| table impacted_Name ALL_count PT_count|stats sum(ALL_count) as ALL_count, sum(PT_count) as PT_count by impacted_Name |fillnull|where PT_count  = 0

Re: How to compare two search results to produce unique results within a table

mayurr98 — Fri, 02 Feb 2018 07:22:22 GMT

can you try this

index=itam sourcetype=itam_inc_xml (product_name="prod name" OR product_name="prod name2") AND service_type="User*" AND status="Closed" OR status="Resolved" 
| stats latest(product_name) as product_name, latest(login_id) as login_id, latest(resolution_category_tier_2) as All_res_cat_2 by incident_number 
| lookup userid_lookup.csv impacted_Userid as login_id OUTPUTNEW impacted_Name impacted_Company impacted_street_address 
| stats count(incident_number) as ALL_count by impacted_Name, impacted_Company, impacted_street_address 
| join impacted_Name type=outer 
    [ search index=itam sourcetype=itam_inc_xml (product_name="prod name" OR product_name="prod name2") AND service_type="User*" AND status="Closed" OR status="Resolved" AND resolution_category_tier_2="Process" OR resolution_category_tier_2="Training" 
    | stats latest(product_name) as product_name, latest(login_id) as login_id, latest(resolution_category_tier_2) as PT_res_cat_2 by incident_number 
    | lookup userid_lookup.csv impacted_Userid as login_id OUTPUTNEW impacted_Name impacted_Company impacted_street_address 
    | stats count(incident_number) as PT_count by impacted_Name, impacted_Company, impacted_street_address ] 
| table impacted_Name ALL_count PT_count 
| where isnull(PT_count)

let me know if this helps!

Re: How to compare two search results to produce unique results within a table

TDR57 — Mon, 05 Feb 2018 07:43:11 GMT

Thank you so much that worked a treat

Re: How to compare two search results to produce unique results within a table

micahkemp — Mon, 05 Feb 2018 14:19:15 GMT

It sounds like this comment contained the solution. You might consider converting it to an answer (or editing your answer to include it), so @TDR57 can accept it.