topic Re: Converting from MB to GB not working in Splunk Search

Converting from MB to GB not working

annageorgiou — Tue, 11 Feb 2020 03:56:58 GMT

HI,

I have my query and doesn't seem to convert from MB to GB. What am I doing wrong? Can anyone help me?

Re: Converting from MB to GB not working

renjith_nair — Tue, 11 Feb 2020 05:05:46 GMT

@annageorgiou ,

In your search, you are not usnig the evaluated fields in your stats command.

Can you run the search and check if its working for you ?
index= * "other search terms"
| fields TotalSent,TotalRcvd
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
| eval TotalGB=round(TotalMB/1024,2)
|table TotalSent,TotalRcvd,TotalMB,TotalGB

Re: Converting from MB to GB not working

annageorgiou — Tue, 11 Feb 2020 05:30:15 GMT

I just ran your exact search with my index and it didn't work.

Re: Converting from MB to GB not working

annageorgiou — Tue, 11 Feb 2020 05:51:49 GMT

I found this coding but this does not appear to be working for me either. I wanted a search by app and total to GB

index= *
|stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd

|eval TotalDownload=round((TotalRcvd)/1024/1024,2)
|eval TotalUpload=round((TotalSent)/1024/1024,2)
|eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
|eval TotalGB=round((TotalMB)/1024,2)
|table TotalDownload TotalUpload TotalGB

Re: Converting from MB to GB not working

gcusello — Tue, 11 Feb 2020 07:18:28 GMT

Hi @annageorgiou,
as @renjith.nair said, after stats command you have only the fields of stats command, in your case only TotalSent, TotalRcvd and app; to use some other fields you have to use other functions like values, sum, max, avg.
So in your search, try:

index= *
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
| eval TotalGB=round(TotalMB/1024,2)
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd values(TotalGB) AS TotalGB by app
| addtotals
| sort limit=30 - total

p.s.: you don't need dedup after stats.

Ciao.
Giuseppe

Re: Converting from MB to GB not working

annageorgiou — Tue, 11 Feb 2020 22:03:53 GMT

Hi Giuseppe,

Thanks for the coding and I have tried it but again it doesn't give me the GB's. This is just an example of the result and I only included first App, It appears in MB (I think) and I get no totals in the 'TotalGB' column. Can you please help? P.s. Can't line up the APP row to go under the headings. There was no GB's in that column.

    app        TotalSent        TotalRcvd       TotalGB     Total

1 HTTP 1348273 830314 2178587

Re: Converting from MB to GB not working

somesoni2 — Tue, 11 Feb 2020 22:31:56 GMT

Try this (assuming your data has field sentbyte, rcvdbyte and app extracted. Consider adding some metadata filter to your search e.g index/sourcetype/source/host for better query performance)

index= *
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd values(TotalGB) AS TotalGB by app
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
| eval TotalGB=round(TotalMB/1024,2)
| sort -TotalGB
| head 30

Re: Converting from MB to GB not working

gcusello — Wed, 12 Feb 2020 12:51:34 GMT

Hi @annageorgiou,
try this:

index= *
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd by app
| eval TotalMB=round((TotalSent+TotalRcvd)/1024/1024,2)
| eval TotalGB=round(TotalMB/1024,2)
| addtotals
| sort -TotalGB
| head 30

Ciao.
Giuseppe

Re: Converting from MB to GB not working

annageorgiou — Wed, 12 Feb 2020 23:36:53 GMT

I'm not sure why it's not working. I played around with it and came up with this but unsure if it's giving me the correct information. It is converting MB to GB right? Sorry... new to splunk and coding.

Re: Converting from MB to GB not working

annageorgiou — Wed, 12 Feb 2020 23:39:29 GMT

Thanks somesoni2 but this coding is not working for me. Still doesn't display the GB. I have played around with some coding and I find this gives me a result but not sure how accurate it is. Sorry new to Splunk and coding so not sure if this is showing a result of MB converting to GB? Is it correct?

Re: Converting from MB to GB not working

gcusello — Thu, 13 Feb 2020 08:29:24 GMT

Hi @annageorgiou,
there's a problem: sentbyte and rcvdbyte are in bytes, so if you don't use the first eval you haven't the value in GB but in kB, to have GB conversion in only one line, you have to divide three times for 1024:

| eval TotalGB=round((TotalSent+TotalRcvd)/1024/1024/1024,2)

in this way it should be correct.

Ciao.
Giuseppe

Re: Converting from MB to GB not working

annageorgiou — Thu, 13 Feb 2020 22:12:24 GMT

Thanks for your help Guiseppe but the only coding that worked for me is below. I'm pretty sure it's showing me GB. I tried your coding but it's still not showing me the GB amounts. I've changed it to NOT show by app now and just give me an overall amount. if I use 1024/1024/1024 it doesn't work. Do you think this coding would show me GB or Bytes?

index=*
| stats sum(sentbyte) AS TotalSent, sum(rcvdbyte) AS TotalRcvd
| eval TotalSent=round((TotalSent)/1024,2)
| eval TotalRcvd=round((TotalRcvd)/1024,2)
| addtotals

Re: Converting from MB to GB not working

annageorgiou — Fri, 14 Feb 2020 04:50:39 GMT

Okay... I fiddled with the coding and I think I have it!!! This actually gives me GB's

Re: Converting from MB to GB not working

annageorgiou — Fri, 14 Feb 2020 05:39:40 GMT

I figured out that it doesn't work with 3 of the 1024 in your coding (1024,1024,1024). What I think it's doing is using the Sentbyte or Rcvdbyte as 1024 so you only need another 2 of the 1024 in your coding to divide it to get to GB. I'm not sure if I made sense... but it works.

Re: Converting from MB to GB not working

gcusello — Fri, 14 Feb 2020 07:50:58 GMT

Hi @annageorgiou,
it should be the same thing, but the importance is that you solved your problem!
Please accept and or upvote this answer to share it withthe other people of community.

Ciao.
Giuseppe

Re: Converting from MB to GB not working

annageorgiou — Sun, 16 Feb 2020 21:38:07 GMT

Thanks Giuseppe 😉