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

[TDR57]

Hi

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

[mayurr98 · Accepted solution]

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!

[493669]

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

[micahkemp]

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.

[TDR57]

Thank you so much that worked a treat

[niketn]

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

[TDR57]

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

[TDR57]

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

[493669]

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...

[493669]

try like this:

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