How to remove results of one query from a second query

[pgawron2]

I'm currently trying to find workstations that haven't been logged into by a human over a period of time.

My first query is 

index=windows host=ch0cswop002
| stats values(ComputerName) as computers

That correctly gets me a list of all the windows workstations that are sending us logs.

My second query

search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-*
| stats count by ComputerName

correctly gives me the number of times any human logged into a specific ComputerName

When I combine the two

index=windows host=ch0cswop002
| stats values(ComputerName) as ComputerName
| eval count = 0
| join type=outer ComputerName
[search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-*
| stats count by ComputerName]
| where count == 0
| table ComputerName

I get a list of all the computers.  If I table ComputerName,count I see only a single count value of zero for the first result.

Does my error jump out for anyone or know a better way of doing this?

[scelikok]

Hi @pgawron2,

Please try below;

index=windows host=ch0cswop002 NOT 
    [ search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-* 
    | stats count by ComputerName 
    | fields ComputerName] 
| stats count by ComputerName
| fields - count

[pgawron2]

I'm still getting a large list of ComputerNames that include machines that have been used by humans.  Results also are displayed before the query finishes.  The logic I'm looking to implement wouldn't know of any results until after it has finished looking at all the logs.

[ITWhisperer]

Your first stats with values is generating a multivalue field with all the computer names; what you actually want is separate events for each computer name so you can join with the second search.

index=windows host=ch0cswop002
| stats count by ComputerName
| eval count = 0
| join type=outer ComputerName
[search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-*
| stats count by ComputerName]
| where count == 0
| table ComputerName

[pgawron2]

I'm still getting all of the workstations returning with a count of zero.  If I table ComputerName,count  I now get zeros for each computer.  So you were correct about changing it to a count instead of a value.

Unfortunately,  there is still something wrong with my logic.  I've run the subsearch against one of the machines that showed as zero  again by itself and it showed a valid user logging in 6 times.

[ITWhisperer]

index=windows host=ch0cswop002
| stats count by ComputerName
| fields - count
| join type=outer ComputerName
[search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-*
| stats count by ComputerName]
| where isnull(count)
| table ComputerName

[pgawron2]

Sorry, same results.  This time if I table ComputerName,count  all the count entries are null.

I'll keep trying and post here if I figure it out.

Thanks

[pgawron2]

index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-* | stats count by ComputerName

Returns ComputerName with number of times a human logged in.

Computer1             2

Computer3             4

index=windows host=ch0cswop002 | stats count by ComputerName | fields - count

Returns a list of all ComputerName

Computer1

Computer2

Computer3

index=windows host=ch0cswop002 | stats count by ComputerName | fields - count | join type=outer ComputerName [search index=windows host=ch0cswop002 EventCode=4624 Logon_Type=10 OR Logon_Type=2 user!=DWM-* user!=UMFD-* | stats count by ComputerName] | where isnull(count) | table ComputerName

Returns all Computers

Computer1

Computer2

Computer3

Desired results is just Computer2.  

[ITWhisperer]

Isn't that what you want? It represents Computers which don't appear in the second search.

[pgawron2]

It is giving me the same result as just the first query where we gather the list of computers.

It is not giving me the subset.

The null count isn't accurately representing no logins