How to only display rows in table when one field changes?

[bensec01]

Hey folks,

I have a hard time believing this hasn't come up before, but I didn't find the right kinds of questions/answers when I went a-searching.

I have an inventory of hosts (specifically, data from a VMware DCN) that gives me periodic information about my virtual hosts. Super awesome, two thumbs up. I'm just starting to really dig into it and get more useful bits out.

The bit I'm working on now is tracking a VMs movement through our clusters. Ie, if a VM is vmotioned to another host, I want to see that. So, I have a very very simple search that spits out a table:

tag=virtualmachine tag=inventory tag=virtualization vm_name="some-VM-name" changeSet.summary.runtime.host.name=*
| table _time, hypervisor_name

This spits out:

2017-08-31 12:00:00 dc1-host-63
2017-08-31 11:00:00 dc1-host-63
2017-08-31 10:00:00 dc1-host-63
2017-08-31 09:00:00 dc1-host-41
2017-08-31 08:00:00 dc1-host-09
2017-08-31 07:00:00 dc1-host-09
2017-08-31 06:00:00 dc1-host-63
2017-08-31 05:00:00 dc1-host-63

etc, etc.

What I'd really like to see, is just when the second column changes - I don't need to know that the VM was still on dc1-host-63 at 11:00 today. Instead, I want to see the first event when that second column has changed from the previous value (in this example, when the inventory run first sees the VM on a new host):

2017-08-31 10:00:00 dc1-host-63
2017-08-31 09:00:00 dc1-host-41
2017-08-31 08:00:00 dc1-host-09
2017-08-31 05:00:00 dc1-host-63

I know dedup, but I can't dedup the time because I want to keep that timestamp intact where the second column changes. And I can't just dedup the second column, because then when a VM moved back to a previous host, I'd lose a row. I have the feeling dedup isn't going to be the right sauce for this one, but I haven't found the magic yet.

Any help would be very much appreciated!

Chris

[DalJeanis · Accepted solution]

try this

tag=virtualmachine tag=inventory tag=virtualization vm_name="some-VM-name" changeSet.summary.runtime.host.name=*
| table _time, hypervisor_name
| sort 0 _time
| streamstats current=f window=2 last(hypervisor_name) as prior_name
| where hypervisor_name!=prior_name OR isnull(prior_name)
| fields - prior_name

[bensec01]

Awesome. I had to add a space after the hyphen in fields, but it works perfectly. I thought streamstats might be a possibility, but I'm pretty green with that one...

Thank you so much!