topic Re: Splunk Use Case in Splunk Search

Splunk Use Case- How can I compare two IP addresses when IP address is only available in one sourcetype?

Splunk_Master01 — Tue, 16 Aug 2022 13:11:58 GMT

Hi All,

I am trying to build a use case with the below scenarios:

1) Person A can do tasks X and Y but not task Z or,

2) Person A can do tasks Y and Z but not task X or,

3) Person A can either do task X or task Y or task Z

At no given point is Person A allowed to conduct all three tasks and at no given point should the IP addresses of Person A and Person B be the same.

Information is being picked from two separate source types from the same index.

The challenge is picking the IP address when Person A does tasks X and Y and Person B does task Z, how can I get the IP addresses of both Person A and Person B, so as to compare and make sure that they are two different IP addresses, keeping in mind that the IP address is only available in one source type and not the other?

Any assistance on this would be appreciated ...

Re: Splunk Use Case

gcusello — Tue, 16 Aug 2022 07:58:51 GMT

Hi @Splunk_Master01,

maybe there's an error in the question: you sayed:

3) Person A can either do task X or task Y or task Z

At no given point is Person A allowed to conduct all three tasks

then you don't give any condition for PersonB.

Then some additional information:

I suppose that IP address is in a field called "ip_address"
you have ip_address of PersonA and PersonB from the first sourcetype, instead tasks are from the second sourcetype, is this correct?

Ciao.

Giuseppe

Re: Splunk Use Case

Splunk_Master01 — Tue, 16 Aug 2022 08:22:07 GMT

Hi Giuseppe,

Yes. IP address of Person A and Person B are in one source type and the tasks being performed by Person A and Person B are in the other source type and both source types belong to the same index.

Re: Splunk Use Case

gcusello — Tue, 16 Aug 2022 14:33:12 GMT

Hi @Splunk_Master01,

you can correlate data from different sourcetypes using the stats command, something like this:

index=your_index (sourcetype=sourcetypeA OR sourcetype=sourcetypeB) | stats dc(ip_address) AS ip_count values(ip_address) AS ip_address values(task) AS task BY person

Having all these correlated information, you can find the rules you like: e.g. if there's more than ip, etc...

Ciao.

Giuseppe

Re: Splunk Use Case

Splunk_Master01 — Wed, 17 Aug 2022 05:26:30 GMT

Hi Giuseppe,

I have tried this, but it still doesn't bring the IP addresses of both Person A and Person B.

The query picks the IP address of only Person A.

I need both IP addresses to be displayed.

Re: Splunk Use Case

yuanliu — Wed, 17 Aug 2022 07:06:55 GMT

gcusello's code should result in a list like the following

person

ip_count

ip_address

task

Person A

10.101.8.1

10.101.9.3

Person B

10.101.7.105

10.101.8.1

10.101.8.12

If in your data you only see one line, check your search time period to make sure both Person A and Person B have activities.

The code does not directly address the part of requirement about At no given point. You may need a time bucket variable to flesh that out. In physics, there is no such a thing as a point in time, so I assume that you meant "during no given time interval" (of a predefined length). In general you don't want to deal with a rolling time interval in Splunk, so choose a meaningful span, say, 1h, and use gcusello's formula with this modifier

index=your_index (sourcetype=sourcetypeA OR sourcetype=sourcetypeB) | bin span=1h _time | stats dc(ip_address) AS ip_count values(ip_address) AS ip_address values(task) AS task BY _time person

The span can be any value that is a common multiple of your data collection intervals.

Re: Splunk Use Case

Splunk_Master01 — Thu, 18 Aug 2022 07:06:52 GMT

I am using the below query:

index=myindex (sourcetype="mysourcetype" OR sourcetype=mysouretype)
| eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z)
| stats dc(IP) as IP_COUNT values(IP) as IP values(USERID_X) values(USERID_Y) values(USERID_Z) BY USER

The reason behind usage of mvappend is because the usernames in both source types have different titles.

The results I get with the above query are depicted in the table below:

USER	IP_COUNT	IP	USERID_X	USERID_Y	USERID_Z

The IP that is currently being picked is for "USER" and "USERID_X" but I require IP addresses of USERID_Y and USERID_Z as well, as in some situations they vary. After that I need to put in the IF statement to compare IP addresses to make sure that they are not the same.

