how to make a visualization using a lookup with ipranges? CIDR

[rosho]

Hi

I am visualizing in a map private ip addresses.
I created a lookup table which looks like this:

    ip      iprange        iprangeLatitude  iprangeLongitude    iprangeProvince 
 10.xx.y.1  10.xx.y.0/zz        53.749997        -71.9833294           Quebec           
 10.xx.y.2  10.xx.y.0/zz        53.749997        -71.9833294           Quebec           
 10.xx.y.3  10.xx.y.0/zz        53.749997        -71.9833294           Quebec           

But I have more than 1000 ips (mobile, vpn, etc). So is there a way to make a lookup with only the iprange instead of writing all the ips?

index=network srcip=10.xx.y.0/zz 
| stats count by srcip
| lookup ipranges_lookup ip as srcip
| geostats latfield=iprangeLatitude longfield=iprangeLongitude count by srcip

[rmmiller]

I think the most elegant solution here is to use an automatic lookup and modify transforms.conf and props.conf rather than trying to do this with straight SPL.
Automatic lookup link: https://docs.splunk.com/Documentation/Splunk/7.3.2/Knowledge/Makeyourlookupautomatic
(hyperlinks don't seem to be working for me at the moment)

Splunk answer 5916 looks like a very close approximation of what you're trying to do. Let's adapt that to your situation, because there is a twist in your question. You essentially want to define a subnet field with your events.
Answer 5916: https://answers.splunk.com/answers/5916/using-cidr-in-a-lookup-table.html

Lookup file contents: (same as your example, just remove the ip column)

iprange           iprangeLatitude    iprangeLongitude    iprangeProvince    
10.xx.y.0/zz        53.749997         -71.9833294           Quebec            
10.xx.y.0/zz        53.749997         -71.9833294           Quebec            
10.xx.y.0/zz        53.749997         -71.9833294           Quebec            

Your lookup file must contain an entry for each subnet. I assume you name your lookup file subnets.csv.

Your transforms.conf would look something like this:

 [your_arbitrary_lookup_stanza_name]
 filename = complete_path_to_subnets.csv
 max_matches = 1
 min_matches = 1
 match_type = CIDR(srcip)

Your props.conf must reference the lookup you have defined as follows, and I'm assuming you want it to apply to all events of this particular sourcetype:

[your_sourcetype_for_these_events_in_your_network_index]
LOOKUP-iprange = your_arbitrary_lookup_stanza_name srcip OUTPUTNEW iprange iprangeLatitude iprangeLongitude iprangeProvince

Note the output of the iprange field. This is the very same column we're using in conjunction with the CIDR command in transforms.conf.

At this point all of your events should have the iprange, iprangeLatitude, iprangeLongitude, and iprangeProvince fields added to them. This eliminates the need to call stats or do any heavy lifting with SPL, and something like this should work:

 index=network srcip=10.xx.y.0/zz 
 | geostats latfield=iprangeLatitude longfield=iprangeLongitude count by iprange 

Hope that gets you in the right direction!

rmmiller