I am not getting full data in output when combining 2 queries using join. When I run first query individually, I get 143 results after dedup but upon joining, I am getting 71 results only. Whereas I...
See more...
I am not getting full data in output when combining 2 queries using join. When I run first query individually, I get 143 results after dedup but upon joining, I am getting 71 results only. Whereas I know that for remaining records, data is available when running 2nd query individually.
How can I fix this?
I am searching for records where pods got claimed and then searching for connected time using subsearch and need output of all columns in tabular format.
index=aws-cpe-scl source=*winserver* "methodPath=POST:/scl/v1/equipment/router/*/claim/pods" responseJson "techMobile=true"
| rex "responseJson=(?<json>.*)"
| eval routerMac = routerMac
| eval techMobile = techMobile
| eval status = status
| spath input=json path=claimed{}.boxSerialNumber output=podSerialNumber
| spath input=json path=claimed{}.locationId output=locationId
| eval node_id = substr(podSerialNumber, 0, 10)
| eval winClaimTime=strftime(_time,"%m/%d/%Y %H:%M:%S")
| table winClaimTime, accountNumber, routerMac, node_id, locationId, status, techMobile
| dedup routerMac, node_id sortby winClaimTime
| join type=inner node_id
[ search index=aws-cpe-osc ConnectionAgent "Node * connected:" model=PP203X
| rex field=_raw "Node\s(?<node_id>\w+)\sconnected"
| eval nodeFirstConnectedTime=strftime(_time,"%m/%d/%Y %H:%M:%S")
| table nodeFirstConnectedTime, node_id
| dedup node_id sortby nodeFirstConnectedTime]
| table winClaimTime, accountNumber, routerMac, node_id, locationId, status, techMobile, nodeFirstConnectedTime