How to compare two tables with different time range?

[Mrig342]

Hi All,

I have got the below query at two different time range (Last 24 hrs and All Time).

index=* | stats count by index,host 

which gives a table as below:

index	host	count
abc	hdcgcgmefla02uv	127976

Now I want to compare the host column in both the tables and populate it in a new column in a tabular view. If host is available in both time ranges, then the value is "Availabe" and if host is not available in any of the  time ranges the value will be "Not Available" Like below:

index	host	Comparision
abc	hdcgcgmefla02uv	Available
abc	hdcgcgmefla22uv	Not Available
xyz	hdcgcgmefla12uv	Available

Please help to create a query to get the table with the desired comparisons. Your kind inputs are highly appreciated.

Thank you..!!

[isoutamo · Accepted solution]

Hi

Here is one example how you could do it

index=* ``` This is not a best practices, try to define used indexes```
| eval TP = if (_time > relative_time(now(), "-24h@h"), mvappend("TP_all", "TP_24h"), "TP_all")
``` chart supports only 2 by fields ```
| eval HI = mvzip(host, index, "::")
| chart count by HI TP
| eval Comparision = if(TP_all = TP_all - TP_24h, "Not Available", "Available")
| mvexpand HI
| eval host = mvindex(split(HI, "::"),0), index = mvindex(split(HI, "::"),1)
| table index host Comparision

r. Ismo 

[Mrig342]

Hi @isoutamo ,

Can you please explain me what you did in this below step.

| eval TP = if (_time > relative_time(now(), "-24h@h"), mvappend("TP_all", "TP_24h"), "TP_all")

Thank You..!!

[isoutamo]

I create a mv field which contains TP_all only or TP_all and TP_24h values if _time is within last 24h. Then when I do chart over it, it calculates it "correctly" for both periods. 

[Mrig342]

Thank you very much @isoutamo ...!!

Your kind inputs are highly appreciated. Cheers..!!