Splunk Search

How to extract a list and count over it?

vitorvmiguel
Explorer

Hi folks,

I need help. I'm trying to do a search that extracts one list of Unique Session ID's and then performs with this list of ID's a count of User Agents in only one search. My log archive has the fields tipo (type) equals to I=success, E=error, W=warning; programa (transaction); idlogon (session ID) and info10 (user agent).

Now i have this search below that gives me a list of session ID's.

index="raw_internet_cartonista" programa="WNHC" tipo="E" | eval Clientes=idlogon | dedup Clientes | table Clientes

I need to perform a subsearch with this list of Clients and count the User Agent of each one.

index="raw_internet_cartonista" programa="ILCL" tipo="I" | stats c as UserAgent by info10 (adding something to consider Clientes)

So i need to put these things together and return me some result. I've researched some commands like map, return and lookup for doing this, but can't achieve any solution.

I think that it should not be so difficult, but i can't find a way.
Can anyone help me?

Tks. Rgs.

0 Karma
1 Solution

woodcock
Esteemed Legend

Try this:

index="raw_internet_cartonista" programa="WNHC" tipo="E" | eval Clientes=idlogon | dedup Clientes | table Clientes | map search="search index=\"raw_internet_cartonista\" programa=\"ILCL\" tipo=\"I\" idlogon=$Clientes$| stats count as UserAgents by info10 idlogon"

View solution in original post

0 Karma

stephanefotso
Motivator

Try this:

 index="raw_internet_cartonista" programa="WNHC" tipo="E" | stats list(idlogon ) as  Clientes|dedup Clientes|join[search  index="raw_internet_cartonista" programa="ILCL" tipo="I"|stats list(info10) as UserAgent  ]|stats count(Clientes) by UserAgent

Thanks

SGF
0 Karma

yannK
Splunk Employee
Splunk Employee

Or use a sub search as search condition in the main search.

[ index="raw_internet_cartonista" programa="WNHC" tipo="E" 
 | eval Clientes=idlogon 
 | dedup Clientes 
 | table Clientes ]
index="raw_internet_cartonista" programa="ILCL" tipo="I" | stats c as UserAgent by info10  Clientes

the first [sub search] will return a list of conditions in the format ( Clientes="A" OR Clientes="B" OR Clientes="C" etc..)
this will work if the field "Clientes" exists in the events of the main search.

0 Karma

woodcock
Esteemed Legend

Try this:

index="raw_internet_cartonista" programa="WNHC" tipo="E" | eval Clientes=idlogon | dedup Clientes | table Clientes | map search="search index=\"raw_internet_cartonista\" programa=\"ILCL\" tipo=\"I\" idlogon=$Clientes$| stats count as UserAgents by info10 idlogon"
0 Karma

woodcock
Esteemed Legend

OK, based on your last clarification (re-translated as follows):

My log information contains these fields:
- tipo (type) = {I=success, E=error, W=warning}
- programa (transaction)
- idlogon (Client Session ID)
- info10 (User Agent)

The end goal is to get a list of how many Clients ( idlogons ) are facing errors and in wich UserAgents ( info10 ).
The problem is that the UserAgent ( info10 ) is in one transaction (programa="ILCL" tipo="I") but the error is in another transaction (programa="WNHC" tipo="E").

First I need to gather Unique Client Sessions by collecting a list of distrinct idlogon values.
Then I need to count how many Clients ( idlogons ) used which UserAgent ( info10 ).

If that is correct, then this will work (sticking with the original train of thought):

index="raw_internet_cartonista" programa="WNHC" tipo="E" | dedup idlogon | map search="search index=\"raw_internet_cartonista\" programa=\"ILCL\" tipo=\"I\" idlogon=$Clientes$| stats dc(info10) AS NumUserAgentsWithErrors by idlogon"

However, this should also work and be much quicker:

index="raw_internet_cartonista" ((programa="WNHC" tipo="E") OR (programa="ILCL" tipo="I")) stats dc(info10) AS NumUserAgentsWithErrors values(info10) AS UserAgentsWithErrors dc(tipo) AS numTipos by idlogon | where numTipos=2
0 Karma

