Recently I had to find all occurrences of some IP addresses that could be anywhere in an ITSM system and identify what form,field,entry they are in. This was part of a security audit a customer wanted to do so they were trying to find everywhere users had input in some sensitive systems's IP addresses in Remedy.
At first I figured this is a good task to perform in the DB itself and found the following which kind of did what was needed but it didn't get the specific entry's but at least gave me a picture of which tables and columns contained the IP I was looking for and then I would subsequently go search there and find the record.
So after see this in action, I decided to see what the difference in performance would be to do something similar but using only the Remedy API and came up with the attached program, figured I would share it. It actually runs in about the same amount of time and I can get a bit more detail then the SQL proc (5-6 minutes on this customers system)
Extract and run the program like this:
java -jar FindStringInFields.jar -x <servername> -u <username> -p <password> -t <port> -s <string to search for in doublequotes> -o <name of output file, defaults to output.csv if not set here>
This will provide a csv file with formName, EntryID, FieldName and FieldID for all found occurrences. If you want to get the similar output as the SQL proc, in excel just use the remove duplicates function and match based on formName and FieldID. With this out of the way and the work I've previously done on Entry Inspector, I will most likely add a replace capability in here so that it could search and replace anything found in any field without too much additional effort., could be useful for a few different use-cases I suspect.
Hope this is useful.
You can track any progress on my blog here as well: http://soleauthority.net/find-string-in-fields-utility/
- Updated to version 1.1
- Reordered the output to be Form Name, Field Name, Field ID, Entry ID to be more consistent
- Added simple progress bar to track progress based on total number of entries being searched
- Added output to screen showing number of results so you don't have to check the file to see how many or if there are even any results.
- Sample run from my local system searching for server name references:
- Updated to version 1.2
- Better semantics for writing the file, reduces the memory and time required for large list of matches