Thank you for the details on the expected output of your data with examples. Without seeing what the output that the query I proposed is doing it's difficult to know what is going on but I am going to assume that this data changes over time due to you using dedup and using values on the stats was causing multiple events to show up on each deviceId row. This can be solved by simply using latest instead of values on those fields: index=168347-np
([| `last_np_sourcetype("index=168347-np", "hw_eox")`] currentEoxMilestone=EoSCR OR currentEoxMilestone=LDoS (physicalType=*)) OR
([| `last_np_sourcetype( "index=168347-np", "group_members")` ] groupId=290681)
| fields deviceId deviceName physicalElementId hwEoxId sourcetype
| stats latest(deviceName) as deviceName latest(physicalElementId) as physicalElementId values(sourcetype) as sourcetype latest(hwEoxId) as hwEoxId latest(groupName) as groupName latest(groupId) as groupId by deviceId
| search sourcetype=hw_eox sourcetype=group_members
| stats count as Devices If you ever need more fields you just add them to the stats line using latest so you always get the most current results for the deviceId. Here's a run anywhere example demonstrating this using fake data: | makeresults count=1
| eval data="cpyKey: 168347, currentEoxMilestone: LDoS, currentEoxMilestoneDate: 2018-01-31T00:00:00, deviceId: 18468220, deviceName: 10.24.13.240, hwEoxId: 286609, nextEoxMilestone: null, nextEoxMilestoneDate: null, physicalElementId: 362026445, physicalType: Power Supply, productId: C3K-PWR-265WAC, timeStamp: 2020-12-15T12:57:03
cpyKey: 168347, currentEoxMilestone: LDoS, currentEoxMilestoneDate: 2018-01-31T00:00:00, deviceId: 18468220, deviceName: 10.82.112.71, hwEoxId: 266079, nextEoxMilestone: null, nextEoxMilestoneDate: null, physicalElementId: 341757347, physicalType: Power Supply, productId: C3K-PWR-265WAC, timeStamp: 2020-12-15T13:57:03
deviceId:18468220, groupId:309425, groupName:Cisco 3900 ISR Routers, timeStamp:2020-12-15T12:56:41"
| makemv data tokenizer="(?<data>[^\n]+)"
| mvexpand data
| rex mode=sed field=data "s/, /\n/g"
| rex field=data "timeStamp: ?(?<timeStamp>[^\s]+)"
| eval _time=strptime(timeStamp, "%FT%T"), sourcetype=if(match(data, "cpyKey"), "hw_eox", "group_members")
| fields - timeStamp
| rex field=data "deviceId: ?(?<deviceId>\d+)"
| rex field=data "deviceName: (?<deviceName>[^\n]+)"
| rex field=data "physicalElementId: (?<physicalElementId>[^\n]+)"
| rex field=data "hwEoxId: (?<hwEoxId>[^\n]+)"
| rex field=data "groupId:(?<groupId>[^\n]+)"
| rex field=data "groupName:(?<groupName>[^\n]+)"
| rex field=data "cpyKey: (?<cpyKey>[^\n]+)"
| rex field=data "currentEoxMilestone: (?<currentEoxMilestone>[^\n]+)"
| rex field=data "currentEoxMilestoneDate: (?<currentEoxMilestoneDate>[^\n]+)"
| rex field=data "nextEoxMilestone: (?<nextEoxMilestone>[^\n]+)"
| rex field=data "nextEoxMilestoneDate: (?<nextEoxMilestoneDate>[^\n]+)"
| rex field=data "physicalType: (?<physicalType>[^\n]+)"
| rex field=data "productId: (?<productId>[^\n]+)"
| stats latest(deviceName) as deviceName latest(physicalElementId) as physicalElementId values(sourcetype) as sourcetype latest(hwEoxId) as hwEoxId latest(groupName) as groupName latest(groupId) as groupId latest(cpyKey) as cpyKey latest(currentEoxMilestone) as currentEoxMilestone latest(currentEoxMilestoneDate) as currentEoxMilestoneDate latest(nextEoxMilestone) as nextEoxMilestone latest(nextEoxMilestoneDate) as nextEoxMilestoneDate latest(physicalType) as physicalType latest(productId) as productId by deviceId
| search sourcetype=hw_eox sourcetype=group_members
| fields - sourcetype deviceId You'll see in the above run anywhere example only the most current values are showing for each field except the sourcetypes because we want to ensure both sourcetypes are being represented in this data.
... View more