topic Re: Returning the results of two criteria in Splunk Search

Returning the results of two criteria

johnnybillyd — Tue, 25 Aug 2020 04:50:18 GMT

Hi,

I am fairly new to Splunk. I have been going down a lot of rabbit holes and its probably time I reach out for some guidance:

I work as part of a team that look after a fleet of audiovisual (AV) systems. My Splunk searches return strings that populate these three fields: RoomName , AttributeID and RawSerialValue.

There are two AttributeIDs I am interested in: "Config Filename" and "Processor Firmware". My individual searches on both return their values in the RawSerialValue field.

I need to run a search that returns the RoomName for every AV system that has the same combination of "Config Filename" and "Processor Firmware". To be clear, systems can have the same "Config Filename" but different "Processor Firmware", and vice versa.

My efforts to combine the two either return no results, or strip out results that should be returned.

If someone can suggest the best method I should use, I'd appreciate it.

This search returns the RoomNames and groups them according to their "Config Filename":

index=av sourcetype=Fusion10PROD AttributeID="Config Filename" RawSerialValue="*" | dedup RoomName| top limit=20 RawSerialValue

And this returns the RoomNames and groups them according to their "Processor Firmware":

index=av sourcetype=Fusion10PROD AttributeID="Processor Firmware" RawSerialValue="*" | dedup RoomName| top limit=20 RawSerialValue

Thanks in advance,

Regards,

John

Re: Returning the results of two criteria

gcusello — Tue, 25 Aug 2020 05:47:09 GMT

Hi @johnnybillyd,

let me understand:

your events have three fields: RoomName, AttributeID and RawSerialValue;
among all the events you are interested in those in which AttributeIDs has value: "Config Filename" or "Processor Firmware";
you want to correlate the events that have the same RoomName and select those in which AttributeIDs have both values: "Config Filename" or "Processor Firmware";
you want to display RoomName and RawSerialValue of those that appear for a greater number of times;

did I understand your requirements correctly?

If these are your requirements, please try something like this:

index=av sourcetype=Fusion10PROD (AttributeID="Config Filename" OR AttributeID="Processor Firmware") RawSerialValue="*" | stats values(AttributeID) AS AttributeID dc(AttributeID) AS dc_AttributeID values(RawSerialValue) AS RawSerialValue count BY RoomName | where=2 | sort -count | table RoomName RawSerialValue count

Ciao.

Giuseppe

Re: Returning the results of two criteria

johnnybillyd — Tue, 25 Aug 2020 06:43:15 GMT

Hi Giuseppe,

@gcusello

Thanks for answering. Apologies if my description was not clear enough. My replies are at the end of your bullet points inline:

your events have three fields: RoomName, AttributeID and RawSerialValue; ........................Correct
among all the events you are interested in those in which AttributeIDs has value: "Config Filename" or "Processor Firmware"; .....................Correct
you want to correlate the events that have the same RoomName and select those in which AttributeIDs have both values: "Config Filename" or "Processor Firmware"; ......................Yes, I need to correlate the events for the RoomNames that have both values.
you want to display RoomName and RawSerialValue of those that appear for a greater number of times;.........I'm not sure I completely understand this statement...

But you have certainly assisted me to clarify what I am actually after. I want to display a table that has the list of Config Filenames" /"Processor Firmware" pairs so when I click on one of the listings, I can then see the RoomNames that have these pairings.

For example: 8 rooms called 1A, 1B, 1C....1H

1A, 1B, 1C, 1D have a Config Filename of xyz

1E, 1F, 1G, 1H have a Config Filename of uvw

1A, 1B, 1C have Processor Firmware zzz

1E, 1F, 1G have Processor Firmware yyy

1D and 1H has Processor Firmware xxx

Output to look something like:

Config Filename/Processor Firmware Count

xyz/zzz 3

xyz/yyy 0

xyz/xxx 1

uvw/zzz 0

uvw/yyy 3

uvw/xxx 1

etc.

Re: Returning the results of two criteria

gcusello — Tue, 25 Aug 2020 06:49:32 GMT

Hi @johnnybillyd,

Using my search you can have the result you want, with the only exception of the 0 values.

About the item that you don't understand I mean to sort results, so you can take only a parte of them (e.g. the 5 most presesent) addinf the command head <num> at the end of the search.

Ciao.

Giuseppe

Re: Returning the results of two criteria

johnnybillyd — Tue, 25 Aug 2020 06:54:58 GMT

Hi @gcusello

Thanks again. The where clause is returning an error:

Error in 'where' command: The expression is malformed. An unexpected character is reached at '=2 '.

Regards,

John

Re: Returning the results of two criteria

gcusello — Tue, 25 Aug 2020 07:03:27 GMT

hi @johnnybillyd,

sorry!

| where dc_AttributeID=2

Ciao.

Giuseppe

Re: Returning the results of two criteria

johnnybillyd — Tue, 25 Aug 2020 07:22:07 GMT

Hi @gcusello

I think we're getting there. For some reason, every pairing is returning the same count. I think I need to explain that these results I am searching are being returned to the database constantly. I'm not sure of the exact frequency, but I think the values are polled approximately once every 3 or 4 minutes.

I changed the time range to "last three minutes" and each pair then gave me a count of 2. Before that(with a time search of 1 hour) each pair said it was returning 26 values.

However when I click on the pairings, sometimes there are 4 rooms, and sometimes there is 1.

One of the pairings returning 1 room should be actually returning over 800.

Sorry about this. If it's becoming too difficult and you need to stop helping, I really appreciate all the assistance, and I am certainly a lot closer than I was a short while ago!

Regards,

John

Re: Returning the results of two criteria

gcusello — Tue, 25 Aug 2020 07:39:49 GMT

Hi @johnnybillyd,

I hope to have transferred to you not a solution to your need (it was really impossible with so few informations) but an approach to solve these kind of problems.

If you think that my comments answer to your question, please accept it for the other people of community.

Ciao and good splunking.

Giuseppe

P.S.. Karma Points are appreciated 😉