topic Re: Query for different lines with status in Splunk Search

Query for different lines with status

graziaedu — Thu, 23 Sep 2021 20:46:11 GMT

I have a log as a below

cod:5678,status:600
cod:9012,staus:600
cod:1234,status:600
cod: 1234,status:900
cod:4987,status:600
cod:4987,status:900
cod:3655,status:600
cod:3655,status:900

I need a query that give me this result

cod	status1	status2
1234	600	900
5678	600
9012	600
4987	600	900
3655	600	900

how can i write a query for this?

Thanks

Re: Query for different lines with status

PickleRick — Fri, 24 Sep 2021 05:38:08 GMT

What if for a given value of cod you had just status=900?

Re: Query for different lines with status

graziaedu — Fri, 24 Sep 2021 09:03:41 GMT

I have 900 only if I have 600.

Re: Query for different lines with status

PickleRick — Fri, 24 Sep 2021 10:24:44 GMT

OK. So why not just list the max value? (either the 600 or 900)

Of course you can do it in two columns but it's much easier to just

| stats max(status) by cod

than to try to combine the values, then split them into different columns.

It is of course possible, but this one is way, way simpler

Re: Query for different lines with status

graziaedu — Fri, 24 Sep 2021 11:56:59 GMT

I need 2 columns because I have to analyze if the cod changed status.

Cod 600 is processing and 900 is processed. I need this view in a table.

Re: Query for different lines with status

PickleRick — Fri, 24 Sep 2021 13:05:28 GMT

I don't get it. You say that there's no possiblity that you have just 900, without 600. So if you have 600 as the result, it's still processing. If 900 - it's finished (must have been 600 previously, but now you have both events)

As I said - it's possible to do it in two columns but it's just an overkill and unnecessary complication.

Re: Query for different lines with status

ashvinpandey — Fri, 24 Sep 2021 13:53:21 GMT

@graziaedu How actually you are looking for results ? if status1 is 600 then status2 should also be 600 ? am i getting this right ?

Re: Query for different lines with status

Richfez — Fri, 24 Sep 2021 19:11:46 GMT

OK, so assuming you strictly interpret the need here, and assume that it's only values of 600 and 900 and no others, and I took out one 600-value from the test data I built so I could confirm that if there's a 900 and NOT a 600, that it does what I expected.

Then, with those caveats and this fragility, one could do the last two lines here.

| makeresults | eval testdata = "cod:5678,status:600XXcod:9012,status:600Xcod:1234,status:600Xcod: 1234,status:900Xcod:4987,status:600Xcod:4987,status:900Xcod:3655,status:900" | makemv testdata delim="X" | mvexpand testdata | rex field=testdata "cod:\s*(?<cod>[^,]*),status:\s*(?<status>\d*)" | chart values(status) over cod by status | rename 600 AS Status1, 900 AS Status2

WHERE - the first 5 lines (run them all by themselves!) are just to build a test data set as a run-anywhere search.

The chart command and the rename command - the last two lines - are the ones that do the work.

And it assumes you have this data ingested in a way there are two fields for each record - a cod field and a status field. Otherwise, really, you should go back and reingest the data and set that up.