Re: Splunk Use Case

yuanliu — Thu, 18 Aug 2022 08:40:43 GMT

First, how does USERID_A factor into the equation in your illustration? (I interpret "title" as field name.)

Second, how did you determine that only USERID_X is being picked up? More fundamentally, how did you determine that events containing USERID_Y and USERID_Z are actually in your search range?

Let's examine data. Perform this simple test for each of USERID_X, USERID_Y, USERID_Z

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) USERID_X=* | eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z)

Are there any events with USERID_Y and USERID_Z? What are values of USER in each case?

Re: Splunk Use Case

Splunk_Master01 — Thu, 18 Aug 2022 09:22:08 GMT

I have run the below test query changing the USERID to X, Y and Z respectively:

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) USERID_X=*
|  eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z)

Each time the USERID changes to X,Y or Z, results are populated for USER, USERID_X, USERID_Y and USERID_Z.

What I've noticed is that either values in USER, USERID_X and USERID_Y are the same or values in USER, USERID_Y and USERID_Z are the same.

Re: Splunk Use Case

yuanliu — Thu, 18 Aug 2022 09:38:26 GMT

Maybe I should have designed the test a little more comprehensively, as your main interest is to examine IP. How about

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) USERID_X=* | eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z) | eventstats values(IP) as IPs

Replace USERID_X with other field names. Do the resultant IPs field vary? How do values of IPs overlap?

Re: Splunk Use Case

Splunk_Master01 — Thu, 18 Aug 2022 10:43:24 GMT

I have run the below query as advised.

I get no results when I change the values to X,Y and Z respectively.

However removing the line USERID_X=* generates results of IP addresses.

The users are different in every row, but the IP addresses being picked are for all IP's in a subnet and all subnets and not the specific users that are part of the row.

Results are something as below:

USER

USERID_X

USERID_Y

USERID_Z

IPs

Mary

Jane

Mary

Jane

10.10.1.1

10.10.1.2

10.10.1.3

10.10.2.1

10.10.2.2

10.10.2.3

10.10.3.1

10.10.3.2

10.10.3.3

....

How can I find out which IP belongs to Mary and which IP belongs to Jane from the long list of IPs?

Re: Splunk Use Case

yuanliu — Thu, 18 Aug 2022 22:13:49 GMT

@Splunk_Master01 wrote:
I have run the below query as advised.
I get no results when I change the values to X,Y and Z respectively.
However removing the line USERID_X=* generates results of IP addresses.

This means that USERID_X, USERID_Y, etc., are not searchable fields in raw search. This is just to get an understanding of the data and unrelated to the solution.

To perform stats on disparate field names, you may iterate over them with foreach. For example,

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>)] | stats dc(IP) as IP_COUNT values(IP) as IP BY USER

(This assumes that different field names do not appear in the same event.) If these field names have a common pattern, you may use wildcard to enumerate, like foreach USERID_*.

Re: Splunk Use Case

Splunk_Master01 — Fri, 19 Aug 2022 05:48:10 GMT

I require something like the below:

USER	USERID_X	IP OF USERID_X	USERID_Y	IP OF USERID_Y	USERID_Z	IP OF USERID_Z
MARY MARY JANE	MARY	10.10.1.1	MARY	10.10.1.1	JANE	10.10.1.2
BEATRICE ARTHUR ARTHUR	BEATRICE	10.10.2.1	ARTHUR	10.10.2.3	ARTHUR	10.10.2.3
AMY DIANA BELLA	AMY	10.10.3.1	DIANA	10.10.3.2	BELLA	10.10.3.6
JOSEPH JACINTA JACINTA	JOSEPH	10.10.4.1	JACINTA	10.10.4.1	JACINTA	10.10.4.1

Where the row highlighted in bold needs to be fired as an alert as all the IP's are the same.

How can I achieve something like the table above?

Re: Splunk Use Case

yuanliu — Sat, 20 Aug 2022 04:17:54 GMT

You need to better describe data to explain how they relate to your mockup. What is the mechanism/criteria to group MARY, MARY, JANE in one row and BEATRICE, ARTHUR, ARTHUR in another row? Is it some sort of session, transaction/sequence? Time bucket?

