I am running the below search
index=main sourcetype="aws:description" state=* image.attributes.name!=emr* id=i-069ffd47ebb88ee85
| table private_ip_address id state
| dedup private_ip_address
OUTPUT
private_ip_address id state
10.xx.xx.xx i-069ffd47ebb88ee85 running
null i-069ffd47ebb88ee85 terminated
I want the output to be
10.xx.xx.xx i-069ffd47ebb88ee85 terminated
I have used join, but that is taking a lot of time. Is there a better optimization, like the coalesce command or something?
Thanks in Advance
Try this:
index=main AND sourcetype="aws:description" AND id="*" AND ((state="terminated" AND image.attributes.name!="emr*") OR private_ip_address!=null)
| eval state = if(state=="terminated", state, null())
| eval node=aws_account_id
| eval resource="Instance Termination"
| eval type="Instance Terminated"
| eval severity=1
| stats max(_time) AS _time values(image.attributes.name) AS instance values(id) AS id values(private_ip_address) AS private_ip_address values(state) AS state BY id image.attributes.name
|mvexpand private_ip_address
|search state=terminated AND private_ip_address!=null
| eval description = "The instance: " . instance . " with id:" . id . " has status " . state . " at " . _time
Try this
index=main sourcetype="aws:description" state=* image.attributes.name!=emr* id=i-069ffd47ebb88ee85
|stats latest(state) as state , values(private_ip_address) as private_ip_address by id | mvexpand| where ISNOTNULL( private_ip_address )
Try this:
index=main sourcetype="aws:description" state= image.attributes.name!=emr id=i-069ffd47ebb88ee85
| table private_ip_address id state
| stats earliest(private_ip_address), latest(id), latest(state) by id
No luck everything shows blank expect id
Ok, your data have more than 2 rows. So add these 3 fields to the search and make sure the output contains data in all 3 fields and use the stats command I posted.
Did you get a chance to test it?
Yep no luck
Can you provide all the rows returned rows for the search for atleast one id
Try this:
index=main AND sourcetype="aws:description" AND id=* AND ((state="terminated" AND image.attributes.name!="emr*") OR private_ip_address!=null)
| eval state = if(state=="terminated", state, null())
| stats values(private_ip_address) AS private_ip_address values(state) AS state BY id
the private_ip_address field has both values the ip address and null
OK, then add this to the end:
| eval private_ip_address = mvfilter(NOT match(private_ip_address, "^null$"))
You may need to adjust the "^null$"
a bit.
index=main AND sourcetype="aws:description" AND id=* AND ((state="terminated" AND image.attributes.name!="emr*") OR private_ip_address!=null)
| eval state = if(state=="terminated", state, null())
| eval node=aws_account_id
| eval resource="Instance Termination"
| eval type="Instance Terminated"
| eval severity=1
| stats values(private_ip_address) AS private_ip_address values(state) AS state BY id image.attributes.name |mvexpand private_ip_address |search state=terminated |search private_ip_address!=null
This is working but I am trying to add another field called description doing an eval command to get the description field like below
| eval description="The instance : ". image.attributes.name . " with id:" .id. " has status " .state ." at ". time
Is there way to get the into the search ?
Thanks
Try adding this:
| selfjoin id
Tried using selfjoin but the state is showing running not terminated
Add this to the bottom:
| stats values(private_ip_address) AS private_ip_address last(state) AS state BY id
OR:
| stats values(private_ip_address) AS private_ip_address values(state) AS state BY id
The values command combines everything but I want id to match output the id ,ip adress which is not null and the state should be teriminated like below
i-069ffd47ebb88ee85 10.xx.xxx.x terminated .I have attached the image of the output that I wanted to look like which I got it through join
So do you have something that works?
Yes join works but its taking a lot of time
index=main sourcetype="aws:description" state=terminated image.attributes.name!=emr* id=* |rename state as abc|join id [search index=main sourcetype="aws:description" id=* private_ip_address!=null] |table private_ip_address id abc |dedup private_ip_address