topic Re: Several searches on one table in Splunk Search

Several searches on one table

rachelei — Mon, 21 Jun 2021 08:58:09 GMT

I'm trying to take the results of 2 different searches in the same index and display them on one table, I tried to use with the 'append' function to combine two searches, but the search takes a long time, do you have another solution?
Any help on this would be appreciated.

Re: Several searches on one table

kamlesh_vaghela — Mon, 21 Jun 2021 09:00:52 GMT

can you pls share your sample searches?

Re: Several searches on one table

rachelei — Tue, 22 Jun 2021 07:39:14 GMT

These are the queries:

Query 1:
...
| eval op=mvzip(mvzip(mvzip(open_port, nexpose_port,"###"),shodan_port, "###"), digital_shadows_port, "###")
| eval Severity=mvzip(mvzip(mvzip(mvzip(nexpose_severity , panorays_severity ,"###"),shodan_severity, "###"),digital_shadows_info_severity,"###"),digital_shadows_sock_severity,"###")
| eval vulns=mvzip(mvzip(mvzip(mvzip(nexpose, Panorays, "###"),Shodan,"###"),Digital_Shadows_info,"###"),Digital_Shadows_sock,"###")
| eval Status=mvzip(mvzip(mvzip(mvzip(nexpose_severity , panorays_status ,"###"),shodan_severity, "###"),digital_shadows_info_severity,"###"),digital_shadows_sock_severity,"###")
| fillnull Status Severity vulns op value=""

| eval "Finding Date"=_time
| fillnull value="Unknown" field3 Public_IP "Asset Name" Organization Source "Finding Date" Panorays Shodan nexpose Digital_Shadows_info Digital_Shadows_sock Owner OS "Full OS String"
| stats min("Finding Date") as "Finding Date" by field3 Public_IP "Asset Name" Organization Source Panorays Shodan nexpose Digital_Shadows_info Digital_Shadows_sock Owner OS "Full OS String"
| fields - count
| rex field=field3 "(?<vuln>[^\r]+)\@@@(?<Sev>[^\,]+)\@@@(?<status>[^\,]+)"
| eval vuln=if(isnull(vuln),'field3','vuln')
| eval Sev=case(Sev="" OR isnull(Sev), "HIGH", true(), 'Sev')
.....
| rename Sev as Severity vuln as "Vulnerability Description"
| dedup "Asset Name" "Public_IP" "Vulnerability Description"
| convert ctime("Finding Date") as "Finding Date" timeformat="%m/%d/%Y"
| table Public_IP "Asset Name" "Full OS String" Organization "Vulnerability Type" "Vulnerability Description" Severity Source "Finding Date"

Query 2:

....
| eval Public_IP=coalesce('Public IP', 'Public_IP')
| eval Owner=coalesce('Owner', 'Confirmed Owner')
| eval "Asset Name"=coalesce(coalesce('Asset_name', 'Host_Name') , 'Domain Name')
| eval "Asset Name"=if('Asset Name'="", "None", 'Asset Name')
| eval Status=if(isnull('Status'), "Not Communicated Yet", 'Status')

| eval op=mvzip(mvzip(mvzip(open_port, nexpose_port,"###"),shodan_port, "###"), digital_shadows_port, "###")
| eval Severity=mvzip(mvzip(mvzip(mvzip(nexpose_severity , panorays_severity ,"###"),shodan_severity, "###"),digital_shadows_info_severity,"###"),digital_shadows_sock_severity,"###")
| eval vulns=mvzip(mvzip(mvzip(mvzip(nexpose, Panorays, "###"),Shodan,"###"),Digital_Shadows_info,"###"),Digital_Shadows_sock,"###")
| eval status=mvzip(mvzip(mvzip(mvzip(nexpose_severity , panorays_status ,"###"),shodan_severity, "###"),digital_shadows_info_severity,"###"),digital_shadows_sock_severity,"###")
| fillnull status Severity vulns op value=""

| makemv vulns delim="###"
| makemv Severity delim="###"
| makemv status delim="###"
| eval field1=mvzip(mvzip(vulns,Severity, "@@@"), status, "@@@")

| makemv op delim="###"
| eval op=mvdedup(op)
| eval field1=mvappend(field1,op)

| eval "Finding_Date"=_time
| convert ctime("Finding_Date") as "Finding_Date" timeformat="%Y-%m-%d"
| eval "Finding Date"=if(isnull('Finding Date'), 'Finding_Date', 'Finding Date')

