Splunk Search

Can you help me with my search query?

Builder

alt text
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

0 Karma

Esteemed Legend

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
0 Karma

Influencer

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 )
0 Karma

Path Finder

Try this:

index=main sourcetype="aws:description" state= image.attributes.name!=emr id=i-069ffd47ebb88ee85
| table privateipaddress id state
| stats earliest(privateipaddress), latest(id), latest(state) by id

0 Karma

Builder

No luck everything shows blank expect id

0 Karma

Path Finder

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.

0 Karma

Path Finder

Did you get a chance to test it?

0 Karma

Builder

Yep no luck

0 Karma

Path Finder

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

0 Karma

Esteemed Legend

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
0 Karma

Builder

the privateipaddress field has both values the ip address and null

0 Karma

Esteemed Legend

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.

0 Karma

Builder

index=main AND sourcetype="aws:description" AND id=* AND ((state="terminated" AND image.attributes.name!="emr*") OR privateipaddress!=null)
| eval state = if(state=="terminated", state, null())
| eval node=awsaccountid
| eval resource="Instance Termination"
| eval type="Instance Terminated"
| eval severity=1
| stats values(privateipaddress) AS privateipaddress values(state) AS state BY id image.attributes.name |mvexpand privateipaddress |search state=terminated |search privateipaddress!=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

0 Karma

Esteemed Legend

Try adding this:

| selfjoin id
0 Karma

Builder

Tried using selfjoin but the state is showing running not terminated

0 Karma

Esteemed Legend

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
0 Karma

Builder

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

0 Karma

Esteemed Legend

So do you have something that works?

0 Karma

Builder

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=* privateipaddress!=null] |table privateipaddress id abc |dedup privateipaddress

0 Karma