topic Re: How to extract a list and count over it? in Splunk Search

How to extract a list and count over it?

vitorvmiguel — Tue, 16 Jun 2015 14:23:43 GMT

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.

Re: How to extract a list and count over it?

woodcock — Tue, 16 Jun 2015 14:55:37 GMT

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"

Re: How to extract a list and count over it?

yannK — Tue, 16 Jun 2015 15:01:09 GMT

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.

Re: How to extract a list and count over it?

vitorvmiguel — Tue, 16 Jun 2015 15:06:40 GMT

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?

Re: How to extract a list and count over it?

woodcock — Tue, 16 Jun 2015 15:15:09 GMT

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"

Re: How to extract a list and count over it?

stephanefotso — Tue, 16 Jun 2015 15:20:17 GMT

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

Re: How to extract a list and count over it?

vitorvmiguel — Tue, 16 Jun 2015 15:31:26 GMT

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

Re: How to extract a list and count over it?

woodcock — Tue, 16 Jun 2015 16:05:55 GMT

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

Re: How to extract a list and count over it?

woodcock — Tue, 07 Jul 2015 21:35:22 GMT

So did this work?

Re: How to extract a list and count over it?

vitorvmiguel — Wed, 08 Jul 2015 12:42:55 GMT

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

Re: How to extract a list and count over it?

woodcock — Wed, 08 Jul 2015 14:22:44 GMT

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.

Re: How to extract a list and count over it?

vitorvmiguel — Wed, 08 Jul 2015 15:46:28 GMT

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.

Re: How to extract a list and count over it?

woodcock — Wed, 08 Jul 2015 15:50:40 GMT

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

Re: How to extract a list and count over it?

vitorvmiguel — Mon, 13 Jul 2015 14:45:18 GMT

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.

Re: How to extract a list and count over it?

woodcock — Mon, 13 Jul 2015 15:46:39 GMT

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))

Re: How to extract a list and count over it?

vitorvmiguel — Mon, 13 Jul 2015 16:57:29 GMT

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