woodcock
Esteemed Legend

So did this work?

0 Karma

vitorvmiguel
Explorer

Hi woodcock, the query you've sent worked but didn't produced the results i expected. The query below produces the results i want, but the join performance is realy bad.

index="raw_internet_cartonista" programa="ILCL" tipo="I" |  join max=0 idlogon [search index="raw_internet_cartonista" programa="WNHC" tipo="E" ] | timechart span=1d dc(codigoAcesso) as ClientesImpactados by info10 | sort limit=5 by -ClientesImpactados

So i was wondering if there is a way of doing the same job with better performance, the map query you've sent me did not count any value, it simply shows one list of info10 and counts 1 for each.
Anyway the both queries, mine and yours, seem to loose some values when compared to it running in parts.
Do you have any hints? Any idea why? Or even a better performatic query to run besides that two?

Thanks

0 Karma

woodcock
Esteemed Legend

Try this, which should be identical but perform a bit better:

index="raw_internet_cartonista" programa="ILCL" tipo="I" [ search index="raw_internet_cartonista" programa="WNHC" tipo="E" | dedup idlogon | field idlogon ] | stats dc(codigoAcesso) AS ClientesImpactados by info10 | sort 5 -ClientesImpactados

If it works, don't forget to "Accept" this answer.

0 Karma

vitorvmiguel
Explorer

I think you forgot something, because you are using multiple index in the same search and no function for subsearch. The query doesn't run.

0 Karma

woodcock
Esteemed Legend

Yes, I forgot the search directive; I have re-edited it; try again.

0 Karma

vitorvmiguel
Explorer

Hi woodcock, i think we finally achieved the goal. Thanks for the help.
Can you explain me the subsearch you've done [ search index="raw_internet_cartonista" programa="WNHC" tipo="E" | dedup idlogon | fields idlogon ] mainly the fields function, whats intended to do?

Rgs.

0 Karma

woodcock
Esteemed Legend

It keeps only that one idlogon field so that the subsearch is expanded into a search directive for the outer search that looks like

... AND ((idlogon=a) OR (idlogon=b) OR (idlogon=c) OR ... OR (idlogon=z))
0 Karma

vitorvmiguel
Explorer

Many thanks woodcock. Now it makes sense to me. Rgs.

0 Karma

vitorvmiguel
Explorer

woodcock, query worked. But it's not counting how many Clients had used each UserAgent. The count is "1" for each entry. Can you help me with this?

0 Karma

woodcock
Esteemed Legend

I cannot; the solution posted does EXACTLY what you asked PROVIDED that the 2nd search actually contains more than one event per pairing of info10 and idlogon. Because you have not said anything about what info10 is, we have no way to help you figure out what is going on there but I am sure that info10 is the problem. Does it look "better" if we remove it?

 index="raw_internet_cartonista" programa="WNHC" tipo="E" | eval Clientes=idlogon | dedup Clientes | table Clientes | map search="search index=\"raw_internet_cartonista\" programa=\"ILCL\" tipo=\"I\" idlogon=$Clientes$| stats count as UserAgents by idlogon"
0 Karma

vitorvmiguel
Explorer

Sorry about my poor english, i'm trying to express myself the best i can.
I'll try to explain again. My log information contains the fields tipo (type) equals to I=success, E=error, W=warning; programa (transaction); idlogon (session ID) and info10 (user agent). I'm trying to isolate in one list the Unique Client Sessions by colecting the idlogon.
Then i wish to count how many Clients used wich UserAgent (info10) so i must get a list of how many Clients are facing errors in wich UserAgent. So my log information of UserAgent is in one transaction (programa="ILCL" tipo="I") and my error is in another transaction (programa="WNHC" tipo="E") so i need to put all this things together.
First collect a list of clients facing error in the transaction, by extracting the session ID (idlogon), and after this use this list to count how many clients are using each different UserAgent.

I hope i made myself clear. Sorry about my english again.
Tks. Rgs

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...