Can you help me with my search query?

[vrmandadi]

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

[woodcock]

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

[Vijeta]

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 )

[cvssravan]

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

[vrmandadi]

No luck everything shows blank expect id

[cvssravan]

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.

[cvssravan]

Did you get a chance to test it?

[vrmandadi]

Yep no luck

[cvssravan]

Can you provide all the rows returned rows for the search for atleast one id

[woodcock]

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

[vrmandadi]

the private_ip_address field has both values the ip address and null

[woodcock]

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.

[vrmandadi]

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

[woodcock]

Try adding this:

| selfjoin id

[vrmandadi]

Tried using selfjoin but the state is showing running not terminated

[woodcock]

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

[vrmandadi]

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

[woodcock]

So do you have something that works?

[vrmandadi]

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