Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

IPAM : swql request with several fields to be entered by user (need ur help!)

Hi!

First of all, hello everybody ! i need your help coz i just bought an IPX license for solarwinds IPAM and i am facing an issue.

I need to make a request in the front page (not a report) that search through the database free subnets with several custom fields to be entered by the users.

For exemple: search free subnets which have the following specifications: CIDR = xxx AND status = "yyy" AND location " zzz "

==> I can manage to have only one free field that a user

Request SWQL:

SELECT s.Location, s.Address, s.CIDR, s.VLAN, c.Zone, c.Etat, GroupId FROM IPAM.GroupsCustomProperties c

INNER JOIN

IPAM.subnet AS s ON c.groupId = s.SubnetId

where s.CIDR LIKE '${SEARCH_STRING}' AND s.location LIKE "PARIS" AND c.Status LIKE 'LIBRE'

order by s.location

==> so i need hundred of requests if i can have only one free fileds on a swql request

Sorry for the difficulty to explain my problem.. i dont know very well the software.. !

JF-C

Find more posts tagged with

Accepted answers

mesverrum

I've done fairly elaborate things like this by leveraging substrings, but it gets pretty complex to build them out sometimes.

Something like this, with the assumption that I am always entering 3 arguments like so: cidr:24,loc:PARIS,status:LIBRE

where s.CIDR LIKE substring('${SEARCH_STRING}',(charindex('cidr:','${SEARCH_STRING}')+5),(charindex(',loc:','${SEARCH_STRING}')-1))

AND s.location LIKE substring('${SEARCH_STRING}',(charindex('loc:','${SEARCH_STRING}')+4),(charindex(',status:','${SEARCH_STRING}')-1))

AND c.Status LIKE substring('${SEARCH_STRING}',(charindex('status:','${SEARCH_STRING}')+7),255)

I may not have got that exactly right as I was just winging it, but hopefully you get the picture and can tweak it to your needs.

This document covers all the available SWQL functions

SWQL Functions · solarwinds/OrionSDK Wiki · GitHub

All comments

mesverrum

I've done fairly elaborate things like this by leveraging substrings, but it gets pretty complex to build them out sometimes.

Something like this, with the assumption that I am always entering 3 arguments like so: cidr:24,loc:PARIS,status:LIBRE

where s.CIDR LIKE substring('${SEARCH_STRING}',(charindex('cidr:','${SEARCH_STRING}')+5),(charindex(',loc:','${SEARCH_STRING}')-1))

AND s.location LIKE substring('${SEARCH_STRING}',(charindex('loc:','${SEARCH_STRING}')+4),(charindex(',status:','${SEARCH_STRING}')-1))

AND c.Status LIKE substring('${SEARCH_STRING}',(charindex('status:','${SEARCH_STRING}')+7),255)

I may not have got that exactly right as I was just winging it, but hopefully you get the picture and can tweak it to your needs.

This document covers all the available SWQL functions

SWQL Functions · solarwinds/OrionSDK Wiki · GitHub

jf_c

First of all thank you so much to answer me

I tried but it does not work for me. In order to understand better coz i am a poor newbie in sql... :

i did not understand the +5 +4 -1 +7 255 in your request:

where s.CIDR LIKE substring('${SEARCH_STRING}',(charindex('cidr:','${SEARCH_STRING}')+5),(charindex(',zone:','${SEARCH_STRING}')-1))

AND C.Zone LIKE substring('${SEARCH_STRING}',(charindex('zone:','${SEARCH_STRING}')+4),(charindex(',etat:','${SEARCH_STRING}')-1))

AND c.Etat LIKE substring('${SEARCH_STRING}',(charindex('etat:','${SEARCH_STRING}')+7),255)

anyway i undestand what your are doing and you probably have the right answer.

JF_C

mesverrum

Charindex returns the position of the start of the search string. The + moves the start of the substring to after the colon character. The+255 on the last one is just me being lazy about finding the actual length of the string and just guessing at a high value.

mesverrum

Like I mentioned that exact code was just me guessing as I sat on a plane and might not be 100%, but the idea is to use sub strings and character index to find where each delimiter ends, until the start of the next delimiter.