Splunk Search
Highlighted

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

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
Highlighted

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

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, an upvote would be appreciated.

View solution in original post

Highlighted

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

Explorer

Thanks Rich for the quick reply.

But I'm looking for -- something like this

My Query

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

Output
assetid, vulnerabilityname, siteid, zone, severity, firstdiscovered, 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 assetid, I need to have assetname in the output.

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

Thanks

0 Karma
Highlighted

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

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, an upvote would be appreciated.
Highlighted

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

Explorer

Rich, your query works. Thanks.

As AssetName, VulnerabilityName are grouped by assetid, output looks clumsy. Is there any other way? My idea was like - a scheduled query to update csv file or table with *assetid* & 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 assetid AssetName VulnerabilityName siteid zone severity first_discovered Days

Sorry Rich, I'm bugging too much.

0 Karma
Highlighted

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

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(siteid==5,"W - DMZ",siteid==6,"P- DMZ",siteid==1,"P- Internal",siteid==3,"W - Internal")
| eval Days=floor((now()-strptime(firstdiscovered,"%Y-%m-%d %H:%M:%S.%3Q"))/(3600*24)) | lookup assetid OUTPUT assetname
| table asset
id assetname vulnerabilityname siteid zone severity firstdiscovered Days

---
If this reply helps you, an upvote would be appreciated.
Highlighted

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

Explorer

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

0 Karma