SPL map command

[torustad]

Good morning all,

I run this search

| makeresults count=4 | eval a="splunk_server" | map search="search index=esak-qa sourcetype=eport:access| table _time index $a$"

the value of the field splunk_server is listed in a column called splunk_server so it works as I want it to.

However, when I run this spl I get a column called "splunk_server host" with no values:

| makeresults count=4 | eval a="splunk_server host" | map search="search index=esak-qa sourcetype=eport:access| table _time index $a$"

How can I get two columns, namely splunk_server and host?
(I have also tried a="splunk_server,host")

Any tips?

Regards,
Bård Tørustad

[PickleRick]

OK. The obligatory warning - map is an unsafe command and is not to be used lightly. In most of use cases map can be substituted for another SPL construct to get the same results. Here be dragons, you've been warned.

Now.

You can simply use two "variables".

| makeresults count=4
| eval a="splunk_server"
| eval b="host"
| map search="search index=esak-qa sourcetype=eport:access | table _time index $a$ $b$"

Anyway, what's the underlying problem? What is it you're trying to do that you're resorting to use map?

[torustad]

sorry - just a test

[PickleRick]

You mean you're just testing how the command works as a form of exercise? That's OK. Just remember that in production there can be restrictions on using unsafe commands.

[torustad]

I’m trying to dynamically show only the columns that were updated in a table, based on SQL update statements extracted from Windows events, joined to CDC rows for the same table. The goal is to list changed attributes with their before/after values, without showing all columns.

[PickleRick]

