Service-Now search queries to populate Splunk Enterprise Security lookup tables

[milesbrennan]

I'm using the Service-Now application to build some lookup tables for user and asset information, which is needed for our Enterprise System Security (ESS) application.

As Service-Now is a fully relational database backend, I need to query several tables (sourcetypes) in order to build the lookup tables, however, I can't pass fields or variables to a subsearch in order to follow the link from one table to another.

Every item / entry in Service-Now is given a unique 32 alphanumerical character ID (field is "sys_id"), and each record / event has several sys_id values which relate to other properties. i.e. computer or network asset will have their own ID, and more to indicate the "site", "equipment owner" etc...

i.e. user_list table uses a "sys_id" to identify the user, and a second "sys_id" which links and belongs to the manager.

index=snow sourcetype="snow:sys_user_list"
| dedup sys_id | rex field=manager "(?<manager_id>\w{32})"
| rename user_name AS identity, introduction AS prefix, first_name AS first, last_name AS last, title AS suffix, mobile_phone AS phone2, manager_id AS managedBy, u_billing_entity AS bunit, vip AS priority, city AS work_city, country as work_country
| table identity, prefix, nick, first, last, suffix, email, phone, phone2, managedBy, priority, bunit, category, watchlist, startDate, endDate, work_city, work_country, work_lat, work_long
| outputlookup identities.csv

The "field" manager is a HTTP url to open up the manager's user page, but we've captured the manager's "sys_id" in a new "manager_id" field, which we are hoping to use to lookup the manager's name.

We have the similar situation with our assets lookup table, where "u_site", "company", and "u_managed_by" are located in different Service-Now database tables / sourcetypes.

 index=snow sourcetype="snow:cmdb_ci*" category=hardware | dedup name\
| rex field=u_site            "(?<site_id>\w{32})"\
| rex field=company           "(?<company_id>\w{32})"\
| rex field=u_managed_by      "(?<managed_id>\w{32})"\
| eval ip_address             = Dynamic, ip               = "" \
| eval host_is_expected       = false,   is_expected      = ""\
| eval host_should_timesync   = false,   should_timesync  = ""\
| eval host_requires_av       = false,   requires_av      = ""\
| eval host_should_update     = false,   should_update    = ""\
| eval active                 = if(operational_status == 1, "true", "false")\
| eval priority               = case(u_criticality == "Not_Agreed", "low", u_criticality == "Level_1", "medium", u_criticality == "Level_2", "medium", u_criticality == "Level_3", "high", u_criticality == "Level_4", "high", u_criticality == "Level_5", "critical", u_criticality == "Level_6", "critical")\
| rename mac_address AS mac, name AS nt_host, u_common_name AS dns, site_id AS country, company_id AS bunit, managed_id AS owner\
| table ip, mac, nt_host, dns, owner, priority, lat, long, city, country, bunit, category, pci_domain, is_expected, should_timesync, should_update, requires_av, active\
| outputlookup assets.csv

Any assistance is greatly appreciated.
Regards,

[ehaddad_splunk]

Hi,

We have the same type of lookups already bundled with the ServiceNow Add-on. Take the example of the incident table. It includes assignment_group sys_id but not the name of the assignment group. So we index the sys_user_group table and have a savedsearch "ServiceNow Sys User Group List" to build the mapping between sys_user_group id to sys_user_group. We then use that csv file in the lookup applied on the incident table. I would apply the same concept to any other field with sys_id. You will just need to create a new input to the table where you would need to get that mapping from.
I would use the latest version of the add-on 2.8.0 since it has few fixes related to the formatting of sys_ids returned by servicenow.
Hope this helps.

[thambisetty]

Hi,

I am wondering how those lookup files will be used in building asset information is used in Enterprise security. Because none of the fields are matching from the lookup files which are created using saved searches and asset lookup headers (ref Asset lookup header)

[XPGoD]

This is an accurate statement, it doesn't appear there is a two-way method to use what is in the lookups right off to push into an easy to design search.  Furthermore, it's stacked sys_id after sys_id after sys_id to make sense of the data as simple things are not what they appear in most deployments of the ServiceNow CMDB.