Combine two searches using a lookup table

[troconn]

index=“client_index” AND Event_Type 6152
|eval new=substr(audit_filename, 16,14)
|eval ip=mvindex(split(new,”_”),0)
|eval mvip=split(ip,”.”),0)
|eval site_ip_range=mvindex(mvip,0).”.”.mvindex(mvip,1).”.”.mvindex(mvip,2)
|stats count BY site_ip_range
|sort no limit site_ip_range
|lookup siteusage.csv site_ip_range output Site_Name Site_Number 

|eval status=1
|append [| input lookup site usage.csv | table site_ip_range | eval status=0]
|stats max(status) as status by site_ip_range
|where status=0

The first part of this query pulls all active IPs and shows me how many time they have logged in ( Event_Type 6152 ) it then runs it against the lookup file siteusage.csv to give me the details associated with each IP. However, the results exclude “zero” usage.

The second query provides “zero” usage, but excludes/overwrites the first query and ONLY provides the zero usage report with no details from siteusage.csv.

My goal is to get a consolidated report from zero usage to XXX logins with the details from the siteusage.csv lookup table.

I can’t figure out how to combine them to do that.

[woodcock]

Like this:

index=“client_index” AND Event_Type 6152
|eval new=substr(audit_filename, 16,14)
|eval ip=mvindex(split(new,”_”),0)
|eval mvip=split(ip,”.”),0)
|eval site_ip_range=mvindex(mvip,0).”.”.mvindex(mvip,1).”.”.mvindex(mvip,2)
|stats count BY site_ip_range
|sort no limit site_ip_range
|eval status=1

| appendpipe [| input lookup site usage.csv | table site_ip_range Site_Name Site_Number]

|stats values(*) AS * BY site_ip_range

Those entries with a non-null status (value will always be 1) are from the first batch.

[somesoni2]

You need to include all the fields that you need from lookup in your append subsearch and stats command, like this

index=“client_index” AND Event_Type 6152
|eval new=substr(audit_filename, 16,14)
|eval ip=mvindex(split(new,”_”),0)
|eval mvip=split(ip,”.”),0)
|eval site_ip_range=mvindex(mvip,0).”.”.mvindex(mvip,1).”.”.mvindex(mvip,2)
|stats count BY site_ip_range
|sort no limit site_ip_range
|lookup siteusage.csv site_ip_range output Site_Name Site_Number
|eval status=1
|append [| input lookup site usage.csv | table site_ip_range Site_Name Site_Number| eval status=0]
|stats max(status) as status by site_ip_range Site_Name Site_Number
|where status=0

[troconn]

Thank you, that definitely worked, in that my "zero" results now have the other associated identifiers (which is awesome), but I still don't see any results from the first part of the query. I have 20 "zero" use sites and about 180 active ones and I don't see anything but the "zero" use sites.

For some reason the results for everything from line 1 through 8 are not showing up. It seems like I need an AND or an OR statement (or something) between line 8 and 9 to tell Splunk to take the results from the first half and combine them with the results from the second. Does that make sense? This is really driving me mad.

[somesoni2]

You've where status=0 clause which will filterout all rows from main search (your current logic keeps rows which are in lookup table but not in client_index search results). So it's doing what you designed it to do. If you want to see all results, remove last line with where clause.

[troconn]

I removed the "where status=0" line and unfortunately I get the same result. I know that you recommended that before. That line doesn't seem to have an effect either way.

[somesoni2]

What do you mean by zero use sites? How do you identify those records? Do you see them without append portion? Meanwhile give this a try as well.

index="client_index" AND Event_Type 6152
 |eval new=substr(audit_filename, 16,14)
 |eval ip=mvindex(split(new,"_"),0)
 |eval mvip=split(ip,"."),0)
 |eval site_ip_range=mvindex(mvip,0).".".mvindex(mvip,1).".".mvindex(mvip,2)
 |stats count BY site_ip_range
 |sort no limit site_ip_range
 |lookup siteusage.csv site_ip_range output Site_Name Site_Number
 |eval status=1 | fillnull value="NA"
 |append [| input lookup site usage.csv | table site_ip_range Site_Name Site_Number| eval status=0]
 |stats max(status) as status by site_ip_range Site_Name Site_Number
 |where status=0

[troconn]

The fillnull value=N/A did not change the results (sadly) (looks exactly the same with or without that fillnull addition.

I have about 200 locations with workstations. The script prior to the append portion shows me at which of those 200 sites someone has logged into a workstation and how many times (per site. The script takes the IP down to the third octet). What it doesn't parse is which of those 200 sites has not logged on (zero use).

The append portion of the script shows me the remainder that haven't logged on. Both scripts run independently work perfectly, but thus far, the append portion seems to ignore/overwrite the first part of the script when combined. I'd like to combine the result for a consolidated list. Does that make sense?