Ok, for your "disappearing" reply - this forum has an automatic clasifying system which usually works quite well (without it we'd have tons of spam) but once every blue moon can falsely flag something as spam. Do report those.

Back to our main issue - this explanation is getting a bit vague without examples 🙂

It's usually best if you post a snippet of your (anonymized if needed) data and expected results.

[torustad]

And so, here is my (probably clumsy) attempt at the SPL:

index=windowsevents EventCode=33205 SourceName=MSSQLSERVER 
(database_name=prod_prosjekt)  (action_id="UP")    
(session_server_principal_name!=prod_prosjekt AND session_server_principal_name!="sa") |
table _time, object_name, action_id, server_principal_name, statement |  
eval result_type="main_events" |  
eval attrs="extract commaseparated list of the attributes"

append  [search index=windowsevents EventCode=33205 SourceName=MSSQLSERVER database_name=prod_prosjekt action_id=UP       	
(server_principal_name!=prod_prosjekt AND server_principal_name!="sa") |  	
eval E=_time-3, L=_time+5, tabell="cdc.dbo_"+object_name+"_CT"    |  	

map search="search index=esak-audit tablename=$tabell$ earliest=$E$ latest=$L$        |
fields _time, tablename, operasjon, client, <here I want to be able use attrs> |
fields - _raw |  eval result_type='audit_logs' "   
]| 
sort - _time

[PickleRick]

OK. Let me start from dissecting your search so far and pointing out a few things.

index=windowsevents EventCode=33205 SourceName=MSSQLSERVER 
(database_name=prod_prosjekt)  (action_id="UP")    
(session_server_principal_name!=prod_prosjekt AND session_server_principal_name!="sa")

This part is generally OK since you already have several conditions narrowing your search pretty well but remember that exclusion on its own is not a very good way to search. So if you just had the session_server_principal_name on its own, it would be very ineffective. (You can check how many rows are scanned vs. how many results are returned in your job details).

| table _time, object_name, action_id, server_principal_name, statement

OK. Here's my first objection. The table command in general should not be used early in the search. It moves the processing from the indexers to the search heads. So you no longer benefit from distributed searching. It also gathers all results Splunk has so far so you're not processing data in a streaming fashion. If you want to limit the set of fields processed further (due to memory constraints you might want to remove _raw sometimes for example), use the "fields" command.

| eval attrs="extract commaseparated list of the attributes"

I suppose this is a placeholder for opration(s) which would result in the "attrs" field being filled with names of several fields extracted from the event, right?

| append
  [ search index=windowsevents EventCode=33205 SourceName=MSSQLSERVER database_name=prod_prosjekt action_id=UP 
(server_principal_name!=prod_prosjekt AND server_principal_name!="sa")
   | eval E=_time-3, L=_time+5, tabell="cdc.dbo_"+object_name+"_CT"
   | map search="search index=esak-audit tablename=$tabell$ earliest=$E$ latest=$L$ |
fields _time, tablename, operasjon, client, <here I want to be able use attrs> |
fields - _raw | eval result_type='audit_logs' " 
]

OK. This one has more issues than just map.

First and foremost - unless you have a very well-defined case when the appended search is sure to be run quickly and return a small set of result, don't use it. Sometimes append can be optimized out internally to multisearch but it works only for specific searches and you can't count on it always. In this case your appended subsearch does not qualify for changing it to a multisearch.

So splunk will try to run the appended subsearch and might hit its limits (60 seconds runtime by default and 10k results returned). Since the appended search contains map, it will be "heavy" - for each row of the "base subsearch" Splunk will try to spawn a whole search - create it, parse, spawn a search job to indexers, fetch results, aggregate them... it's a lot of work. This is definitely _not_ the kind of search you'd want to have as an appended search.

Another thing which you overlooked is the scope. You're not only trying to dynamically list fields, you're trying to pass them over _from another search_ - look at it, you're generating the "attrs" field in the "first" search and want to use it in the appended search which - for Splunk - is a completely separate search and Splunk only aggregates results from those searches. Other than that those searches do not share any common "knowledge".

This is definitely _not_ how you should approach this.

Especially since you're doing two separate "runs" over the same result set (the part 

search index=windowsevents EventCode=33205 SourceName=MSSQLSERVER database_name=prod_prosjekt action_id=UP 
(server_principal_name!=prod_prosjekt AND server_principal_name!="sa")

is common to the initial search and the appended search.

So the issue here is that you're thinking about the problem as a programming problem - about things to _do_ whereas with Splunk you have to think about data manipulation. Think about it in terms of two data sets and combining them.

Please post at short snippet of those events:

index=windowsevents EventCode=33205 SourceName=MSSQLSERVER 
(database_name=prod_prosjekt) (action_id="UP") 
(session_server_principal_name!=prod_prosjekt AND session_server_principal_name!="sa")

and those events:

search index=esak-audit

Anonymize them of course if they contain sensitive information. It's about the structure and correlation, not about the data as such.

[torustad]

Messy...

CDC-events - fetched from database to csv-files that are ingested by a forwarder
most of the columns come from database attributes; they are many and usually only a very few should be displayed,
The second column is "operasjon" (Norwegian for operation) which is updatebefore (values before the update) and updateafter (values after the update)

"change_time","operasjon","databaseserver","databasenavn","tablename","start_lsn_hex","seqval_hex","__$start_lsn","__$end_lsn","__$seqval","__$operation","__$update_mask","KONTO_K","BUDSJ_AR","SOKTBEL","INNSTBEL","INNSTDAT","BEVBEL","FORBRUK","JUSTBEL","RGNSTA_K","ENDRDATO","ENDRINIT","PROSJNR","ORGBELOP","LOCK_FLAG","aretsBudsjettOverfortDato","__$command_id"
"07.10.2026 16:10:49","updatebefore","sqlserver","prod_prosjekt","cdc.dbo_BUDSJETT_CT","0x000E7C290000600A0002","0x000E7C29000060050002","System.Byte[]","","System.Byte[]","3","System.Byte[]","8770","2026","0","1017","12.01.2026 08:55:17","0","0,00","","1","23.09.2026 10:51:00","bruker","359390","0","System.Byte[]","","1"

"07.10.2026 16:10:49","updateafter","sqlsesrver","prod_prosjekt","cdc.dbo_BUDSJETT_CT","0x000E7C290000600A0002","0x000E7C29000060050002","System.Byte[]","","System.Byte[]","4","System.Byte[]","8770","2026","0","1018","12.01.2026 08:55:17","0","0,00","","1","23.09.2026 10:51:00","bruker","359390","0","System.Byte[]","","1"

[ITWhisperer]

As a general point, it is better to share data (and SPL for that matter) in a code block (using the </> formatting button). This is because general text is formatted, removing / compressing white spaces, etc. which may or may not be important to get good solutions.

[PickleRick]

OK. And I assume that in this case as a result you'd want to return the time of the operation, the database table name, and (again - in this case), the INNSTBEL value before and after the change. I also see a "client" field in your original attempt but I do not see it in the data. But that's for later.

The question is whether there is any field strictly correlating both those logs - not just giving you the rough time approximation but pointing to a specific search. 

I see the start_lsn_hex and seqval_hex fields which seem to contain some form of identifiers. Are they unique to an update operation or are they some form of bitmask or similar so they repeat throughout your data?

The problem with correlating based solely on time is that you might have two or more concurrent or very closely spaced operations in source 1 which might be difficult to distinguish in source 2 (especially if - for example - source 1 logs something at the beginning of an operation whereas source 2 - at the end or vice versa).

[torustad]

Hi,

Thanks for taking the time.

start_lsn_hex and seqval_hex are internal counters for the CDC - counts transactions and actions within a transaction I think. No connection with the sql audit log so must use timespansearch.  We have to live with that, but so far it has not been a problem with many hits in the CDC-log.
yes, I misplaced the extraction of the attributes; should have been in the append.
Append because I want the event from windows events to be listed together with the CDC events that were found.
Is there a way to not needing the second search against the windows event log?

Thanks and regards,
Bård

[torustad]

windows events originating from the sqlserver:

10/07/2026 04="10:45.857 PM"
LogName=Application
EventCode=33205
EventType=0
ComputerName=sqlserver.domain.no
SourceName=MSSQLSERVER
Type=Information
RecordNumber=578434686
Keywords=Audit Success, Classic
TaskCategory=None
OpCode=None

Comment: Message is interpreted as one field containing the rest of the event since key and value are separated by a colon (key:value)
Comment: I have set up props.conf on the search head to extract them into fields

Message=Audit event: audit_schema_version:1
event_time:2026-10-07 14:10:45.2482797
sequence_number:1
action_id:UP
21 additional attributes...
session_server_principal_name:domain\bruker
server_principal_name:domain\bruker
server_principal_sid:010500000000000....
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:sqlserver
database_name:prod_prosjekt
schema_name:dbo
object_name:PROSJEKT
statement:update budsjett set INNSTBEL=1016 where PROSJNR=999999
9 additional attributes ...
obo_middle_tier_app_id:

[torustad]

After these I will try to publish the search (I had to split the text for it to be accepted)

Thanks and regards,
Bård

[torustad]

Also the result is meant for normal users.

The list of attributes needs to be dynamic because the update statements are different.

So the only problem I have is how to display only the fields in attrs.

[torustad]

Then I want to use attrs from the main search in the map-command to display only those three attributes.
The CDC-events contain very many attributes so displaying all of them will make the result unreadable.

[torustad]

The search is done using a timespan of 5 seconds around the _time of the event from the main search

[torustad]

Good morning and thanks for bearing over with me,

The problem with the rejected message was presumably that it contained sqlcode.

The primary search finds sql update statements from an index with windows events that are generated by an sqlserver. Then the attributes that are to be updated are extracted from the set-part of the sql update statement. (for example if a,y and z are the attributes to be updated then attrs is a comma separated list of the three)

[torustad]

So I tried to post the explanation again and got this:

Success!

• Click to go to your post.

and I clicked and got this:

This reply was marked as spam and has been removed. If you believe this is an error, submit an abuse report.

So I am lost....

[torustad]

No, that was a test to see if my response to you arrived which this test did.

I have tried to post a full explanation of what I want, but it seems to have been thrown away.