Can someone clarify how the map command is supposed to work or if I have made a mistake in my search?

[chrishartsock]

Hello,

I am currently trying to do a search across two different sourcetypes using the map command:

sourcetype=source1 "alert"
| rename blahblahblah AS Machine
| WHERE isnotnull(Machine)
| eval earliest=_time-86400
| eval latest=_time+86400
| map search="search sourcetype=source2 Computer=$Machine$ earliest=$earliest$ latest=$latest$" maxsearches=100
| table Computer status

The idea is that source1 contains certain events revolving around certain computers. I want to cross-reference this with source2 to find the status of each Computer that shows up in the results of the search from source1 around the given time the event occurs. However, I am only getting results concerning the first computer that appears.

e.g., instead of getting:
| Computer | status |

|--------------|---------|
| ComputA | 1.0 |
| ComputB | 3.0 |
| ComputC | 1.0 |

I am just getting:
| Computer | status |

|--------------|---------|
| ComputA | 1.0 |

Is this the way the map command is supposed to work and I just misunderstood, or have I made a mistake somewhere else?

Thanks ahead of time!

[landen99]

I'm not a fan of the map SPL command; its too problematic and slow for my tastes. The temporal subsearch filter will preform better:

index=idx2 sourcetype=source2 
  [ search index=idx1 sourcetype=source1 "alert"
  | stats count by field1 _time | rename field1 AS Machine
  | eval earliest=_time-86400
  | eval latest=_time+86400
  | return 999 Machine earliest latest]
| table _time Computer status

[somesoni2 · Accepted solution]

Try something like this (the table command in the end is to be applied to the result of map searches so should come inside. Keeping it outside map will just give you Computer and status from last row of the base search)

sourcetype=source1 "alert"
 | rename blahblahblah AS Machine
 | WHERE isnotnull(Machine)
 | eval earliest=_time-86400
 | eval latest=_time+86400
 | map search="search sourcetype=source2 Computer=$Machine$ earliest=$earliest$ latest=$latest$  | table Computer status " maxsearches=100
 | table Computer status

[chrishartsock]

Thanks for your help. This works exactly the way I wanted it to.

[javiergn]

You can use a subsearch I think:

sourcetype=source2 [
   search sourcetype=source1 "alert" earliest=ABC latest=DEF
   | rename blahblahblah AS Computer
   | WHERE isnotnull(Computer)
   | table Computer
] 
| table Computer status

More info about subsearches: http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchTutorial/Useasubsearch

[chrishartsock]

Well the issue is the status changes over time. So if I run the search over the last 7 days, that computer may have 7 different statuses, but, if it can be done, I would like to output just the status around the time the event occurred.

[javiergn]

Hi, I'm not quite sure what you mean. You can filter by time in your main search by using earliest and latest too in the same ways as I did within the subsearch.

Can you post an example that includes some sample data from source1 and 2?

Thanks,
J

[chrishartsock]

Ok, for example:
Source1:
01/04/15 3:46:11.000 PM Machine="ComputA" alert="major"
01/03/15 4:25:59.000 PM Machine="ComputB" alert="medium"
12/31/15 12:16:55.000 PM Machine="ComputC" alert="major"
12/30/15 10:42:28.000 AM Machine="ComputA" alert="minor"
12/30/15 10:37:57.000 AM Machine="ComputD" alert="major"

Source2:
01/05/16 06:41:35.000 PM Computer="ComputA" status=5.0
01/04/16 10:15:02.000 PM Computer="ComputA" status=2.0
01/04/16 09:24:50.000 PM Computer="ComputB" status=4.0
01/04/16 03:52:22.000 PM Computer="ComputA" status=4.0
01/04/16 02:45:01.000 AM Computer="ComputD" status=2.0
01/03/16 10:22:13.000 PM Computer="ComputA" status=5.0
01/03/16 10:14:28.000 PM Computer="ComputD" status=1.0
01/03/16 11:32:20.000 AM Computer="ComputD" status=4.0
01/03/16 04:54:58.000 AM Computer="ComputB" status=4.0
01/03/16 04:07:21.000 AM Computer="ComputC" status=2.0
01/01/16 11:01:54.000 AM Computer="ComputC" status=4.0
01/01/16 01:14:58.000 AM Computer="ComputB" status=5.0
12/31/15 09:56:52.000 PM Computer="ComputB" status=5.0
12/31/15 01:38:41.000 AM Computer="ComputD" status=3.0

So you see, I want to know what the status of the machine was from source2 at the time the alert was triggered from source1. Therefore, the earliest and latest will be different for each event from source1.