Selection of specific text in a field

Navigation
1

I recently created a table in SQL Server , within that data I want to select descripcion those with the word Ropa and show them to me, so use the command SUBSTRING next to CHARINDEX to obtain my specific results, leaving the query as follows: 

SELECT
    SUBSTRING(
                Descripcion,
                (CHARINDEX('GRUPOSABANS\',descripcion)+12),
                (CHARINDEX('''.',descripcion))-(CHARINDEX('GRUPOSABANS\',descripcion)+12)
            ) AS Usuario,
        *
FROM LogData
WHERE
    CHARINDEX('GRUPOSABANS\',descripcion) > 0;

This results in the following: 

Usuario|id|fechahora|descripcion|

edejesus|1| 2017-03-03 17:06:09.880000| Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

edejesus|2| 2017-03-03 17:16:13.600000| Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

xptdacorazon|3| 2017-03-03 17:18:15.180000| Login failed for user 'GRUPOSABANS\xptdacorazon'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.115.57]

edejesus|4| 2017-03-03 17:31:58.450000|Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

xcpdvtdadulqueb|5|  2017-03-03 17:35:30.790000|Login failed for user 'GRUPOSABANS\xcpdvtdadulqueb'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.242.25]

As you can see in the query, I say that I especially select the words that are after the 12 letters that make up GRUPOSABANS\ including \ , print it to me in an alias of name Usuario besides that do subtraction within '' and before ending in . :

  

(CHARINDEX('''.',descripcion))-(CHARINDEX('GRUPOSABANS\',descripcion)+12)

And after that they are greater than 0, and as a result you should give me the username, which happens to be GRUPOSABANS\eduardoxt only to be eduardoxt , as you can see the result is successful.

But now I have a problem when I want to just show the ip, I occupy the same query only if the charindex grabs it after CLIENT: the code stayed the same: 

SELECT
SUBSTRING(
            Descripcion,
            (CHARINDEX('[CLIENT:',descripcion)+9),
            (CHARINDEX(']',descripcion))-(CHARINDEX('CLIENT:',descripcion)+9)
        ) AS Cliente,
    *
FROM LogData
WHERE
    CHARINDEX('[CLIENT: ',descripcion) > 0;

When executing the query it shows me the result in a few seconds, but then this error appears:

  

Invalid length parameter passed to the LEFT or SUBSTRING function.

I have already tried to compose it but I can not get it to show me the results, more than anything in a matter of milliseconds it shows the result I want, but then it shows me the error mentioned above, I would like to know what I am putting wrong: (.

In advance I would appreciate your help:).

--------------------- Update ------------------------- ----------------

I have already achieved it but I can not remove the ] that is at the end. The code I did was the following: 

SELECT
SUBSTRING(
            Descripcion,
            (CHARINDEX('[CLIENT: ',descripcion)+9),
            (CHARINDEX(']',descripcion))+(CHARINDEX('[CLIENT:',descripcion)+9)
        )AS Cliente,
        len(descripcion),
    *
FROM LogData
WHERE
    CHARINDEX('[CLIENT: ',descripcion) > 0;

The result is: 

Cliente|id|fechahora|descripcion|

10.248.56.69]|1| 2017-03-03 17:06:09.880000| Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

10.248.56.69]|2| 2017-03-03 17:16:13.600000| Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

10.248.115.57]|3| 2017-03-03 17:18:15.180000| Login failed for user 'GRUPOSABANS\xptdacorazon'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.115.57]

10.248.56.69]|4| 2017-03-03 17:31:58.450000|Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]

10.248.242.25]|5|  2017-03-03 17:35:30.790000|Login failed for user 'GRUPOSABANS\xcpdvtdadulqueb'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.242.25]

As I remove that ] , it shows me at the end of the first column of the name Client, where 10.248.242.25] is 10.248.242.25 .

I would like to thank you in advance.

    
asked by Santiago Huh 19.07.2017 в 20:26
source

2 answers

1

Santiago, there is not much science in this, unless you can implement some CLR that offers some functionality of regular expressions, the basic tools are quite limited. The important thing as Lamak mentions is to be paranoid with the format of what you are going to process because it is very easy after a CHARINDEX of 0 because you do not find something and goodbye logic. In my case I will use a slightly different approach that you will have to validate yourself.