Re: Splunk Use Case

Splunk_Master01 — Sat, 20 Aug 2022 04:22:54 GMT

Transactions being conducted by them.

The problem is some data is one sourcetype and IP address is in another sourcetype.

So picking IP addresses of the people is a challenge.

Re: Splunk Use Case

yuanliu — Sat, 20 Aug 2022 05:22:50 GMT

Still unclear. Do you mean each row is associated with a unique transaction ID?

Re: Splunk Use Case

Splunk_Master01 — Sat, 20 Aug 2022 05:59:40 GMT

Yes.

Re: Splunk Use Case

yuanliu — Sat, 20 Aug 2022 07:16:35 GMT

Let's first take the alert action you described in the mockup. This should be sufficient for that purpose, is it not?

Judging from your mockup, the above should give something like

transactionID

USER

10.10.4.1

some ID

JOSEPH

JACINTA

What is the use of knowing "IP of USERID_X", "IP of USERID_Y"? (Especially when, as illustrated, MARY has the exact same IP address in the same transaction whether she appears as USERID_X or USERID_Y, so does AUTHUR in the same transaction whether he appears as USERID_Y or USERID_Z.) This table tells you exactly which transaction encountered one or more IP addresses that are each used by more than one user. The alert shouldn't care about what is the original field name that is merged into USER, or which transactions are without such incidents.

This said, suppose the redundant listing has meaning to someone - I also have bosses that are particular about layout, you can spaghettify code to give

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>) | eval "IP OF <<FIELD>>" = IP] | stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID

This should produce the same layout as your mockup except the added column of transactionID without which the problem is ill-defined. Again, using wildcard and such could reduce the amount of typing.

Re: Splunk Use Case

Splunk_Master01 — Sat, 20 Aug 2022 08:47:19 GMT

When I run this:

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB)
|  foreach USERID_X USERID_Y USERID_Z
    [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>)]
| stats values(USER) as USER by IP transactionID
| where mvcount(USER) > 1

It brings something like this:

transactionID

USER

10.10.4.1

some ID

JOSEPH

JACINTA

The issue is that it is picking JOSEPH's IP address only and not JACINTA's and both users are using different IP's when I check individually for each user.

When I run this:

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB)
|  foreach USERID_X USERID_Y USERID_Z
    [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>)
    | eval "IP OF <<FIELD>>" = IP]
| stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID

I get something close to this:

USER	USERID_X	IP OF USERID_X	USERID_Y	IP OF USERID_Y	USERID_Z	IP OF USERID_Z
MARY MARY JANE	MARY	10.10.1.1	MARY	10.10.1.1	JANE	10.10.1.2
BEATRICE ARTHUR ARTHUR	BEATRICE	10.10.2.1	ARTHUR	10.10.2.3	ARTHUR	10.10.2.3
AMY DIANA BELLA	AMY	10.10.3.1	DIANA	10.10.3.2	BELLA	10.10.3.6
JOSEPH JACINTA JACINTA	JOSEPH	10.10.4.1	JACINTA	10.10.4.1	JACINTA	10.10.4.1

The issue here is that when I use BY Transaction_ID, the IP addresses disappear and I am only left with the fields USER, USERID_X, USERID_Y and USERID_Z.

Removing the line BY TRANSACTION_ID populates the whole table but the issue is that IP addresses duplicate across the fields for USER, USERID_X, USERID_Y and USERID_Z and are not correctly mapped to the correct user.

So I see something like:

USER

USERID_X

IP OF USERID_X

USERID_Y

IP OF USERID_Y

USERID_Z

IP OF USERID_Z

Jane

Martha

Jane

10.10.1.1

Jane

10.10.1.1

Martha

10.10.1.1

And I know that the IP addresses assigned to Martha and Jane are in-correct because I have run searches for each individual user to see their individual IP addresses.

Re: Splunk Use Case

Splunk_Master01 — Sat, 20 Aug 2022 09:36:39 GMT

I need results to be close to this format:

index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB)
|  foreach USERID_X USERID_Y USERID_Z
    [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>)
    | eval "IP OF <<FIELD>>" = IP]
| stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID

But I need information be picked correctly for each user.