Why does removing a search term reduce the number of results returned?

[jfriedman_ofigl]

My data looks like this:

{   [-] 
     computer_dns_name:  computer.domain.com    
     computer_sid:   22264db9ce59cd8f10000000d  
     group_name:     New    
     id:     14711  
}
{   [-] 
     computer_dns_name:  computer.domain.com    
     computer_sid:   22264db9ce59cd8f10000000d
     group_name:     Old    
     id:     14711  
}

I am trying to find cases where the group_name has changed.

This query returns results:

computer_dns_name="*" id=14711
| transaction id
| eval group_count=mvcount(group_name)
| where group_count > 1
| table computer_dns_name, group_name

This one does not!

computer_dns_name="*"
| transaction id
| eval group_count=mvcount(group_name)
| where group_count > 1
| table computer_dns_name, group_name

I expected that removing the search term id=14711 would give me all of the changes. Coming from a SQL background I expect that removing restrictions will, if anything, give me more results.

[vupham · Accepted solution]

The transaction command joins events that fit a sequence. Event A happens -> Event B -> Event C, etc. e.g. User clicks on webpage -> user navigates to another page -> user submits form -> etc. Depending on the what the timestamps of your raw events look like, the transaction command might not recognize them as transactions. For example, the timestamps could be too similar, or there could be too many events in between each set of changes. You can see more examples of this in action in the command documentation. You can use the keeporphans option to verify that the events are not being picked up as transactions.

Using the stats command may be a better fit for your search rather than transaction, and typically will run faster. Your example rewritten with stats would look like

computer_dns_name="*"
| stats dc(group_name) as group_count values(group_name) as group_name by id computer_dns_name
| where group_count > 1

[jfriedman_ofigl]

My Splunk instance is currently down, thank you @vupham, I will be testing this solution when able.

[back2root]

You could use streamstats as an alternative.

computer_dns_name="*"
| streamstats current=f last(computer_dns_name) as last_computer_dns_name by id
| search computer_dns_name != last_computer_dns_name

See docs: http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Streamstats#8._Calculate_when_a_DH...
Or Blog:
https://www.splunk.com/blog/2013/10/31/streamstats-example.html

[jfriedman_ofigl]

@back2root that was very helpful, I ended up using that suggestion with this full query:
computer_dns_name="*"
| streamstats current=false last(group_name) as new_group_name last(_time) as time_of_change by computer_dns_name
| where group_name!=new_group_name
| convert ctime(time_of_change) as time_of_change
| rename group_name as old_group_name
| table time_of_change, computer_dns_name, old_group_name, new_group_name