Add a column with a count of unique IP's to a chart

[markus007]

Hi@all,

i'm new a splunk and been trying to figure out this for a while now. But for me it is not possible to add a additionally column with a count of unique ip (ip2) adresses to my existing chart.

My serach string :

index="xxx_prod" environment="p" (ZS-2200150 OR ZS-2200039 OR ZS-2200044 OR ZS-2200045 OR ZS-2200046 OR ZS-2200068 OR yyy-021024 OR ZS-2200054 OR ZS-2200056 OR ZS-2200046 OR ZS-2100018 OR xxx-050001) | eval week=strftime(_time, "%V/%Y") | eval error_text=if(like(error_text,"%<text>%"),"Ohne Bezug dürfen ausschließlich wechselwirkungsrelevante Abgaben gespeichert werden", error_text)  | rex field=error_text mode=sed "s/\..*$//" | rex field=error_text mode=sed "s/\(.*$//" | rex field=error_code mode=sed "s/xxxx-050003/EMED-021024/g" | eval error_text2 = replace(error_text,",", " ") | eval Fehlermeldung=error_code. " " .error_text2. " - " .environment . " - " . application1 | rex field=_raw "IP: (?<ip2>[^,]+)"  | chart count(Fehlermeldung) by Fehlermeldung week | sort Fehlermeldung

Result:

Fehlermeldung                                            10/2018
ZS-2100018 Proxy-Request.......                         431
ZS-2200044 Bei der Abfrage......                          12
.................

But i would like this result:

Fehlermeldung                                                 10/2018           UniqueIP
ZS-2100018 Proxy-Request.......                              431                      12
ZS-2200044 Bei der Abfrage......                              12                         3
    .................

Thanks for the help in advanced.

[cmerriman]

if you are ok with them being in the same column, how about something like this:

 index="xxx_prod" environment="p" (ZS-2200150 OR ZS-2200039 OR ZS-2200044 OR ZS-2200045 OR ZS-2200046 OR ZS-2200068 OR yyy-021024 OR ZS-2200054 OR ZS-2200056 OR ZS-2200046 OR ZS-2100018 OR xxx-050001) | eval week=strftime(_time, "%V/%Y") | eval error_text=if(like(error_text,"%<text>%"),"Ohne Bezug dürfen ausschließlich wechselwirkungsrelevante Abgaben gespeichert werden", error_text)  | rex field=error_text mode=sed "s/\..*$//" | rex field=error_text mode=sed "s/\(.*$//" | rex field=error_code mode=sed "s/xxxx-050003/EMED-021024/g" | eval error_text2 = replace(error_text,",", " ") | eval Fehlermeldung=error_code. " " .error_text2. " - " .environment . " - " . application1 | rex field=_raw "IP: (?<ip2>[^,]+)"  | stats count(Fehlermeldung) as count dc(ip2) as ip by Fehlermeldung week |eval metric=count." Fehl., ".ip." unique IPs"|chart values(metric) by Fehlermeldung week

^^^ would account for multiple weeks and put both values in the same column, so you wouldn't have two columns per week. if you wanted two columns per week, try this:

 index="xxx_prod" environment="p" (ZS-2200150 OR ZS-2200039 OR ZS-2200044 OR ZS-2200045 OR ZS-2200046 OR ZS-2200068 OR yyy-021024 OR ZS-2200054 OR ZS-2200056 OR ZS-2200046 OR ZS-2100018 OR xxx-050001) | eval week=strftime(_time, "%V/%Y") | eval error_text=if(like(error_text,"%<text>%"),"Ohne Bezug dürfen ausschließlich wechselwirkungsrelevante Abgaben gespeichert werden", error_text)  | rex field=error_text mode=sed "s/\..*$//" | rex field=error_text mode=sed "s/\(.*$//" | rex field=error_code mode=sed "s/xxxx-050003/EMED-021024/g" | eval error_text2 = replace(error_text,",", " ") | eval Fehlermeldung=error_code. " " .error_text2. " - " .environment . " - " . application1 | rex field=_raw "IP: (?<ip2>[^,]+)"  | chart count(Fehlermeldung) as Count dc(ip2) as UniqueIP by Fehlermeldung week 

[markus007]

The solution with the two columns per week works for me and is exactly what I was looking for.
Very nice, thank you cmerriman

[cmerriman]

perfect, i'm glad it works. if the solution is acceptable, please accept the answer to "close" the question and award points. if you need anything else, i'm happy to respond to any comment or other question you might post.

[niketn]

@markus007 when you have multiple weeks how would you show your data? Would there be multiple Count and UniqueIP columns for every week?

i.e. Count: 10/2018            UniqueIP:10/2018            Count: 11/2018             UniqueIP:11/2018             ....

[markus007]

yes columns for every week. cmerriman post my solution.
But thanks anyway to all.

[deepashri_123]

Can u try this:

index="xxx_prod" environment="p" (ZS-2200150 OR ZS-2200039 OR ZS-2200044 OR ZS-2200045 OR ZS-2200046 OR ZS-2200068 OR yyy-021024 OR ZS-2200054 OR ZS-2200056 OR ZS-2200046 OR ZS-2100018 OR xxx-050001) | eval week=strftime(_time, "%V/%Y") | eval error_text=if(like(error_text,"%%"),"Ohne Bezug dürfen ausschließlich wechselwirkungsrelevante Abgaben gespeichert werden", error_text) | rex field=error_text mode=sed "s/..$//" | rex field=error_text mode=sed "s/(.$//" | rex field=error_code mode=sed "s/xxxx-050003/EMED-021024/g" | eval error_text2 = replace(error_text,",", " ") | eval Fehlermeldung=error_code. " " .error_text2. " - " .environment . " - " . application1 | rex field=_raw "IP: (?[^,]+)" | chart dc(ip2) AS "Unique IP" count(Fehlermeldung) over week by Fehlermeldung | sort Fehlermeldung

Let me know if this helps!!