Splunk Search

How do I lookup/return a field from one sourcetype to another sourcetype?

anil_ec21
Explorer

Hi All,

Newbie here, would appreciate if anyone can help to answer this little question

Feeds from Vulnerability Scanner having two sourcetypes -

sourcetype='A': We have asset_id and asset_name

sourcetype='B': We have asset_id and vulnerability_name

I need schedule a query (runs everyday) to output assest_id and asset_name details to a csv file or to an table from sourcetype='A'

In next query, need to table asset_name, vulnerability_name with respect to that of an asset_id.

Please NOTE: I was asked not to use 'Joining Commands'.

Thanks in Advance.

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Avoiding join is wise.
Is the lookup file needed for other purposes or is it just for this query? If the latter then you may not need it.
See if this gets you started.

sourcetype=A OR sourcetype=B | stats values(asset_name) as AssetName values(vulnerability_name) as VulnerabilityName by asset_id | table asset_id AssetName VulnerabilityName
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Avoiding join is wise.
Is the lookup file needed for other purposes or is it just for this query? If the latter then you may not need it.
See if this gets you started.

sourcetype=A OR sourcetype=B | stats values(asset_name) as AssetName values(vulnerability_name) as VulnerabilityName by asset_id | table asset_id AssetName VulnerabilityName
---
If this reply helps you, Karma would be appreciated.

anil_ec21
Explorer

Thanks Rich for the quick reply.

But I'm looking for -- something like this

My Query

sourcetype="B" severity=critical (site_id=5 OR site_id=6 OR site_id=1 OR site_id=3 OR site_id=33 OR site_id=45 OR site_id=49 OR site_id=44)
| eval zone=case(site_id==5,"W - DMZ",site_id==6,"P- DMZ",site_id==1,"P- Internal",site_id==3,"W - Internal")
| eval Days=floor((now()-strptime(first_discovered,"%Y-%m-%d %H:%M:%S.%3Q"))/(3600*24))
| table asset_id vulnerability_name site_id zone severity first_discovered Days

Output
asset_id, vulnerability_name, site_id, zone, severity, first_discovered, Days
1, Elevation of Privilege, 1, P-Internal, critical, 04:34.0, 0
2, Remote Code Execution, 5, W- DMZ, critical, 04:34.0, 0
3, Shell Remote Code Execution Vulnerability, 6, P-DMZ, critical, 56:06.1, 15
4, Obsolete Version of Microsoft Internet Explorer, 3, W-Internal, critical, 05:01.8, 355

Along with asset_id, I need to have asset_name in the output.

Please let me know how can I tweak my query to get the desired output.

Thanks

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this untested query.

(sourcetype="B" severity=critical (site_id=5 OR site_id=6 OR site_id=1 OR site_id=3 OR site_id=33 OR site_id=45 OR site_id=49 OR site_id=44)) OR sourcetype=A
| eval zone=case(site_id==5,"W - DMZ",site_id==6,"P- DMZ",site_id==1,"P- Internal",site_id==3,"W - Internal")
| eval Days=floor((now()-strptime(first_discovered,"%Y-%m-%d %H:%M:%S.%3Q"))/(3600*24)) | stats values(zone) as zone values(Days) as Days values(first_discovered) as first_discovered values(asset_name) as AssetName values(vulnerability_name) as VulnerabilityName values(site_id) as site_id by asset_id
| table asset_id AssetName VulnerabilityName site_id zone severity first_discovered Days
---
If this reply helps you, Karma would be appreciated.

anil_ec21
Explorer

Rich, your query works. Thanks.

As AssetName, VulnerabilityName are grouped by asset_id, output looks clumsy. Is there any other way? My idea was like - a scheduled query to update csv file or table with asset_id & asset_name.

And, the second query to refer/return the corresponding asset_name to that of an asset_id from the csv or table and display

| table asset_id AssetName VulnerabilityName site_id zone severity first_discovered Days

Sorry Rich, I'm bugging too much.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, you can do that. The nightly query for asset data would look something like this.

sourcetype=A | table asset_id asset_name | outputlookup Assets.csv

Then the vulnerability query looks like this.

sourcetype="B" severity=critical (site_id=5 OR site_id=6 OR site_id=1 OR site_id=3 OR site_id=33 OR site_id=45

| eval zone=case(site_id==5,"W - DMZ",site_id==6,"P- DMZ",site_id==1,"P- Internal",site_id==3,"W - Internal")
| eval Days=floor((now()-strptime(first_discovered,"%Y-%m-%d %H:%M:%S.%3Q"))/(3600*24)) | lookup asset_id OUTPUT asset_name
| table asset_id asset_name vulnerability_name site_id zone severity first_discovered Days

---
If this reply helps you, Karma would be appreciated.

anil_ec21
Explorer

Thanks Rick..! It's working... 🙂

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...