Correlate (and tag!) Splunk events with Change Control Tickets

[ftk]

How can I correlate splunk events with change control tickets in our ticketing system?

If I have just a few events I can tag them with the ticket number. But what if I update my systems and FIM and update logs spew out thousands of events? How can I effectively relate these events to a change control ticket at a later time?

One idea I have had is in addition to alerting via email on certain conditions, I'll configure to write to a special summary index in which the alerts can then be tagged by change control. This would be fairly easy to report against in order to find gaps.

I haven't found any leads in the documentation, which surprises me -- there have to be other people with the same problem out there. How did you solve this issue?

[BobM]

I think you could open your workflow in a form view where the form asks for a ticket number and appends it on a post process search.

[ftk · Accepted solution]

Thanks to gkapanthy's suggestions I have managed to work through this problem.

The problem again is to tag one or more events in splunk with a change control ticket number, and no singular item tagging currently being available natively in splunk. The basic approach is adding information needed to identify unique events to a lookup, and then using that lookup to enrich regular search results and reverse lookup splunk events based on ticket numbers.

1. Define Lookup

First we define our lookup in transforms.conf and props.conf. I did it in $SPLUNK_HOME/etc/apps/my_app/local but this could also be done in the search app or at the system level. transforms.conf defines the lookup CSV (in $SPLUNK_HOME/etc/apps/my_app/lookups) and specifies I want to pull more than 1 event out of it (since I want to tag multiple events at times) :

[ticket_correlation]
filename = ticket_correlation.csv
max_matches = 9999999

props.conf applies the lookup to all hosts on my splunk instance. It verifies _cd, index, and splunk_server before spitting out a ticket number:

[host::*]
lookup_tickets = ticket_correlation _cd index splunk_server OUTPUTNEW change_ticket

2. Setup Tagging Workflow Action

Set up a workflow action at the event level. This is easy to do in the Manager UI. Assign a label (I use "Assign Change Ticket: $change_ticket$") and a unique identifier. Show the action in the Event menu and select Action type search. Use the search string below, and save the workflow.

| loadjob $@sid$ events=true 
| eval change_ticket="$change_ticket$" 
| eval change_ticket = if(isnotnull(change_ticket),change_ticket,"TICKET_MISSING") 
| fields - _kv,_meta,_raw,_pre_msg,_s*,_h,_indextime 
| fields + _cd, index, splunk_server, change_ticket, host 
| inputlookup append=true ticket_correlation 
| outputlookup ticket_correlation

Here is what the search does when invoked from an event:

1. Load the the events from the search the workflow was executed from
2. Bring over our change_ticket value
3. Make sure change_ticket is not null. If it is, put a placeholder in the lookup table that we can easily edit later
4. Remove pesky internal fields we don't want in the lookup table (the want to show up if you include _cd in the | fields +)
5. Only keep the _cd, index, splunk_server, change_ticket, and host fields
6. Load any data that currently exists in our ticket_correlation lookup so we can append to it
7. Output the new lookup table (original lookup table + new events)

3. Tagging Events

Here is a scenario I would use this workflow in: I receive an alert, a new user account has been created. I run the alert search, and sure enough, a new user account has been created, "Bilbo".

index="foo" sourcetype="WinEventLog:Security" event_id="624" earliest=-1h

I look in our ticketing system, and sure enough there is an approved change request for a new account for new hire "Bilbo". I verify that the only results returned from my current search apply to the change ticket. Hooray, they do: let's tag 'em!

I append the ticket number as a field to the current search:

index="foo" sourcetype="WinEventLog:Security" event_id="624" earliest=-1h | eval change_ticket="12345"

Now I expand the Event menu on an event and select the "Assign Change Ticket: 12345" workflow. A new search is executed that exports the identifying fields for my event(s) to the ticket_correlation lookup table.

4. Enriching Searches and Reporting on Tickets

Since the lookup is applied to every host, events that have been tagged by our workflow action will show up with an additional change_ticket field. We can pull up events based on a specific query:

index=foo change_ticket="12345"

Or we can pull out all tagged events:

index=* change_ticket=*

Now depending on the size of your indexes this will take quite a wile as the _cd field isn't indexed. Chances are you don't have to run searches like this too often (probably only when the auditors are on site).

5. Conclusion

• It is definitely possible to utilize lookups to tag unique events in splunk. There are some caveats (see Lowell's comment on gkapanathy's answer) but it appears to work quite well. I guess only time will tell if it'll hold up to production use. If it doesn't I will revisit this topic and definitely update it with any changes I make.
• The lookup definition in props.conf may not be as efficient as possible (since I am doing it on all hosts). If this turns out to be a problem I will make changes and post here.
• One pain point is trying to remember to add the eval change_ticket=xxx to events I want to save but it is a lot easier to do than to type up the inputlookup/outputlookup commands every time. I am also mostly unfamiliar with Python and didn't want to learn it to solve this problem (plus this way this whole setup should technically be supportable by splunk!)
• Don't forget to make the workflow action public, else only you will be able to use it! (which may be desirable)
• If anybody comes up with a more elegant or user friendly solution please do post it here as I am definitely interested in it.

[koshyk]

would be good, if someone can create an app for ticketing mechanism

[araitz]

_cd is short for _code.

This value is NOT indexed! Therefore your lookup/search performance may vary depending on the size of your index and the time range that you are searching across. I would recommend considering the addition of _time to the lookup as well as leveraging a temporal lookup.

[gkanapathy]

I suppose the way I'd approach this would be to create a field action that calls a script that in turn updates a table that associates the ticket number with the combination of the three fields: (_cd,index,splunk_server). This table could be in some custom external database, or in a Splunk CSV lookup file. You could also have other scripts update this table as long as they could access those three fields from the data.

You'd then create a corresponding lookup against this table (scripted or file-based as appropriate) that input _cd,index,splunk_server and output the ticket number as a new field.

The main disadvantage of this approach is that searches for events associated with a specific ticket number (i.e., a reverse lookup on the ticket number) will quite slow since the _cd field is not indexed.

[maverick]

@gkanapathy - in this case he may have multiple events that need to be associated all at once with the corresponding ticket id. Wondering if, when using transaction command, the resulting metaevent has a unique _cd as well that can be linked (or enriched via the lookup to the ticketing db).

Another idea could be to create a custom command that links the metaevent with the ticket id and writes final report result into summary index.

[Lowell]

I believe that _cd contains two numbers separated by a colon. The first is the bucket id and the second is an id assigned to a single event within a bucket. So with all these fields together, you can determine the unique id of the event, which bucket it's in, which index the bucket is in, and which server the indexed is located on. Which should uniquely identify a single event. (Of course, all of this assums that you never change the name of your server or index, or renumber your buckets, etc...)

[ftk]

_cd appears to be an internal field but I could not find any info (other some configs that reference it) in the documentation. I can't get it to display in the search interface. Do you have more info on this fields? Is this a unique event ID?