Firstly, I verify that the processed chains have a format similar to the one you indicated in the examples by PATINDEX , this to avoid a later error, you may have to make it a little less precise. Then for the user I use the \ bar as anchor in this way so I do not need to see the domain (in case you eventually had different domains) and as a final anchor the . . For the IP the anchors are going to be [CLIENTE: and ] . Important when you place the initial anchor + its length + 1 will give you the position "from" to be trimmed, the position of the final anchor must be subtracted from the previous position to reach the trimming length. 

DECLARE @TMP TABLE (
        ID INT IDENTITY,
        DESCRIPCION  VARCHAR(255)
        )

INSERT INTO @TMP (DESCRIPCION)
VALUES
('Login failed for user ''GRUPOSABANS\edejesus''. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]'),
('Login failed for user ''GRUPOSABANS\edejesus''. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]'),
('Login failed for user ''GRUPOSABANS\xptdacorazon''. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.115.57]'),
('Login failed for user ''GRUPOSABANS\edejesus''. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]'),
('Login failed for user ''GRUPOSABANS\xcpdvtdadulqueb''. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.242.25]')


SELECT  DESCRIPCION,
    SUBSTRING(
            SUBSTRING(DESCRIPCION, 24, CHARINDEX('.', SUBSTRING(DESCRIPCION, 24, LEN(DESCRIPCION)))-2),
            CHARINDEX('\',SUBSTRING(DESCRIPCION, 24, CHARINDEX('.', SUBSTRING(DESCRIPCION, 24, LEN(DESCRIPCION)))-2)) + 1,
            LEN(SUBSTRING(DESCRIPCION, 24, CHARINDEX('.', SUBSTRING(DESCRIPCION, 24, LEN(DESCRIPCION)))-2))
        ) AS 'Usuario',

    SUBSTRING(DESCRIPCION, CHARINDEX('[CLIENT:', DESCRIPCION) + 10,  CHARINDEX(']', DESCRIPCION) - (CHARINDEX('[CLIENT:', DESCRIPCION) + 10) )
        AS 'IP'


    FROM @TMP
    WHERE   PATINDEX ( 'Login failed for user%[\]%. Reason: Could not find a login matching the name provided. [[]CLIENT: %]' , DESCRIPCION )  <>  0

The output would be something like this: 

DESCRIPCION                                                                                                                             Usuario         IP
======================================================================================================================================= =============== ============
Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]         edejesus        0.248.56.69
Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]         edejesus        0.248.56.69
Login failed for user 'GRUPOSABANS\xptdacorazon'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.115.57]    xptdacorazon    0.248.115.57
Login failed for user 'GRUPOSABANS\edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69]         edejesus        0.248.56.69
Login failed for user 'GRUPOSABANS\xcpdvtdadulqueb'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.242.25] xcpdvtdadulqueb 0.248.242.25
    
answered by 20.07.2017 / 19:21
source
1
First of all, your code should make sure that the description of the error contains both the string GRUPOSABANS\ and the string [CLIENT: before using SUBSTRING , otherwise it will throw an error like the one you're getting now (in the WHERE current only it is fixed that there is [CLIENT: ).

In that case, the code that I would use is: 

SELECT  CASE 
            WHEN descripcion LIKE '%GRUPOSABANS%'
            THEN SUBSTRING(descripcion,
                                CHARINDEX('GRUPOSABANS\',descripcion)+12,
                                CHARINDEX('. Reason',descripcion) - CHARINDEX('GRUPOSABANS\',descripcion)
                                 - 13)
            ELSE '0' 
        END Usuario,
        CASE 
            WHEN descripcion LIKE '%[[]CLIENT:%'
            THEN SUBSTRING(descripcion,
                                CHARINDEX('[CLIENT:',descripcion)+9,
                                CHARINDEX(']',descripcion) - CHARINDEX('[CLIENT:',descripcion)
                                 - 9) 
            ELSE '0' 
        END IP
FROM dbo.LogData;
    
answered by 19.07.2017 в 20:53
Collect objects from an ArrayList and load them into another Pass a variable to a modal - PHP