How can I use timechart to report IIS time_taken by location, using a CIDR inputlookup to compute the location by the client's IP?

[cyphertek]

Hello Splunk peoples!

Would someone please help me figure out how to use timechart to find IIS time_taken by locations using a CIDR inputlookup to figure out the subnet's locations? To show you what I do have working on my Splunk 6.5.2 instance:

the following search shows time_taken by c_ip from the IIS sourcetype:

index=foo sourcetype=iis
| timechart max(time_taken) by c_ip

the following search builds a “host Location SubNet” table:

index=foo sourcetype=iis
[| inputlookup MyServers]
| append 
[| inputlookup SubNetLocations]
| table host Location SubNet

…here is contents of my transforms.conf

[SubNetLocations]
filename = SubNetLocations.csv
min_matches = 1
default_match = NONE
match_type = CIDR(c_ip)

[MyServers]
filename = MyServers.csv

…here is the contents of my MyServers.csv lookup:

host
ServerA

…here is the contents of my SubNetLocations.csv lookup:

SubNet,Location
184.174.182.128/26,Remote_IP
172.31.33.0/25,LAN_IP

Yet, the following search:

index=foo sourcetype=iis
[| inputlookup MyServers]
| join SubNet* max=0
[| inputlookup SubNetLocations]
| timechart max(time_taken) as IISLatency by Location

...merges the Location values to 1 line, like:

If I mouse over the different Locations on the right, the line color in the graph changes to that Location's color. By now I have tried following at least 10 other Splunk community pages (some on inputlookup and others on lookup)...but I can’t get anything to graph the Location values separately into their own time_taken value lines.

Does anyone have any ideas how I can graph the Location values as their own line in the timechart graph? Thank you in advance.

[gvmorley · Accepted solution]

Hi,

I think what may be tripping you up here is the use of inputlookup as opposed to lookup.

I would suspect that inputlookup is appending the entire lookup table to your results. As opposed to what you want to do, which is the CIDR match of c_ip to the SubNet column in your csv table.

Therefore, to add the 'Location' fields to your results, you want:

| lookup SubNetLocations SubNet as c_ip

I've knocked up something that you could try (this just uses makeresults to create some sample data):

| makeresults count=1 
| eval time_taken=1000 
| eval host="serverA"
| eval c_ip="172.31.33.1"
| append 
    [| makeresults count=1 
    | eval _time=_time+1000 
    | eval time_taken=2000 
    | eval host="serverB"
    | eval c_ip="184.174.182.128"]
 | lookup SubNetLocations SubNet as c_ip
 | timechart max(time_taken) as IISLatency by Location

The syntax in your transforms.conf is also slightly off. The match_type line needs to specify a column in your lookup table. In your example, this would be 'SubNet'.

Go for this:

[SubNetLocations]
filename = SubNetLocations.csv
min_matches = 1
match_type = CIDR(SubNet)

Also, the contents of your 'MyServers' file needs more than one column. One that has something to match on from the events and then others that you want to add based on that match (like a friendly name).

I hope that gets you a bit closer to what you're looking for.

Graham.

[cyphertek]

gvmorley,

Thank you for your help, it worked!