Conditional regex captures and replacements
10 posts
Hi.
Is it possible to have conditional regex captures and replacements?
I would like to look for a value in another column and replace it with another value. Something like a virtual column with the target column as source and regex with ([blue or red]) or ([green or white]) to be replaced with active if the first group or inactive for the second. In this case I use just two choices, but I am looking for more than two.
To make sure I understood your requirements correctly:
You want to derive a value from another column, and then apply a regex-based rule such that:
- if the source value contains blue or red, it is replaced with one value (for example, active), and
- if it contains green or white, it is replaced with a different value (for example, inactive).
Is that correct?
Yes, it is correct.
Another example might be:
Virtual column - Item Name SW ID Source column - [Location] Regular expression - \b(Purchase Office|Finances|Budget)\b|\b(Administration|IT|Human Resources)\b Replacement expression - if $1 210|if $2 109
If the source value contains Purchase Office or Finances, it is replaced with one value (for example, number 210), and
if it contains Administration or IT, it is replaced with a different value (for example, number 109), etc.
Regular expressions and virtual columns alone cannot provide conditional output based on multiple criteria.
However, this is easily accomplished with a VBScript under Options - Remote Scripting:
Dim set1, set2, i
Dim strLower
strLower = LCase(Input.Column("Location"))
' Define Set 1 strings (returns 210)
set1 = Array("purchase office", "finances", "budget")
' Define Set 2 strings (returns 109)
set2 = Array("administration", "it", "human resources")
' Check Set 1
For i = 0 To UBound(set1)
If InStr(strLower, set1(i)) > 0 Then
Output.Write(210)
End If
Next
' Check Set 2
For i = 0 To UBound(set2)
If InStr(strLower, set2(i)) > 0 Then
Output.Write(109)
End If
Next
The script reads the Location column and checks which predefined set of departments it contains, outputting the corresponding code. If the location doesn't match any of these departments, no output is generated.
Alternatively, you can use this python script under Options - Remote Python, which achieves the same goal:
def get_location_code(location):
location_lower = location.lower()
set1 = ["purchase office", "finances", "budget"]
set2 = ["administration", "it", "human resources"]
if any(item in location_lower for item in set1):
return 210
elif any(item in location_lower for item in set2):
return 109
return None
# Usage:
print(get_location_code(Input.Column("Location")))
Hi
I implemented a modified version of the Remote Python script - get_location, and it was working, but after the last snapshot build all pythons are failing to read from the source column. After a selected row (host) rescan it works, but not at the first app scan.
The script did work, but did not read from (Input.Column("Location")). So it returned None, unless a rescan was done... then it returned the expected value.
Thanks for the details - that narrows it down significantly. The following will let us reproduce it exactly:
1. How is your "Location" column produced?
Specifically, is it:
- a Remote SNMP or Remote WMI query column, or
- a Virtual Column, or
- a Remote Scripting (VBScript) column?
The reason it matters: columns are populated in a fixed order during a scan, and a Remote Python column reads the other columns as they stand at the moment it runs. If "Location" happens to be produced by a step that runs after the Python column (such as a Remote Scripting/VBScript column), then on the very first scan of a fresh host the Python script sees it empty and returns None, while a rescan shows the value carried over from the previous pass, which matches exactly what you're describing.
2. Even better, could you send us your configuration file?
Simply click File - Current Config - Save to File. That would remove all guesswork.
Feel free to wipe anything sensitive before sending. With either the answer to (1) or the config file, we'll be able to reproduce the behaviour and sort out the fix.
Hi.
Sorry for the delay.
My "Location" column is produced from a VBScript in the Remote Scripting area named "Computer_OU - Scripting"
***********************
"Computer_OU - Scripting"
************************
Dim strComputerName, strItemInfo, strAnswerInfo
'Call Ip2Hostname function to resolve Ip
Ip2Hostname
Dim wshShell, wshNetwork
' Create Global Objects
Set wshShell = CreateObject("WScript.Shell")
Set wshNetwork = CreateObject("WScript.Network")
'output.write "Computer DN: " & GetDN
strItemInfo = GetDN
'output.write strItemInfo
'Call RegEx_ITEM function to apply regex to GetDN result stored on strItemInfo
RegEx_ITEM
Function RegEx_ITEM()
' Use to apply some regex to an item when needed or required
Set re = New RegExp
re.Pattern = "OU=COMPUTADORAS ([^,]+)"
Set matches = re.Execute(strItemInfo)
If matches.Count > 0 Then
Set match = matches(0)
msg = "Found match """ & match.Value & _
""" at position " & match.FirstIndex & vbCRLF
'output.write msg
If match.SubMatches.Count > 0 Then
For I = 0 To match.SubMatches.Count-1
msg = msg & "Group #" & I+1 & " matched """ & _
match.SubMatches(I) & """" & vbCRLF
strAnswerInfo = match.SubMatches(I)
'output.write msg
'output.write strAnswerInfo
Next
End If
'msgbox msg, 0, "VBScript Regular Expression Tester"
output.write strAnswerInfo
Else
'msgbox "No match", 0, "VBScript Regular Expression Tester"
output.write "No match"
End If
End Function
Function Ip2Hostname()
' Use to resolve ip to hostname
Dim ipAddress,hostname
ipAddress = input.current
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & ipAddress & "\root\cimv2")
If Err.Number <> 0 Then
hostname = "Cannot resolve hostname"
Err.Clear
Else
Set colItems = objWMIService.ExecQuery("Select * FROM Win32_ComputerSystem", "WQL", _
wbemFlagReturnImmediately + wbemFlagForwardOnly)
For Each objItem In colItems
hostname = objItem.Name
Next
End If
strComputerName = hostname
End Function
Function GetDN()
' Use the NameTranslate object to convert the NT name of the computer to
' the Distinguished name required for the LDAP provider. Computer names
' must end with "$". Returns comma delimited string to calling code.
Dim objTrans, objDomain
' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1
Set objTrans = CreateObject("NameTranslate")
Set objDomain = getObject("LDAP://rootDse")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_NT4, wshNetwork.UserDomain & "\" _
& strComputerName & "$"
GetDN = objTrans.Get(ADS_NAME_TYPE_1779)
'Set DN to upper Case
GetDN = UCase(GetDN)
End Function Remote Python columns are evaluated before Remote Scripting (VBScript) columns, so on the first scan of a host the Location value didn't exist yet when your Python script ran - Input.Column("Location") returned empty. A rescan worked because the value was already there from the previous pass.
This is now fixed: a Python column reading a Remote Scripting column via Input.Column(...) resolves it on demand during the first scan, so you get the right value immediately. You can download the new build here.
One caveat: Python receives the column's raw script output. If you've set a capture/replacement regex on that column, the regex still applies to its own cell but not to the value passed to Python.
Hi, seems that last build snapshot VBScript scanning gets affected. Python is resolving, but VB is not (VBscripting depending on VBscripting column values to resolve).
My "Computer_OU - Scripting" VBscript is working fine, but not the other VBscript (the one from the upper part of earlier posted messages, that depends on mines, reading the Location column and checking which predefined set of departments contains, ...).
Somehow apps definitions seem to be affected too.
I created a new one that does not want to appear.
Something like...
Application Title:
R|Remote Desktop|mstsc|Admin|Test|Parameters Not Required
Command line:
cmd /k "@echo off" & "set HOST=%1" & "set USERNAME=domain\user" & "cmdkey /generic:TERMSRV/%HOST% /user:%USERNAME% /pass:%PWD_USER_VAR" & "mstsc.exe /admin /v:%HOST%" & "cmdkey /delete:TERMSRV/%HOST%"
cmd /k "@echo off" & "set HOST=%1" & "set USERNAME=domain\user" & "cmdkey /generic:TERMSRV/%HOST% /user:%USERNAME% /pass:%PWD_USER_VAR" & "mstsc.exe /admin /v:%HOST%" & "cmdkey /delete:TERMSRV/%HOST%"
%1 (share name) is the reason why it did not appear. I suppose Options Shares - Shared Resources should be enabled for this to work.
With %0 it did appear.