Splunk Search

Unique count for total logged in users

Path Finder

Hi,

Currently, I'm getting number of users logged in last 24 hrs as below...

index=myindex sourcetype="my_log" login latest=@d | stats min(_time) as firsttime by uid | where (relative_time(now(),"-1d@d") <= firsttime) AND (firsttime < relative_time(now(),"@d")) | stats count

Here, I'm looking for keyword login in the log & then getting firsttime & comparing it with last 24 hrs period. Now, I added timerangepicker drop-down for above query.

But, I'm not able to adjust above query so that it'll show unique logged in users depending on the drop-down selection. Please note that I'm able to show the drop-down without any issue using timerangepicker module.

I'll appreciate if someone can tell me how above query can be modified so that it'll show results based on the drop-down selection?

Thanks!

Tags (2)
0 Karma
1 Solution

Ultra Champion

Ok, try this. I have used Advanced XML and what I am basically doing is creating a customized time picker using the StaticSelect module , which then uses a stringreplace intention to customize your search.You could do this in Simple XML also.You can add more time selection options yourself.I tried using the TimeRangePicker module, but couldn't determine a way to extract the earliest/latest values to set as intentions.

<view autoCancelInterval="90" isVisible="true" onunloadCancelJobs="true"
template="dashboard.html">

<label>Unique count for total logged in users</label>

<module name="AccountBar" layoutPanel="appHeader" />

<module name="AppBar" layoutPanel="navigationHeader" />

<module name="Message" layoutPanel="messaging">
    <param name="filter">*</param>
    <param name="clearOnJobDispatch">False</param>
    <param name="maxSize">1</param>
</module>

<module name="Message" layoutPanel="messaging">
    <param name="filter">splunk.search.job</param>
    <param name="clearOnJobDispatch">True</param>
    <param name="maxSize">1</param>
</module>

<module name="StaticSelect" layoutPanel="panel_row1_col1">
    <param name="settingToCreate">time_numeric</param>
    <param name="staticFieldsToDisplay">
        <list>
            <param name="label">Day</param>
            <param name="value">-1d</param>
        </list>
        <list>
            <param name="label">7 Days</param>
            <param name="value">-7d</param>
        </list>
        <list>
            <param name="label">30 Days</param>
            <param name="value">-30d</param>
        </list>
    </param>
    <param name="label">Find count of users logged on for the first time in
        the previous </param>

    <module name="ConvertToIntention">
        <param name="settingToConvert">time_numeric</param>
        <param name="intention">
            <param name="name">stringreplace</param>
            <param name="arg">
                <param name="time_numeric">
                    <param name="value">$target$</param>
                </param>
            </param>
        </param>

        <module name="HiddenSearch" layoutPanel="panel_row1_col1"
            autoRun="true">

            <param name="search"><![CDATA[index=myindex sourcetype="my_log" login latest=@d | stats min(_time) as firsttime by uid | where (relative_time(now(),"$time_numeric$@d") <= firsttime) AND (firsttime < relative_time(now(),"@d")) | stats count ]]>
            </param>
            <module name="SimpleResultsTable"></module>

        </module>
    </module>
</module>

View solution in original post

0 Karma

Ultra Champion

Ok, try this. I have used Advanced XML and what I am basically doing is creating a customized time picker using the StaticSelect module , which then uses a stringreplace intention to customize your search.You could do this in Simple XML also.You can add more time selection options yourself.I tried using the TimeRangePicker module, but couldn't determine a way to extract the earliest/latest values to set as intentions.

<view autoCancelInterval="90" isVisible="true" onunloadCancelJobs="true"
template="dashboard.html">

<label>Unique count for total logged in users</label>

<module name="AccountBar" layoutPanel="appHeader" />

<module name="AppBar" layoutPanel="navigationHeader" />

<module name="Message" layoutPanel="messaging">
    <param name="filter">*</param>
    <param name="clearOnJobDispatch">False</param>
    <param name="maxSize">1</param>
</module>

<module name="Message" layoutPanel="messaging">
    <param name="filter">splunk.search.job</param>
    <param name="clearOnJobDispatch">True</param>
    <param name="maxSize">1</param>
</module>

<module name="StaticSelect" layoutPanel="panel_row1_col1">
    <param name="settingToCreate">time_numeric</param>
    <param name="staticFieldsToDisplay">
        <list>
            <param name="label">Day</param>
            <param name="value">-1d</param>
        </list>
        <list>
            <param name="label">7 Days</param>
            <param name="value">-7d</param>
        </list>
        <list>
            <param name="label">30 Days</param>
            <param name="value">-30d</param>
        </list>
    </param>
    <param name="label">Find count of users logged on for the first time in
        the previous </param>

    <module name="ConvertToIntention">
        <param name="settingToConvert">time_numeric</param>
        <param name="intention">
            <param name="name">stringreplace</param>
            <param name="arg">
                <param name="time_numeric">
                    <param name="value">$target$</param>
                </param>
            </param>
        </param>

        <module name="HiddenSearch" layoutPanel="panel_row1_col1"
            autoRun="true">

            <param name="search"><![CDATA[index=myindex sourcetype="my_log" login latest=@d | stats min(_time) as firsttime by uid | where (relative_time(now(),"$time_numeric$@d") <= firsttime) AND (firsttime < relative_time(now(),"@d")) | stats count ]]>
            </param>
            <module name="SimpleResultsTable"></module>

        </module>
    </module>
</module>

View solution in original post

0 Karma

Path Finder

Hi,

In above query, how can I replace -1d@d with the drop-down value selection, say for example:

For Last 24 hrs, it should be

relative_time(now(),"-1d@d") <= firsttime) AND (firsttime < relative_time(now(),"@d")

For Last 7 days, it should be

relative_time(now(),"-7d@d") <= firsttime) AND (firsttime < relative_time(now(),"@d")

How can I change the above value dynamically based on the drop-down selection so that I'll get the desired result?

Thanks!

0 Karma

Ultra Champion

So you want to "show unique logged in users depending on the drop-down selection" :

Unless I misunderstand :

timerangepicker -> "Last 24 hours"

search -> index=myindex sourcetype="my_log" login | dedup uid | table uid

0 Karma

Path Finder

This won't work. Since you're first looking for login keyword in last 24 hrs, you'll not be comparing it with users who already logged in earlier before 24 hours....I want to count it only once....So, if the user is logged in 3 days ago, I should not count that user...I might have stated my problem incorrectly. But I need to find out those users who logged in the very first time depending on drop-down selection..

0 Karma

Path Finder

Hi, the user may login multiple times & the "login" keyword gets logged each time...& I don't want to count that user again..
The log line has login keyword & uid keyword containing userid

0 Karma