topic How to remove decimal value from values in a table? in Splunk Search

How to remove decimal value from values in a table?

anissabnk — Tue, 02 May 2023 13:47:35 GMT

Hello everyone,

I need your help for something, please.

I need to remove the decimal value for this fields:

- total

- hier

My spl request is :

| union [ search index="pasrau_statuts_count" libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU")
| lookup lk_etapes_pasrau_2020_rj libelle output evenement, ordre ]
| join type=outer libelle [ search index=pasrau_statuts_count earliest=-100d@d latest=@d libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU") | eval hier=count | table libelle hier ]
| eval delta=case(hier < count, "+".(count-hier), hier > count, "-".(hier-count), hier=count, "0")
| eval libelle=ordre+libelle, total=ordre+"."+count, hier=ordre+"."+hier
| dedup libelle sortby lookup |stats list(libelle) as libelle , list(total) as total list(hier) as hier list(delta) as "DeltaJ/J-1" by evenement
| sort libelle
|rex field=libelle mode=sed "s/^[0-9]+//g"
|rex field=total mode=sed "s/^[0-9]\.+//g"
|rex field=hier mode=sed "s/^[0-9]\.+//g"

Thank you so much

Re: How to remove decimal value from values in a table

yuanliu — Tue, 02 May 2023 07:35:58 GMT

Do you mean round or floor? For example,

| union [ search index="pasrau_statuts_count" libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU") | lookup lk_etapes_pasrau_2020_rj libelle output evenement, ordre ] | join type=outer libelle [ search index=pasrau_statuts_count earliest=-100d@d latest=@d libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU") | eval hier=count | table libelle hier ] | eval delta=case(hier < count, "+".(count-hier), hier > count, "-".(hier-count), hier=count, "0") | eval libelle=ordre+libelle, total=ordre+"."+count, hier=ordre+"."+hier | dedup libelle sortby lookup |stats list(libelle) as libelle , list(total) as total list(hier) as hier list(delta) as "DeltaJ/J-1" by evenement | sort libelle | eval libelle = round(libelle) | eval total = round(total) | eval hier = round(hier)

Re: How to remove decimal value from values in a table

anissabnk — Tue, 02 May 2023 08:34:51 GMT

Unfortunately, it doesn't work

Re: How to remove decimal value from values in a table

yuanliu — Wed, 03 May 2023 03:33:54 GMT

Can you explain what is the desired result? I see that Total is now "11". No decimals. What is the value that you are looking for?

Re: How to remove decimal value from values in a table

anissabnk — Wed, 03 May 2023 07:18:54 GMT

Yes but the other values for total have disappeared

Re: How to remove decimal value from values in a table

PickleRick — Wed, 03 May 2023 09:19:54 GMT

OK. Let's start by saying that what you're doing with your multivalued fields is not a good practice.

You have several separate multivalued fields on which order you probably rely (in your case the value of Envoi SNG1 OK probably should correspond to the first values in other fields and the second values should contain values regarding Envoi SNG1 KO. In some cases it might work this way but in general, there's no way of telling which ones are which - multivalued fields are independent and their order is independent. While your search which does not do much after stats list() should preserve the order of values, you might get into trouble if for some reason some of the values would turn out empty. Splunk would "squish" the remaining values and you would have no way of knowing which values from one field would correspond to which from the other field.

Another thing is your use of the union command. One thing is the command itself (which should be avoided if possible) but another thing is that you have just one subsearch in your union call. So what's the point of the command in the first place?

Also, use of join should be avoided if possible. On small data sets it might be the easiest way to get your results and that's perfectly fine but if you have bigger sets of results to "join", you'd better find another method - typically by means of clever stats usage.

So the question is what data you have and what do you want to get from it. There's most probably a more efficient way to do it. 🙂

Re: How to remove decimal value from values in a table

yuanliu — Thu, 04 May 2023 00:03:37 GMT

So, total is multivalued. Easy

| union [ search index="pasrau_statuts_count" libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU") | lookup lk_etapes_pasrau_2020_rj libelle output evenement, ordre ] | join type=outer libelle [ search index=pasrau_statuts_count earliest=-100d@d latest=@d libelle IN ("Envoi SNGI OK", "Envoi SNGI KO", "RECU") | eval hier=count | table libelle hier ] | eval delta=case(hier < count, "+".(count-hier), hier > count, "-".(hier-count), hier=count, "0") | eval libelle=ordre+libelle, total=ordre+"."+count, hier=ordre+"."+hier | dedup libelle sortby lookup |stats list(libelle) as libelle , list(total) as total list(hier) as hier list(delta) as "DeltaJ/J-1" by evenement | sort libelle | eval libelle = mvmap(libelle, round(libelle)) | eval total = mvmap(total, round(total)) | eval hier = mvmap(hier, round(hier))