| fillnull value="Unknown" field1 Public_IP "Asset Name" Organization Source "Finding Date" Panorays Shodan nexpose Digital_Shadows_info Digital_Shadows_sock Owner Description extracted_Source OS Title
| stats min("Finding Date") as "Finding Date" by field3 Public_IP "Asset Name" Organization Status Source Panorays Shodan nexpose Digital_Shadows_info Digital_Shadows_sock Owner Description extracted_Source Score source OS Title
| fields - count
| eval field1=if('field1'="Unknown", 'Description','field1')
| rex field=field1 "(?<vuln>[^\r]+)\@@@(?<Sev>[^\,]+)\@@@(?<status>[^\,]+)"
| eval vuln=if(isnull(vuln),'field1','vuln')
.....
| eventstats count by "Public_IP" Organization , "Vulnerability Description" Severity
| where (count="1" OR (count>"1" AND 'Asset Name'!="None"))
| table Public_IP "Asset Name" "Full OS String" Organization "Vulnerability Type" "Vulnerability Description" Severity Source "Finding Date"

I would like to combine the results of these 2 queries into one table.

Re: Several searches on one table

kamlesh_vaghela — Tue, 22 Jun 2021 17:14:45 GMT

@rachelei

Did you tried this?

QUERY_ONE | append [search QUERY_TWO]

Re: Several searches on one table

rachelei — Tue, 22 Jun 2021 18:03:05 GMT

I tried this function, but it takes a long time, do you have another option?

Re: Several searches on one table

kamlesh_vaghela — Wed, 23 Jun 2021 04:27:35 GMT

Can you please share some sample _raw events from Query 1 and Query 2?

Re: Several searches on one table

rachelei — Wed, 23 Jun 2021 10:34:58 GMT

Some examples of events from Query 1:

Some examples of events from Query 2:

Re: Several searches on one table

kamlesh_vaghela — Wed, 23 Jun 2021 10:42:07 GMT

@rachelei

I'm expected _raw instead table format. It will help me to combine both search.

Re: Several searches on one table

rachelei — Mon, 28 Jun 2021 14:06:08 GMT

Sorry but I can't share the values with you.
This is a an example of one raw event of query #1:

This is an example of raw event of query #2:

I hope this is what you meant.

Thanks a lot.

Re: Several searches on one table

rachelei — Sun, 04 Jul 2021 10:39:35 GMT

Do you have any idea?

Re: Several searches on one table

kamlesh_vaghela — Tue, 06 Jul 2021 07:14:18 GMT

@rachelei

sample _raw from base search of both searches. Screenshot will not help us to reproduce the issue.

_raw means this. just use </> from toolbar to keep it clear.

{"body":{"records": {"time": "2020-12-20T13:28:50.2164144Z","MachineGroup": "Windows 10", "Timestamp": "2020-12-20T13:27:18.6679858Z", "DeviceName": "3242d4e4.dc.democorp.com", "ReportId": 306737}}}

2-3 events from each base search will help to revert you back.

Re: Several searches on one table

rachelei — Sun, 11 Jul 2021 13:00:45 GMT

Please refer to the raw examples below, I hope this is what you meant:

Query #1:

_raw = "","","public_ip","owner","os","os_version ","","False","True","False","","","","","","","","","","","","","443###1720###5060","","date","vulnerable","finding_date","type","","False","0","0","2019-04-25 00:00:00","2007-08-03 00:00:00","823.15","severity ","vulnerability_score","vulnerability_ description ","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""

_raw = "","host"," public_ip "," owner ","","","","","","cve","cvss","cve_status","443###1720###5060","","date","status","finding date","type”,False###False###False###False###False###False","0###0###0###0###0###0","0###0###0###0###0###0","2019-04-25 00:00:00","822.77###536.03###447.98###182.94###483.61###456.55","severity","vulnerability_score ","vulnerability_description","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""

Query #2:

_raw = "public_ip ","","","","","","","True","False","False","zone","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","domain","TCP","2021-03-21 21:39:59","B","domain","","date","vulnerability","","vulnerability_description","severity"
_raw = "public_ip ","","","","","os","","","","","zone","443","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","domain","TCP###TCP###TCP","date”,C###C###C","domain","######","2021-05-29 12:00:00###2021-05-29 12:00:00###2021-05-29 12:00:00","vulnerability","######","vulnerability_description","severity"

Thanks!