I have a lookup table from which I need to read the IP addresses one by one, perform calculations on each address, and then place the results in a new field.
Here is my query that performs calculations on the input IP address.
"IP-Address" NOT Audit NOT "%BGP_SESSION-5-ADJCHANGE" NOT "%BGP-3-NOTIFICATION" NOT "%BGP-5-NBR_RESET" NOT passive NOT "%BGP-3-BGP_NO_REMOTE_READ" AND "%BGP-5-ADJCHANGE: neighbor"
| rex "%BGP-5-ADJCHANGE: neighbor (?P<Interface_Name>(.+)),"
| transaction host startswith="Down" endswith="Up" keepevicted=true
| eval duration=if(duration==0,now()-_time,duration)
| convert rmunit(duration) as numSecs
| stats sum(numSecs) as Downtime_Duration
| eval Uptime_duration = 2592000 - Downtime_Duration
| eval Availability_Percentage = Uptime_duration / 2592000
| eval string_Downdur = tostring(round(Downtime_Duration), "duration")
| eval string_Updur = tostring(round(Uptime_duration), "duration")
| eval formatted_Downdur = replace(string_Downdur,"(?:(\d+)\+)?0?(\d+):0?(\d+):0?(\d+)","\1d \2h \3m \4s")
| eval formatted_Updur = replace(string_Updur,"(?:(\d+)\+)?0?(\d+):0?(\d+):0?(\d+)","\1d \2h \3m \4s")
| eval stringDownSecs = replace(formatted_Downdur, "^d (0h (0m )?)?","")
| eval stringUpSecs = replace(formatted_Updur, "^d (0h (0m )?)?","")
| table host, Interface_Name, _time, stringDownSecs, stringUpSecs, Availability_Percentage
| rename _time AS "Interface Down", host AS "Reporting Host", stringDownSecs AS "Total Downtime", stringUpSecs AS "Total Uptime"
Also, this is a query that shows how to read the lookup table.
| inputlookup LinkTunnelMap | table TunnelIP
The question is: how can I retrieve the IP addresses from this lookup table one by one, perform calculations on each address, and store the results in a new field like a for-loop process?
SPL, by its nature, is a while-do loop. It reads events one at a time and processes each one individually until there are no more events to process.
To perform a lookup on an IP address, use the lookup command.
| lookup LinkTunnelMap TunnelIP
``` Perform calculations and save the results ```
| eval results = <<some calculations on an IP address>>
Thanks. But I got this message for the first line:
"Your search isn't generating any statistic or visualization results. Here are some possible ways to get results."
For the main part, I need to do the calculation for all the IP addresses. For example, if we have 2 IP addresses in the lookup table which are 172 and 192. In the result we need to have a table like below:
IP result
172 111111
192 222222
@k_ashabi wrote:Thanks. But I got this message for the first line:
"Your search isn't generating any statistic or visualization results. Here are some possible ways to get results."
Click on the Events tab to see your search results. To see results in the Statistics or Visualization tab, the query must contain a stats, chart, or timechart command.
For the main part, I need to do the calculation for all the IP addresses. For example, if we have 2 IP addresses in the lookup table which are 172 and 192. In the result we need to have a table like below:
IP result
172 111111192 222222
How should Splunk derive "111111" from "172" or "222222" from "192"?
1. The event tab also shows zero results when I use your query. However, when I use "| inputlookup LinkTunnelMap | table TunnelIP", I can see the results in both the event and statistics tabs.
2. Allow me to explain the process in detail for better understanding:
a. Read the first IP from the LinktunnelMap table.
b. Search for the events related to this IP.
c. Perform calculations based on these events.
d. Save the result.
e. Go back to step "a" and repeat the process for the second, third, and subsequent IP addresses in the table (500 IPs).
f. Once all IP addresses are processed, we should have a table with all IP addresses and their respective results.
I hope this clarifies my question. Thank you.
I hope you realize my "query" is just a fragment of a query that must be incorporated into your query to produce results. Also, the field names I used may need to be changed to reflect the actual names in your data.
Here's one possible way to do what you seek
```Read the first IP from the LinktunnelMap table.```
"IP-Address" NOT Audit NOT "%BGP_SESSION-5-ADJCHANGE" NOT "%BGP-3-NOTIFICATION" NOT "%BGP-5-NBR_RESET" NOT passive NOT "%BGP-3-BGP_NO_REMOTE_READ" AND "%BGP-5-ADJCHANGE: neighbor"
```Search for the events related to this IP.```
``` Change "IP" to the name of the IP column in the lookup```
``` Add a rename command if the lookup column name does not match the index field name ```
[ | inputlookup LinkTunnelMap | fields IP ]
```Perform calculations based on these events.```
<<add commands here>>>
```Save the result. Nothing needed here as the calculation results are automatically added to the current event.```
```Once all IP addresses are processed, we should have a table with all IP addresses and their respective results```
| table IP <<results>>
Can you please explain how to read IP addresses one by one from a .csv table?
All IPs are in one column of this table.
The inputlookup command will read the entire CSV file in one go with each row becoming a separate event in the results. As it does with other results, Splunk will process each event one at a time.