Splunk Search

How to combine two searches in one search?

UMDTERPS
Communicator

Hello

I am trying to get data from two different searches into the same panel, let me explain.  Below is a search that runs and gives me the expected output of total of all IP's seen in the scans by System:

 

| inputlookup scan_data_2.csv
|join type=inner [
                |inputlookup KV_system  
                |where isnotnull(stuff)
                |eval stuff=split(stuff, "|delim|")
                |mvexpand stuff
                |spath input=stuff "IP Addr" output=ip
                |spath input=devices "OS"
                |fields ip "OS"
      ]
      
|join type=inner ip
      [inputlookup ips_of_systems.csv]
      
|dedup ip

|stats count by "Systems"
|rename count as "Total IP's in System Scans"
|sort - "Total IP's in System Scans"

 

That search gives me something like this as output (as expected):

 

Systems    Total IP's in System Scans
XYZ               10005
ABC                885

 

I would like to add a column that has the total number of servers by Systems whether it's seen in  the scans or not.  For example,  System "XYZ" has a total of 10005 seen in system scans, BUT overall they have 12000 IP's (only 10005 of which are seen by scans). 

Note:  "| inputlookup ips_of_systems.csv" has a roster of ALL the IP's seen, whether it's seen in a scan or not.
Note: "| inputlookup scan_data.csv" has a roster of all of the IP's seen in scans.

I want it to look something like this:

 

Systems    Total IP's in System Scans    Total IP's of Systems
XYZ               10005                       12000
ABC                885                         1000

 

Is that possible? (above) I'm not sure how to accomplish this, it looks easy, but I've been messing around with it for too long.

Heck, even adding another column adding a % overall seen would even be nice too (not sure how to do this):

 

Systems    Total IP's in System Scans    Total IP's of Systems  %Seen_in_Scan
XYZ               10005                       12000                 83%
ABC                885                         1000                 88%

 



Labels (3)
0 Karma
1 Solution

UMDTERPS
Communicator

I ended up running a daily search, like below (checks the entire keystore for the latest date within 30days and does a stats count).

 

|inputlookup KV_system  
                |where isnotnull(stuff)
                |eval stuff=split(stuff, "|delim|")
                |mvexpand stuff
                |mvexpand devices
                |mvexpand data
                |spath input=stuff "IP Addr" output=ip
                |spath input=devices "OS"
                |spath input=data "Systems" 
                |eval last_checked = 0
                |foreach *time [
                |eval last_check=if('<<FIELD>>' > last_check, '<<FIELD>>', 
                last_check)
                ]
                |where last_check > relative_time(now(), "-30d@d"
                |fields ip "OS" "Systems"
                |stats count by "Systems"
                |outputlookup total_ips.csv

 


I then take the daily search I did above and join it with the search I have in the panel:

 

| inputlookup scan_data_2.csv
|join type=inner [
                |inputlookup KV_system  
                |where isnotnull(stuff)
                |eval stuff=split(stuff, "|delim|")
                |mvexpand stuff
                |spath input=stuff "IP Addr" output=ip
                |spath input=devices "OS"
                |fields ip "OS"
      ]
      
|join type=inner Systems
      [inputlookup total_ips.csv]
      
|dedup ip

|stats count by "Systems"
|rename count as "Total IP's in System Scans"
|sort - "Total IP's in System Scans"

 


I couldn't figure out how to do both searches in the same search, so running two separate searches and then joining them by "Systems" was a work around. 



View solution in original post

0 Karma

UMDTERPS
Communicator

I ended up running a daily search, like below (checks the entire keystore for the latest date within 30days and does a stats count).

 

|inputlookup KV_system  
                |where isnotnull(stuff)
                |eval stuff=split(stuff, "|delim|")
                |mvexpand stuff
                |mvexpand devices
                |mvexpand data
                |spath input=stuff "IP Addr" output=ip
                |spath input=devices "OS"
                |spath input=data "Systems" 
                |eval last_checked = 0
                |foreach *time [
                |eval last_check=if('<<FIELD>>' > last_check, '<<FIELD>>', 
                last_check)
                ]
                |where last_check > relative_time(now(), "-30d@d"
                |fields ip "OS" "Systems"
                |stats count by "Systems"
                |outputlookup total_ips.csv

 


I then take the daily search I did above and join it with the search I have in the panel:

 

| inputlookup scan_data_2.csv
|join type=inner [
                |inputlookup KV_system  
                |where isnotnull(stuff)
                |eval stuff=split(stuff, "|delim|")
                |mvexpand stuff
                |spath input=stuff "IP Addr" output=ip
                |spath input=devices "OS"
                |fields ip "OS"
      ]
      
|join type=inner Systems
      [inputlookup total_ips.csv]
      
|dedup ip

|stats count by "Systems"
|rename count as "Total IP's in System Scans"
|sort - "Total IP's in System Scans"

 


I couldn't figure out how to do both searches in the same search, so running two separate searches and then joining them by "Systems" was a work around. 



View solution in original post

0 Karma