Splunk Search
Highlighted

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

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.

0 Karma
Highlighted

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

Explorer

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

View solution in original post

0 Karma
Highlighted

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

Path Finder

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.CalculatewhenaDHCPIPleaseaddresschangedforaspecificMAC_address
Or Blog:
https://www.splunk.com/blog/2013/10/31/streamstats-example.html

0 Karma
Highlighted

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

@back2root that was very helpful, I ended up using that suggestion with this full query:
computerdnsname="*"
| streamstats current=false last(groupname) as newgroupname last(time) as timeofchange by computerdnsname
| where groupname!=newgroupname
| convert ctime(time
ofchange) as timeofchange
| rename group
name as oldgroupname
| table timeofchange, computerdnsname, oldgroupname, newgroupname

0 Karma
Highlighted

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

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

0 Karma