topic Re: Search From Two Indexes. Applying A Name Referenced By Id On Both Searches. in Splunk Search

Search From Two Indexes. Applying A Name Referenced By Id On Both Searches.

tdotcspot — Mon, 17 Dec 2018 19:05:53 GMT

Hi there,

Hoping someone could help me out. I'm currently using the AWS Add-On For Splunk and I wanted to expand the EC2 CPU Usage template that was provided (my 2nd search example below) . I've adjusted it a bit to provide me a list of instance ids in a timechart format. This works great, however I wanted to try to cross-reference it with another search that could give me the "name" tag of the EC2 using another index rather than the id.

1st search - This search gives me a list of "instanceids" and "names"

i-123456789, server1
i-234567890, server 2

index="description" | spath output="dInstanceId" id | search id="i-*" | spath output="dInstanceName" "tags.Name" | search "tags.Name"="*"
|dedup dInstanceName 
|table dInstanceId, dInstanceName

2nd search - This search gives me the CPU Utilization metric.

i-123456789
2018-12-16 11:00:00 0.50

index="cloudwatch" metric_name=CPUUtilization
| rex field=metric_dimensions "^(?<cwInstanceId>(\bInstanceId=.*\b))"
| eval cwInstanceId = trim(replace(cwInstanceId, "InstanceId=\[", ""))
| timechart eval(round(avg(Average),2)) by cwInstanceId where max in top10

My end goal is to have it reference the 1st search and provide a:

server1
2018-12-16 11:00:00 0.50

I attempted trying to cross-compare with an appended search without any luck. Would a join be used in this scenario? Or should I attempt to send the first search to a lookup table then try to input that data into the 2nd search?

Any help would be appreciated!
Thanks,
T

Re: Search From Two Indexes. Applying A Name Referenced By Id On Both Searches.

tdotcspot — Mon, 17 Dec 2018 19:36:22 GMT

Just to add I did accomplish this using a "join", but I am unsure if this is the best way to go based on performance.

index="cloudwatch" metric_name=CPUUtilization
| rex field=metric_dimensions "^(?<instanceId>(\bInstanceId=.*\b))"
| eval instanceId = trim(replace(instanceId, "InstanceId=\[", ""))
| join instanceId
[ search index="description" | spath output="instanceId" id | search id="i-*" | spath output="instanceName" "tags.Name" | search "tags.Name"="*" | dedup instanceId]
| timechart eval(round(avg(Average),2)) by instanceName where max in top10

Re: Search From Two Indexes. Applying A Name Referenced By Id On Both Searches.

macadminrohit — Mon, 17 Dec 2018 22:09:00 GMT

Can you show some some sample data for both the searches, also show the append search. if possible, avoid join as best as you can 🙂

Re: Search From Two Indexes. Applying A Name Referenced By Id On Both Searches.

macadminrohit — Mon, 17 Dec 2018 22:21:35 GMT

I just produced a dummy search which could help you.

| makeresults 
| eval DATA="1100,AWS101" | eval _time=now()
| makemv DATA delim="," 
| eval InstanceID=mvindex(DATA,0) 
| eval InstanceName=mvindex(DATA,1) 
| table InstanceName InstanceID _time
| append 
    [| makeresults 
    | eval _time=now() 
    | eval DATA1="50,1100" 
    | makemv DATA1 delim="," 
    | eval cwInstanceId=mvindex(DATA1,1) 
    | eval CPU_Util=mvindex(DATA1,0) 
    | table cwInstanceId CPU_Util _time | rename cwInstanceId as InstanceID] 
| table InstanceName InstanceID CPU_Util _time | eventstats values(InstanceName) as InstanceName by InstanceID | where NOT isnull(CPU_Util)

Re: Search From Two Indexes. Applying A Name Referenced By Id On Both Searches.

valiquet — Fri, 21 Dec 2018 20:26:59 GMT

index="cloudwatch" metric_name=CPUUtilization index="description" | spath output="dInstanceId" id | search id="i-*" | spath output="dInstanceName" "tags.Name"

| rex field=metric_dimensions "^(?(\bInstanceId=.*\b))"
| eval cwInstanceId = trim(replace(cwInstanceId, "InstanceId=[", ""))

|dedup dInstanceName

| stats count by dInstanceId, dInstanceName, _time
|fields dInstanceName, _time